How to revoke privileges in PostgreSQL

Revoking privileges in PostgreSQL removes access that is no longer required, which reduces blast radius after credential leaks and limits who can read, change, or delete data. Least privilege is cheaper than incident response.

PostgreSQL authorization is role-based, with privileges stored on databases, schemas, tables, sequences, and other objects as access control lists (ACLs). Effective access can also come from role membership and grants to PUBLIC, so checking inheritance and object ACLs avoids chasing the wrong grant.

Privilege changes apply immediately for new statements and can break applications with permission denied errors, so staging and rollback readiness matter. Default privileges are scoped to the object-creating role, so revoking current access is not sufficient if migrations keep granting access on new objects.

1. List role membership for the account to catch inherited privileges.

   $ sudo -u postgres psql --dbname=appdb --command="\du appuser"
          List of roles
    Role name |  Attributes   
   -----------+---------------
    appuser   | 2 connections

   Replace appdb, appuser, appowner, and schema names to match the target database.

2. Revoke role membership that should no longer apply.

   $ sudo -u postgres psql --dbname=appdb --command="REVOKE appreadwrite FROM appuser;"
   REVOKE ROLE

   Revoking role membership can remove many privileges at once.

3. List current privileges on the target table before changes.

   $ sudo -u postgres psql --dbname=appdb --command="\dp public.orders"
                                    Access privileges
    Schema |  Name  | Type  |     Access privileges     | Column privileges | Policies 
   --------+--------+-------+---------------------------+-------------------+----------
    public | orders | table | appowner=arwdDxt/appowner+|                   | 
           |        |       | appuser=arwd/appowner     |                   | 
   (1 row)

   Common table privilege letters: a=INSERT, r=SELECT, w=UPDATE, d=DELETE, D=TRUNCATE, x=REFERENCES, t=TRIGGER.

4. Revoke a specific privilege from a role on a table.

   $ sudo -u postgres psql --dbname=appdb --command="REVOKE DELETE ON TABLE public.orders FROM appuser;"
   REVOKE

5. Revoke the same privilege from PUBLIC when access must be removed for every role.

   $ sudo -u postgres psql --dbname=appdb --command="REVOKE DELETE ON TABLE public.orders FROM PUBLIC;"
   REVOKE

   Revoking from PUBLIC affects every role in the database.

6. Re-check the table privileges to confirm the ACL change.

   $ sudo -u postgres psql --dbname=appdb --command="\dp public.orders"
                                    Access privileges
    Schema |  Name  | Type  |     Access privileges     | Column privileges | Policies 
   --------+--------+-------+---------------------------+-------------------+----------
    public | orders | table | appowner=arwdDxt/appowner+|                   | 
           |        |       | appuser=arw/appowner      |                   | 
   (1 row)

7. Revoke privileges on all existing tables in a schema to remove broad access.

   $ sudo -u postgres psql --dbname=appdb --command="REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM appuser;"
   REVOKE

   This affects existing objects only; default privileges can still grant access to new tables.

8. Revoke privileges on all sequences in a schema to block nextval() and currval() access.

   $ sudo -u postgres psql --dbname=appdb --command="REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM appuser;"
   REVOKE

9. Revoke schema USAGE when the role should not access any objects in the schema.

   $ sudo -u postgres psql --dbname=appdb --command="REVOKE USAGE ON SCHEMA public FROM appuser;"
   REVOKE

   Without USAGE on a schema, object access fails even if table privileges remain.

10. Revoke CONNECT on the database to prevent new sessions.

    $ sudo -u postgres psql --dbname=appdb --command="REVOKE CONNECT ON DATABASE appdb FROM appuser;"
    REVOKE

    Existing sessions remain connected until disconnected; new connections fail immediately.

11. List default privileges to find grants that apply to future objects.

    $ sudo -u postgres psql --dbname=appdb --command="\ddp"
                  Default access privileges
      Owner   | Schema |   Type   |   Access privileges   
    ----------+--------+----------+-----------------------
     appowner | public | function | appuser=X/appowner
     appowner | public | sequence | appuser=rwU/appowner
     appowner | public | table    | appuser=arwd/appowner
    (3 rows)

    Default privileges are stored per owner role; target the role that creates objects using FOR ROLE.

12. Remove default table privileges for the owning role to prevent new tables from granting access.

    $ sudo -u postgres psql --dbname=appdb --command="ALTER DEFAULT PRIVILEGES FOR ROLE appowner IN SCHEMA public REVOKE ALL ON TABLES FROM appuser;"
    ALTER DEFAULT PRIVILEGES

13. Remove default sequence privileges for the owning role to prevent new sequences from granting access.

    $ sudo -u postgres psql --dbname=appdb --command="ALTER DEFAULT PRIVILEGES FOR ROLE appowner IN SCHEMA public REVOKE ALL ON SEQUENCES FROM appuser;"
    ALTER DEFAULT PRIVILEGES

14. Verify the privilege is no longer effective using has_table_privilege().

    $ sudo -u postgres psql --dbname=appdb --command="SELECT has_table_privilege('appuser','public.orders','DELETE') AS can_delete;"
     can_delete
    ------------
     f
    (1 row)

15. Run an application smoke test using the revoked role credentials.

    Permission failures commonly surface as SQLSTATE 42501 (insufficient_privilege).