Delete User Cloud SQL (Postgre SQL)

[RendyH97]

Hi There,

I am using Cloud SQL for Postgre. I want to delete one of the users in the existing DB, but there is an error below:
Invalid request: failed to delete user <user>: . role "<user>" cannot be dropped because some objects depend on it Details: 1424 objects in database <database>.

I have checked using the query below:
SELECT
obj_description(c.oid) as obj_description,
nspname AS schema_name,
relname AS object_name,
CASE
WHEN relkind = 'r' THEN 'table'
WHEN relkind = 'v' THEN 'view'
WHEN relkind = 'm' THEN 'materialized view'
WHEN relkind = 'i' THEN 'index'
WHEN relkind = 'S' THEN 'sequence'
WHEN relkind = 's' THEN 'special'
WHEN relkind = 'f' THEN 'foreign table'
WHEN relkind = 'c' THEN 'composite type'
WHEN relkind = 't' THEN 'table (TOAST)'
END AS object_type
FROM
pg_class c
JOIN
pg_namespace n ON n.oid = c.relnamespace
WHERE
has_schema_privilege(n.oid, 'USAGE')
AND c.relowner = (SELECT oid FROM pg_roles WHERE rolname = '<user>')
ORDER BY
schema_name, object_name;

There is no database object owned by that role.

Has anyone ever experienced the same thing like this?

note :
<user> and <database> are just examples. not the realname

Thank you very much

[ms4446 · Accepted solution]

How to drop a user with dependencies in PostgreSQL for CloudSQL

Backup Reminder

Always take a backup of your database before making significant changes. This ensures you can restore the database to its previous state in case of any issues.

Reassign Ownership Instead of Dropping

Instead of dropping each object, it's often safer to reassign ownership to another user. Dropping objects, especially tables, can result in data loss.

To reassign ownership of a table, use the following SQL statement:

ALTER TABLE <table_name> OWNER TO <new_user>;

For example, to reassign ownership of the table my_table to the user my_new_user, you would use the following statement:

ALTER TABLE my_table OWNER TO my_new_user;

Revoke Privileges

If you have the necessary privileges, you can revoke all privileges associated with a user. This can help to prevent the user from accessing or modifying database objects.

To revoke all privileges associated with a user, use the following SQL statements:

REVOKE

ALL PRIVILEGES ON

ALL TABLES IN SCHEMA public FROM

<user>;
REVOKE

ALL PRIVILEGES ON

ALL SEQUENCES IN SCHEMA public FROM

<user>;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM <user>;

You can also use the following SQL statement to revoke all privileges granted to the user on all objects in the current database:

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM <user>;

Check for Other Dependencies

In addition to object ownership and privileges, there are other dependencies that can prevent a user from being dropped. These include:

• Roles granted to the user
• Roles the user is a member of
• Objects that reference the user

To check for other dependencies, you can use the following SQL query:

SELECT * FROM pg_depend WHERE depender = (SELECT oid FROM pg_roles WHERE rolname = '<user>');

This query will return a list of all objects in the database that have a dependency on the user.

Drop Owned Command

If you have the necessary privileges, you can use the DROP OWNED command to drop all objects owned by a user and to revoke privileges that the user has been granted on other objects:

DROP OWNED BY <user>;

However, this command should be used with caution, as it can result in data loss.

Contact CloudSQL Support

If you are unable to drop a user with dependencies due to limited privileges, you may need to contact CloudSQL support for assistance.

Conclusion

Dropping a user with dependencies in PostgreSQL for CloudSQL can be a complex and delicate operation. It's important to be aware of the potential risks and to take appropriate precautions before executing any commands. If you are unsure of how to proceed, please contact CloudSQL support for assistance.

[RendyH97]

Hi,

when I searched further, I finally found a way to search the 1424 objects that depend on the user database that makes the user cannot be deleted.

What I did was to run the DROP USER <user> query and then I went to the Cloud SQL Log Explorer page. In the Cloud SQL Logs, you can see all the objects that cause the user cannot be deleted (most of them are privileges object).

And then I followed your way to revoke many privileges using the revoke query:

-- Revoke privileges on the public schema
REVOKE ALL ON SCHEMA public FROM "<user>";

-- Revoke privileges on sequences
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM "<user>";

-- Revoke privileges on relations (tables)
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM "<user>"

-- Remove default privileges on sequences for the user database
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON SEQUENCES FROM "<user>";

-- Remove default privileges on relations (tables) for the user datbase
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON TABLES FROM "<user>";

-- Revoke privileges on the database
REVOKE ALL PRIVILEGES ON DATABASE <database> FROM <user>;

I can now safely delete unused database users.

Thank you very much for your help

[adams308-cp]

Sadly, i've done all of the above but i am still not being allowed to drop a user due to one dependency which cannot be found. I will have to get in touch with support regarding this.

[harsh-singh]

I am in a similar situation and there is this one dependency that won't let me remove a user. How did you solve it?