Skip to content

Instantly share code, notes, and snippets.

@samirfor

samirfor/revoke_pgsql_user.md

Last active January 19, 2024 13:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save samirfor/67ff9c6d4fac0c4dc46ff73d9aeb0a0a to your computer and use it in GitHub Desktop.
Save samirfor/67ff9c6d4fac0c4dc46ff73d9aeb0a0a to your computer and use it in GitHub Desktop.
Download ZIP
Revoke a user on PostgreSQL
Raw
revoke_pgsql_user.md 
DBNAME=# \du
                                    List of roles
  Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
 user        |                                                            | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 dbuser      |                                                            | {}

DBNAME=# REASSIGN OWNED BY dbuser TO postgres;
REASSIGN OWNED
DBNAME=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM dbuser;
REVOKE
DBNAME=# REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM dbuser;
REVOKE
DBNAME=# REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM dbuser;
REVOKE
DBNAME=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM dbuser;
REVOKE
DBNAME=# DROP USER dbuser;
ERROR:  role "dbuser" cannot be dropped because some objects depend on it
DETAIL:  privileges for database DBNAME
DBNAME=# REVOKE ALL PRIVILEGES ON DATABASE DBNAME FROM dbuser;
ERROR:  database "remocao" does not exist
DBNAME=# REVOKE ALL PRIVILEGES ON DATABASE "DBNAME" FROM dbuser;
REVOKE
DBNAME=# DROP USER dbuser;
DROP ROLE
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment