PostgreSQL 01006 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 01006: privilege not revoked

PostgreSQL warning code 01006 (SQLSTATE 01006 - privilege_not_revoked) is raised when a REVOKE statement is executed but the specified privilege was not actually removed from the target role or user. Unlike fatal errors, this is a warning-level signal that does not abort a transaction, but it must not be ignored — it indicates your privilege management may not be working as intended. This typically happens when the target user never held the privilege, when the revoker is not the original grantor, or when privileges are inherited through role membership.

Top 3 Causes

1. The Target User Never Had the Privilege

Attempting to revoke a privilege that was never granted to the user is the most common trigger for 01006.

-- Check current privileges before revoking
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'orders'
  AND grantee = 'user_a';

-- If user_a never had INSERT, this raises 01006
REVOKE INSERT ON TABLE orders FROM user_a;

Fix: Always verify existing privileges before executing REVOKE.

-- Safe conditional revoke using DO block
DO $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM information_schema.role_table_grants
    WHERE grantee = 'user_a'
      AND table_name = 'orders'
      AND privilege_type = 'INSERT';

    IF v_count > 0 THEN
        EXECUTE 'REVOKE INSERT ON TABLE orders FROM user_a';
        RAISE NOTICE 'Privilege revoked successfully.';
    ELSE
        RAISE NOTICE 'No privilege found. Skipping REVOKE.';
    END IF;
END;
$$;

2. The Revoker Is Not the Original Grantor

PostgreSQL enforces that only the original grantor (or a superuser) can revoke a privilege. If user_b granted a privilege to user_c, then user_a cannot revoke it.

-- Check the grantor of a privilege
SELECT grantor, grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'orders';

-- Wrong: user_a tries to revoke what user_b granted
-- (logged in as user_a) -- may produce 01006
REVOKE SELECT ON TABLE orders FROM user_c;

-- Correct: connect as the original grantor or superuser
SET ROLE user_b;
REVOKE SELECT ON TABLE orders FROM user_c;
RESET ROLE;

3. Privilege Is Inherited via Role Membership

When a user holds a privilege indirectly through role membership, revoking it directly from the user has no effect and triggers 01006.

-- Check role membership
SELECT r.rolname AS role, m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member;

-- Wrong: trying to revoke from user who inherits via role
REVOKE SELECT ON TABLE orders FROM user_a; -- 01006 if inherited

-- Correct option 1: revoke from the role itself
REVOKE SELECT ON TABLE orders FROM readonly_role;

-- Correct option 2: remove user from the role
REVOKE readonly_role FROM user_a;

Quick Fix Summary

Situation	Solution
User never had the privilege	Check `information_schema` before revoking
Wrong grantor	Use superuser or the original grantor account
Role-inherited privilege	Revoke from the role or remove role membership

Prevention Tips

Use role-based privilege management exclusively. Never grant privileges directly to individual users. Assign privileges to roles, and manage users via role membership. This creates a predictable, single-layer privilege structure that eliminates most 01006 scenarios.

-- Centralized role-based approach
CREATE ROLE app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

-- Assign/revoke only via membership
GRANT app_readonly TO report_user;
REVOKE app_readonly FROM report_user; -- clean, no 01006

Audit privileges regularly. Schedule periodic snapshots of your privilege state and compare them against your expected baseline. Any discrepancy caught early prevents both 01006 warnings and security misconfigurations.

-- Snapshot current privileges for auditing
SELECT grantee, table_schema, table_name, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY grantee, table_name;