Removing a User in CloudSQL blocked by dependent object

[jackson_aisle]

I am trying to remove a user from our CloudSQL Postgres database, but am getting the following error when trying to do so:

Invalid request: failed to delete user <user>: . role "<user>" cannot be dropped because some objects depend on it Details: 1 object in database<database>.

I have followed the steps outlined  in this topic, which brought the number of objects down from 150 objects to 1, but I am unable to figure out what the last remaining object is, and its blocking us from removing this user.

Not sure if this is an important detail but we have a single read replica set up for this database.

[greb · Accepted solution]

Hi @jackson_aisle,

While trying out the suggestions by @ms4446, I'd like to reiterate my insights with regard to the error encountered.

The error you're encountering, "role cannot be dropped because some objects depend on it", signifies that the role still possesses or is linked to an object within the database. Although you have considerably decreased the dependent objects, Cloud SQL could still be seeing some attachments existing between the user and other objects: . 

I’d like to provide my suggestions too that might help:

1. Find the dependent object: See issue “A user cannot be deleted” from Manage Instances section. This step identifies the object (like a table, view, sequence, or other database entity) that still depends on the user <user>.
2. Resolve the Dependency:
   > Transfer Ownership: I’d assume you’re using PostgreSQL. If the object is still needed, reassign ownership to another role or user with the use of “OWNER TO”
   > Drop the Object: If the object is no longer needed, remove it from the database
   
3. Drop the user: This ensures the role is fully removed from the database without any lingering dependencies.
   

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[ms4446]

Please ensure you take the following steps:

1. Identify the Dependent Object

Check for Functions and Procedures

SELECT proname FROM pg_proc WHERE proowner = (SELECT oid FROM pg_roles WHERE rolname = '<user>');

This query will list all functions and procedures owned by the user.

Reassign Ownership:

ALTER FUNCTION <schema_name>.<function_name> OWNER TO <new_owner>;

Drop the Function/Procedure:

DROP FUNCTION <schema_name>.<function_name>;

 If the function is overloaded (multiple versions with different parameters), include the parameter types in the DROP FUNCTION statement, e.g., DROP FUNCTION function_name(param_type);.

Check for Schemas

SELECT nspname FROM pg_namespace WHERE nspowner = (SELECT oid FROM pg_roles WHERE rolname = '<user>');

This query will list all schemas owned by the user.

Reassign Ownership:

ALTER SCHEMA <schema_name> OWNER TO <new_owner>;

Drop the Schema:

DROP SCHEMA <schema_name> CASCADE;

Check for Types

SELECT typname FROM pg_type WHERE typowner = (SELECT oid FROM pg_roles WHERE rolname = '<user>');

This query will list all custom types owned by the user.

Reassign Ownership:

ALTER TYPE <schema_name>.<type_name> OWNER TO <new_owner>;

Drop the Type:

DROP TYPE <schema_name>.<type_name>;

Check for Tablespaces

SELECT spcname FROM pg_tablespace WHERE spcowner = (SELECT oid FROM pg_roles WHERE rolname = '<user>');

This query will list all tablespaces owned by the user.

Reassign Ownership:

ALTER TABLESPACE <tablespace_name> OWNER TO <new_owner>;

Drop the Tablespace:

DROP TABLESPACE <tablespace_name>;

Check for Extensions

SELECT extname FROM pg_extension WHERE extowner = (SELECT oid FROM pg_roles WHERE rolname = '<user>');

This query will list all extensions owned by the user.

Reassign Ownership:

ALTER EXTENSION <extension_name> OWNER TO <new_owner>;

2. Re-attempt User Deletion

Once all dependent objects have been reassigned or dropped, delete the user:

DROP USER <user>;

3. Considerations for Read Replica

Changes on the Primary:

Changes made to the primary database (like dropping a user) will be automatically replicated to the read replica. Manual intervention on the read replica is not required.

Important Point:

Ensure that the user being dropped is not used for replication-related processes. Dropping a user involved in replication (e.g., owning replication slots, publications, or subscriptions) will disrupt replication. Verify with the following queries:

SELECT * FROM pg_publication WHERE pubowner = (SELECT oid FROM pg_roles WHERE rolname = '<user>');
SELECT * FROM pg_subscription WHERE subowner = (SELECT oid FROM pg_roles WHERE rolname = '<user>');

[jackson_aisle]

These are the sames steps that you outlined in the linked thread from my original post. It seems this solution isn't complete in the default privileges will also block deleting a user with the same error message.

[greb · Accepted solution]

Hi @jackson_aisle,

While trying out the suggestions by @ms4446, I'd like to reiterate my insights with regard to the error encountered.

The error you're encountering, "role cannot be dropped because some objects depend on it", signifies that the role still possesses or is linked to an object within the database. Although you have considerably decreased the dependent objects, Cloud SQL could still be seeing some attachments existing between the user and other objects: . 

I’d like to provide my suggestions too that might help:

1. Find the dependent object: See issue “A user cannot be deleted” from Manage Instances section. This step identifies the object (like a table, view, sequence, or other database entity) that still depends on the user <user>.
2. Resolve the Dependency:
   > Transfer Ownership: I’d assume you’re using PostgreSQL. If the object is still needed, reassign ownership to another role or user with the use of “OWNER TO”
   > Drop the Object: If the object is no longer needed, remove it from the database
   
3. Drop the user: This ensures the role is fully removed from the database without any lingering dependencies.
   

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[jackson_aisle · Accepted solution]

Thank you for the help.

What ended up working being the issue was default privileges for the role, which was not listed when running the commands listed by @ms4446 . The fix was running  `ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON TABLES FROM <username>;`, which was mentioned by the original poster of this stack exchange thread linked in the docs you referenced.

I also noticed that trying to drop the role directly through postgres (as opposed to the cloud sql console) gave a more descriptive error message that helped me identify the blocking objects.