Safely Remove a PostgreSQL Role with dropuser

You will remove one PostgreSQL role from a chosen server, with a confirmation prompt and a read-only check before and after the change. The examples match the PostgreSQL 16.15 client installed on this machine. Allow about ten minutes, plus longer if the role owns objects or has been granted privileges across several databases.

1. Check the client and choose the connection

First confirm which executable will run and record its version:

$ command -v dropuser
/usr/bin/dropuser
$ dropuser --version
dropuser (PostgreSQL) 16.15

The role named at the end of the command is the role to remove. The account used to connect is separate and is supplied with -U or PGUSER. If you omit connection options, libpq uses its normal defaults, including PGHOST, PGPORT and PGUSER.

Make the target explicit when there is any chance of connecting to the wrong cluster:

$ psql -h db.example.test -p 5432 -U admin -d postgres -Atc \
  "SELECT current_user, current_database(), inet_server_addr(), inet_server_port();"
admin|postgres|192.0.2.25|5432

Replace the example host, port and account with values for your environment. This query reads connection information and does not change the server. Do not use sudo to run the client: PostgreSQL privileges, not Linux root, decide whether the operation is allowed.

2. Inspect the role before deletion

Check that the role exists and note whether it is a superuser. The query is read-only:

$ psql -h db.example.test -p 5432 -U admin -d postgres -P pager=off \
  -c "SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolname = 'app_old';"
 rolname  | rolsuper | rolcanlogin
----------+----------+-------------
 app_old  | f        | t
(1 row)

Change app_old to the exact role name. SQL string literals are case-sensitive here. A quoted PostgreSQL identifier such as "App_Old" is not the same name as the unquoted-looking app_old. If the query returns no rows, stop and recheck the name and connection instead of adding --if-exists blindly.

3. Check ownership and grants across the cluster

PostgreSQL refuses to remove a role that is still referenced in any database of the cluster. In each database that matters, check objects owned by the role and privileges granted to it. Run these read-only queries while connected to that database:

$ psql -h db.example.test -p 5432 -U admin -d appdb -P pager=off \
  -c "SELECT count(*) AS owned_objects FROM pg_class WHERE relowner = 'app_old'::regrole;"
 owned_objects
---------------
             0
(1 row)
$ psql -h db.example.test -p 5432 -U admin -d appdb -P pager=off \
  -c "SELECT datname FROM pg_database WHERE datallowconn ORDER BY datname;"
  datname
----------
 appdb
 postgres
 template1
(3 rows)

The ownership query is a useful first pass for ordinary tables, sequences, views and similar catalogue entries. It is not a complete migration plan for every object type or every database. If it finds owners, decide whether to transfer ownership with REASSIGN OWNED or remove owned objects with DROP OWNED. Those commands change state and can themselves be destructive, so review their scope in every affected database before using them.

Role memberships do not need a separate cleanup step: dropping the role automatically revokes memberships involving it. Other roles are not dropped. Privileges granted to the role and objects it owns are the boundaries that usually require deliberate preparation.

4. Remove the role with a confirmation prompt

Use --interactive and give the connection details explicitly. Add --echo when you want the generated SQL visible in the terminal:

$ dropuser -h db.example.test -p 5432 -U admin -i -e app_old
Role "app_old" will be permanently removed.
Are you sure? (y/n) y
DROP ROLE app_old;

Only enter y after checking the host, connecting account and role name. The prompt is a final confirmation, not a transaction preview. If you answer n, the role remains. The operation normally needs a PostgreSQL superuser for a superuser target; for a non-superuser target, the connecting role needs CREATEROLE and ADMIN OPTION on the target role.

If authentication requires a password, the client prompts automatically. Use -W to request the prompt before the initial connection attempt, or -w to forbid prompting in a batch job. Never put a password in this command line, because it can be exposed through process inspection or shell history.

5. Verify the result

Query the same server and database connection again:

$ psql -h db.example.test -p 5432 -U admin -d postgres -Atc \
  "SELECT 1 FROM pg_roles WHERE rolname = 'app_old';"
$ printf '%s\n' "$?"
0

No row is the expected result. The printed status belongs to the psql command immediately before it. If you need a clearer shell check:

if psql -h db.example.test -p 5432 -U admin -d postgres -Atqc \
    "SELECT 1 FROM pg_roles WHERE rolname = 'app_old';" | grep -qx 1; then
    printf '%s\n' 'role still exists'
    exit 1
else
    printf '%s\n' 'role is absent'
fi

This verifies the catalogue on the selected connection. It does not restore the role or recover objects that were removed during ownership cleanup.

Common failures and recovery

An error saying the role cannot be dropped because it is referenced means the preparatory work is incomplete. Identify the owning objects and privileges, then choose reassignment or removal deliberately. Do not respond by retrying with --if-exists; that option only suppresses the error when the role does not exist and emits a notice in that case.

A permission error usually means the connecting PostgreSQL role is not authorised, not that Linux elevation is missing. Connect with the approved administrative role or ask a database administrator to perform the change. If the command reaches the wrong host or port, stop immediately and repeat the connection query from step 1.

There is no general undo command for a dropped role. Recovery means recreating an appropriate role and restoring any required ownership, memberships and grants from a reviewed inventory or backup. Keep that record before deletion if the role might be needed again.