PostgreSQL. “Fatal error: failed to delete Postgres user.”

You are trying to delete a PostgreSQL database user and encounter the following error:

“Fatal error: failed to delete postgres user …, execute statement of revoke user was failed. error: pq: permission denied to reassign objects.”

This post is especially for you.

The sequence of PostgreSQL commands must be executed to solve this problem.

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE GRANT OPTION FOR ALL PRIVILEGES ON TABLES FROM the_db_user_you_are_trying_to_delete;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE GRANT OPTION FOR ALL PRIVILEGES ON SEQUENCES FROM the_db_user_you_are_trying_to_delete;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE GRANT OPTION FOR ALL PRIVILEGES ON FUNCTIONS FROM the_db_user_you_are_trying_to_delete;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ROUTINES FROM the_db_user_you_are_trying_to_delete;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE GRANT OPTION FOR ALL PRIVILEGES ON TYPES FROM the_db_user_you_are_trying_to_delete;

REVOKE GRANT OPTION FOR ALL PRIVILEGES ON SCHEMA public FROM the_db_user_you_are_trying_to_delete;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM the_db_user_you_are_trying_to_delete;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM the_db_user_you_are_trying_to_delete;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM the_db_user_you_are_trying_to_delete;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA public FROM the_db_user_you_are_trying_to_delete;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA public FROM the_db_user_you_are_trying_to_delete;

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON TABLES FROM the_db_user_you_are_trying_to_delete;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON SEQUENCES FROM the_db_user_you_are_trying_to_delete;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON FUNCTIONS FROM the_db_user_you_are_trying_to_delete;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON ROUTINES FROM the_db_user_you_are_trying_to_delete;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON TYPES FROM the_db_user_you_are_trying_to_delete;

REVOKE ALL PRIVILEGES ON SCHEMA public FROM the_db_user_you_are_trying_to_delete;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM the_db_user_you_are_trying_to_delete;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM the_db_user_you_are_trying_to_delete;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM the_db_user_you_are_trying_to_delete;
REVOKE ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA public FROM the_db_user_you_are_trying_to_delete;
REVOKE ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA public FROM the_db_user_you_are_trying_to_delete;

REVOKE CONNECT ON DATABASE postgres FROM the_db_user_you_are_trying_to_delete;

DROP ROLE the_db_user_you_are_trying_to_delete;

You can optimize this solution by using parameters in the SQL script. Then, simply execute the SQL script providing the parameter.

cat my_script.sql

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE GRANT OPTION FOR ALL PRIVILEGES ON TABLES FROM :role_name;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE GRANT OPTION FOR ALL PRIVILEGES ON SEQUENCES FROM :role_name;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE GRANT OPTION FOR ALL PRIVILEGES ON FUNCTIONS FROM :role_name;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ROUTINES FROM :role_name;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE GRANT OPTION FOR ALL PRIVILEGES ON TYPES FROM :role_name;

REVOKE GRANT OPTION FOR ALL PRIVILEGES ON SCHEMA public FROM :role_name;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM :role_name;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM :role_name;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM :role_name;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA public FROM :role_name;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA public FROM :role_name;

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON TABLES FROM :role_name;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON SEQUENCES FROM :role_name;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON FUNCTIONS FROM :role_name;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON ROUTINES FROM :role_name;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL PRIVILEGES ON TYPES FROM :role_name;

REVOKE ALL PRIVILEGES ON SCHEMA public FROM :role_name;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM :role_name;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM :role_name;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM :role_name;
REVOKE ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA public FROM :role_name;
REVOKE ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA public FROM :role_name;

REVOKE CONNECT ON DATABASE postgres FROM :role_name;

DROP ROLE :role_name;

Here’s how you can execute SQL while passing parameters in the command line.

psql -h localhost -p 5432 -U postgres -d postgres -f my_script.sql -v role_name="\"the_db_user_you_are_trying_to_delete\""

ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
REVOKE
REVOKE
REVOKE
REVOKE
REVOKE
REVOKE
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
REVOKE
REVOKE
REVOKE
REVOKE
REVOKE
REVOKE
REVOKE
DROP ROLE