Dropping a user in PostgreSQL

We may need to drop the users who no longer need access to PostgreSQL. There will also be a great challenge when a user who is being dropped owns one or more objects. In this case, we may have to re-assign the ownership to another user before dropping the user without dropping the objects it owns. In this recipe, we shall see how a user can be dropped safely and the best practices you can follow to avoid dropping the objects a user owns.

Getting ready

To drop a user, a simple DROP USER or DROP ROLE command is sufficient. However, this only works without errors when there are no objects owned by the user that is being dropped. Otherwise, an error like the following appears in such cases:

postgres=# DROP USER percuser;
 ERROR: role "percuser" cannot be dropped because some objects depend on it
 DETAIL: privileges for database percona
 2 objects in database pmm
 2 objects in database percona

How to do it ...

The following steps need to be followed to complete the recipe:

1. When a user that does not own any objects has to be dropped, it could be done using a simple command, as follows:

$ psql -c "DROP USER percuser"

2. Re-assign the ownership of objects that are owned by the user that needs to be dropped:

$ psql -U postgres -d percona -c "REASSIGN OWNED by percuser TO pmmuser"

3. Revoke the privileges from the user that is being dropped:

$ psql
 psql (13.1)
 Type "help" for help.
 postgres=# REVOKE ALL PRIVILEGES ON DATABASE percona FROM percuser;
 REVOKE
 postgres=# REVOKE ALL PRIVILEGES ON DATABASE pmm FROM percuser;
 REVOKE

4. Drop the user after the revocation is successful:

postgres=# DROP USER percuser ;
DROP ROLE

How it works ...

When the user who needs to be dropped does not own any objects, the command to drop a user as seen in step 1 would succeed without any errors. However, if the user is an OWNER of one or more objects of one or more databases, then the ownership of the objects owned by the user being dropped must be reassigned to another user. This can be done using REASSIGN OWNED as a superuser.

Dropping a user that owns one or more objects of one or more databases can be done using three simple steps. The first step is to reassign the ownership of the objects owned by the user to another user. This can be done using a simple command as seen in step 2.

If there exists another database that has some objects owned by the user being dropped, the ownership of the objects must be reassigned separately for the objects in that database as well:

$ psql -U postgres -d pmm -c "REASSIGN OWNED by percuser TO pmmuser"

Notice the difference in the two commands – the one we saw in step 2 and the preceding command. The first one connects to the percona database and the second command connects to the pmm database and reassigns ownership to pmmuser.

Once the ownership of the objects has been reassigned using the command mentioned in step 2, all the privileges owned by that user must be revoked using the command seen in step 3.

After revoking the privileges from the user being dropped, we can safely use the DROP USER command to drop the user as seen in step 4.