Search code examples
postgresqlpgadmin-4azure-database-postgresql

Safely drop a user in Postgres

I am very new to Postgress security topic. I have used the following commands to create a user.

CREATE USER myuser WITH PASSWORD 'PASSWORD';
GRANT CONNECT ON DATABASE "MyDB" TO myuser;
GRANT ALL PRIVILEGES ON DATABASE "MyDB" TO myuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

Immediately afterwards I wanted to drop this user by I receive an error similar to:

ERROR:  role "tempuser" cannot be dropped because some objects depend on it
DETAIL:
privileges for view myView
privileges for table myTable
... [rest of views and tables]

It kinda scares me. How can I safely drop a user without affecting the data and other objects in database?

Solution

  • Reverse each of your GRANT with a corresponding REVOKE.

    It kinda scares me. How can I safely drop a user without affecting the data and other objects in database?

    If you consider a REVOKE to be "affecting the objects", then you can't drop the user without affecting the objects. Why does this scare you? As you can see, the system won't let you do this implicitly, so what is there to be scared of?