How to Drop User from Postgres Database
PostgresqlPostgresql Problem Overview
I have the following postgres users which I can view by invoking the \du
command in the terminal as follows:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication | {}
tutorial1 | | {}
According to the postgres documentation, I should be able to drop the user called "tutorial1" by entering the following command:
postgres=# DROP USER tutorial1
However, when I use that command nothing happens. The documentation doesn't provide any hints as to why this isn't working, nor does it provide clear examples.
That being said-- what is the command to drop this user?
Postgresql Solutions
Solution 1 - Postgresql
I've 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
Solution 2 - Postgresql
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, E'\n') LIKE '%postgres%';
I'm 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.
Solution 3 - Postgresql
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)
Solution 4 - Postgresql
First in Ubuntu terminal actions:
$ sudo -su postgres
$ psql postgres
then in postgres' terminal:
# \du
# drop user 'user_name';
# \du
[NOTE]:
Don't forget the semicolon ";"
[UPDATE]:
Solution 5 - Postgresql
The answer from Timofey almost worked for me, but I also needed to revoke permissions at schema level. So my drop script looked like this:
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;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM tutorial1;
DROP USER tutorial1;
So revoking may need to happen at any of the levels that happen here: https://www.postgresql.org/docs/9.1/static/sql-revoke.html
EDIT:
While attempting this again I got the error
> ERROR: role cannot be dropped because some objects depend on it
So I had to add the code from this answer to my script https://stackoverflow.com/a/51257346/9540123
Solution 6 - Postgresql
In case you have dot in user name test.user
drop user test.user;
ERROR: 42601: syntax error at or near "."
drop user "test.user";
Should solve it.