Unable to DROP EXTENSION in Cloud SQL for Postgres

[Ayan1303]

Hello, we encountered a problem: after deleting a user in the database, some extensions were left without an owner and now there is no way to update/delete/change their owner since there is no superuser.
Postgres lacks "ALTER EXTENSION OWNER TO" functionality
How can this be fixed and is it possible to connect to the database under cloudsqladmin?

[ms4446]

In Google Cloud SQL for PostgreSQL, certain advanced privileges are restricted for security reasons, including changing object ownership. When attempting to execute the command:

ALTER EXTENSION <extension_name> OWNER TO <new_user>;

with a user possessing the cloudsqlsuperuser role (or any other role), you might encounter the following error during database migration to GCP:

ERROR: must be owner of extension <extension_name>

This indicates that the cloudsqlsuperuser role's privileges do not encompass the ALTER EXTENSION OWNER TO command, especially when trying to assign new users and grants.

Workaround Steps

To address this, you can perform the following steps to create a new user and assign the necessary roles:

1. Connect to the Cloud SQL Instance: Connect to your Cloud SQL instance using a user account that has been granted the cloudsqlsuperuser role. This role offers elevated privileges that might be sufficient for many administrative tasks.

2. Determine the Current Extension Owner: Identify extensions with missing or undefined owners using the following query:
   
   

SELECT extname FROM pg_extension WHERE extowner NOT IN (SELECT oid FROM pg_roles);

3. Identify Roles for the New Owner: Ensure that the new owner has the appropriate role memberships. This can be checked using queries involving pg_roles and pg_auth_members.

4. Grant Permissions: If the new owner isn't a member of the necessary role, grant them permissions with:
   
   

GRANT <selected_role> TO <new_owner>;

5. Ensure CREATE Privileges for the Selected Role: Make sure that <selected_role> has CREATE privileges. If not, grant them with:
   
   

GRANT CREATE ON SCHEMA public TO <selected_role>;

(Note: Replace public with the appropriate schema name if your extension is in a different schema.)

6. Alter Extension Ownership:Attempt to change the ownership of the extension:
   
   

ALTER EXTENSION <extension_name> OWNER TO <new_owner>;

(Replace <extension_name> and <new_owner> with the actual extension name and new owner's username.)

Important Considerations

Due to the managed nature of Google Cloud SQL, some operations might still be restricted. If you encounter limitations or the above steps do not resolve your issue, contacting Google Cloud Support is recommended. They can assist with tasks that are beyond the scope of user-level privileges.

[Ayan1303]

Thanks, but as I said there is no such syntax as 

ALTER EXTENSION <extension_name> OWNER TO <new_owner>;

in PostgreSQL.

[ms4446]

You are correct, and I apologize for the confusion. The PostgreSQL ALTER EXTENSION command does not support changing the owner of an extension. This limitation applies to both standard PostgreSQL installations and managed services like Google Cloud SQL for PostgreSQL.

In a managed environment like Google Cloud SQL, where you don't have superuser privileges, you're limited in how you can manage extensions, especially when it comes to ownership and permissions. If an extension's owner has been deleted, and you're facing issues with managing that extension, here are some steps you might consider:

1. Contact Google Cloud Support: Since direct modification of extension ownership is not possible in your scenario, reaching out to Google Cloud Support is often the best course of action. They have higher-level access and might be able to make changes that are not available to end-users.

2. Recreate the Extension: If feasible, you could drop the extension (if it's not critical to current operations) and recreate it under a different user. This approach, however, should be taken with caution, as it might have implications on your database depending on the extension's role.

3. Review Extension Usage: If the extension is not critical or can be replaced with an alternative solution, consider removing it or finding another way to achieve the same functionality.

4. Database Migration: In extreme cases, if the extension is causing significant issues and cannot be managed effectively, migrating your database to a new instance might be a solution. This would be a more complex and time-consuming process but could resolve issues related to unmanageable objects in the database.

Remember, these steps can have significant impacts on your database and operations, so they should be considered carefully and ideally discussed with a database administrator or Google Cloud Support.