postgresql – How to Drop User from Postgres Database

postgresql – How to Drop User from Postgres Database

Ive wasted way too much time on trying to find all the things to unwind. In addition to the above (or possibly as a complete replacement), refer to this answer to a similar question: https://stackoverflow.com/a/11750309/647581

DROP OWNED BY your_user;
DROP USER your_user;

did the trick for me

If you find yourself here (like me) because you are unable to drop the user, the following template may be helpful:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM tutorial1;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM tutorial1;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM tutorial1;
DROP USER tutorial1;

The user may have privileges in other schemas, in which case you will have to run the appropriate REVOKE line with public replaced by the correct schema. To show all of the schemas and privilege types for a user, I edited the dp command to make this query:

SELECT 
  n.nspname as Schema,
  CASE c.relkind 
    WHEN r THEN table 
    WHEN v THEN view 
    WHEN m THEN materialized view 
    WHEN S THEN sequence 
    WHEN f THEN foreign table 
  END as Type
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.array_to_string(c.relacl, En) LIKE %postgres%;

Im not sure which privilege types correspond to revoking on TABLES, SEQUENCES, or FUNCTIONS, but I think all of them fall under one of the three.

postgresql – How to Drop User from Postgres Database

Your command is ok but you forget to put ; at the end of the command.

Try like this

postgres=# DROP USER tutorial1; (Note I put semicolon at the end)

Leave a Reply

Your email address will not be published.