How to fix 01006: privilege_not_revoked in PostgreSQL

-- For table privileges
SELECT grantee, privilege_type, is_grantable
FROM information_schema.table_privileges
WHERE table_schema = 'public'
  AND table_name = 'your_table'
  AND grantee = 'target_user';

-- For schema privileges
SELECT grantee, privilege_type, is_grantable
FROM information_schema.schema_privileges
WHERE schema_name = 'public'
  AND grantee = 'target_user';

-- For database privileges
SELECT datname, usename, usecreatedb, usesuper
FROM pg_database d, pg_user u
WHERE d.datname = current_database()
  AND usename = 'target_user';

-- Common patterns that might cause 01006:
REVOKE INSERT, UPDATE ON TABLE employees FROM analyst_role;
-- If analyst_role only has SELECT, you'll get 01006 for INSERT and UPDATE

REVOKE ALL ON SCHEMA public FROM app_user;
-- If app_user has no privileges on public schema, you'll get 01006

REVOKE EXECUTE ON FUNCTION calculate_bonus() FROM hr_user;
-- If hr_user was never granted EXECUTE, you'll get 01006

-- See what roles the target user belongs to
SELECT rolname FROM pg_roles
WHERE pg_has_role('target_user', oid, 'member');

-- Check privileges granted to parent roles
SELECT r.rolname, t.privilege_type
FROM pg_roles r
CROSS JOIN unnest(ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']) t(privilege_type)
WHERE has_table_privilege(r.rolname, 'your_table', t.privilege_type)
  AND r.rolname IN (SELECT rolname FROM pg_roles WHERE pg_has_role('target_user', oid, 'member'));

-- Example: Only revoke if privilege exists
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.table_privileges
    WHERE grantee = 'target_user'
      AND table_schema = 'public'
      AND table_name = 'employees'
      AND privilege_type = 'INSERT'
  ) THEN
    REVOKE INSERT ON TABLE employees FROM target_user;
  END IF;
END $$;

-- Or use a function that suppresses the warning
CREATE OR REPLACE FUNCTION safe_revoke_privilege(
  p_username text,
  p_table_schema text,
  p_table_name text,
  p_privilege text
) RETURNS void AS $$
BEGIN
  EXECUTE format('REVOKE %s ON TABLE %I.%I FROM %I',
    p_privilege, p_table_schema, p_table_name, p_username);
EXCEPTION WHEN OTHERS THEN
  -- Log but don't fail
  RAISE NOTICE 'Could not revoke % on %.% from %: %',
    p_privilege, p_table_schema, p_table_name, p_username, SQLERRM;
END;
$$ LANGUAGE plpgsql;

-- Enable logging of privilege commands
ALTER DATABASE yourdb SET log_statement = 'ddl';

-- Or use event triggers for more detailed auditing
CREATE OR REPLACE FUNCTION audit_privilege_changes()
RETURNS event_trigger AS $$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
  LOOP
    IF r.command_tag IN ('GRANT', 'REVOKE') THEN
      INSERT INTO privilege_audit_log
      (event_time, username, command_tag, object_type, object_identity)
      VALUES (now(), current_user, r.command_tag, r.object_type, r.object_identity);
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER audit_privileges ON ddl_command_end
WHEN TAG IN ('GRANT', 'REVOKE')
EXECUTE FUNCTION audit_privilege_changes();