DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

Top comments (0)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.

Eliminate Context Switching and Maximize Productivity

Pieces.app

Pieces Copilot is your personalized workflow assistant, working alongside your favorite apps. Ask questions about entire repositories, generate contextualized code, save and reuse useful snippets, and streamline your development process.

Learn more

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay