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.

Steps to revoke privileges in PostgreSQL:

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.

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.

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.

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

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.

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)

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.

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

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.
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.

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.

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

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

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)

Run an application smoke test using the revoked role credentials.

Permission failures commonly surface as SQLSTATE 42501 (insufficient_privilege).