2

On my Google Cloud SQL database instance (Postgres 9.6), I am unable to drop a user/role via the GCP Console that was created via the GCP Console. When I try, the Console shows a notification that says "User deleted", then upon further inspection I see that the operation failed with an "Unknown error". The instance's error logs show the following error:

06:42:36.347 UTC [199]: [1-1] db=cloudsqladmin,user=cloudsqlagent ERROR:  role "user1" cannot be dropped because some objects depend on it
06:42:36.347 UTC [199]: [2-1] db=cloudsqladmin,user=cloudsqlagent DETAIL:  privileges for database mydb
24 objects in database mydb

I am able to see the 24 objects by running

SELECT distinct table_name
FROM information_schema.role_table_grants
WHERE grantee = 'user1'

Following the advice at https://cloud.google.com/sql/docs/postgres/create-manage-users#deleting_users and I know that I must reassign the ownership to another role (postgres) before dropping the user, but when I attempt to run the following script (from https://stackoverflow.com/questions/51256454/cannot-drop-postgresql-role-error-cannot-be-dropped-because-some-objects-depe):

REASSIGN OWNED BY user1 TO postgres;
DROP OWNED BY user1;
DROP USER user1;

I am presented with a permissions error on the REASSIGN command (ERROR: permission denied to reassign objects) due to the fact that Cloud SQL doesn't give my user SUPERUSER status (https://cloud.google.com/sql/docs/postgres/users#superuser_restrictions). I am also unsure of how to grant SUPERUSER status to a user in a Cloud SQL database instance; my assumption is that I cannot.

This issue is also occurring when using the gcloud command of the Cloud Shell. The output is similar:

$ gcloud sql users delete user1 --instance=<INSTANCE_ID_REDACTED>
user1@None will be deleted. New connections can no longer be made
using this user. Existing connections are not affected.

Do you want to continue (Y/n)? Y

Deleting Cloud SQL user...failed.
ERROR: (gcloud.sql.users.delete) [INTERNAL_ERROR]

Important information:

  • Postgres Version 9.6
  • A read replica is attached to this instance
  • Yes, I have tried turning it off and on again. No, the restart of both the main instance and the read replica did not solve this issue.
  • All database schema migrations and data migrations on this instance have been accomplished using Flask-Alembic with a very standard configuration
  • I can verify that no users have altered the schema outside of the Alembic process in our Cloud Build configuration
  • The user was created in the GCP console
  • No manual alterations have been made to the user, privileges, or object ownership
5
  • Can you run alter table ... owner to postgres; manually for those tables? – a_horse_with_no_name Dec 4 '20 at 7:58
  • Good question. I can run that successfully. But when I ran SELECT * FROM pg_tables; beforehand the tableowner column for each table in question was already the postgres user. – Joe Hawkins Dec 4 '20 at 8:09
  • Then maybe there are some grants on those table. What about revoke all on ... from user1; for each of these tables? – a_horse_with_no_name Dec 4 '20 at 8:10
  • You're a beaut. REVOKE ALL ON ALL TABLES IN SCHEMA public from user1; worked like a dream. User is deleted! Answer it and I'll accept the answer. – Joe Hawkins Dec 4 '20 at 8:21
  • For a couple of users I had to also run REVOKE ALL ON DATABASE mydb from user1; in addition to the other command. 🤷‍♂️. Now comes the fun part of figuring out how the DB got in this state to begin with – Joe Hawkins Dec 4 '20 at 8:27
1

06:42:36.347 UTC [199]: [2-1] db=cloudsqladmin,user=cloudsqlagent DETAIL: privileges for database mydb 24 objects in database mydb

Crossdatabase queries in PostgreSQL are not implemented. PostgreSQL will see some dependencies with the specified pg_catalog.pg_shdepend.dbid, but will not be able to query the system catalog of that database to determine which objects they are. So, postgresql does hint only database names in error details for other databases. (this is also a reason why PostgreSQL has no drop user ... cascade - we can't modify or even read objects description from another database)

But if you connect to the database specified in the details and try to delete a user, you will get a much more detailed description:

postgres=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  3 objects in database m2
postgres=# \connect m2 
You are now connected to database "m2" as user "postgres".
m2=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for table foo
privileges for table foofk
owner of table test

In this case user1 is owner for one table (REASSIGN OWNED will solve this easily) and some priviilegies on another two tables (REASSIGN OWNED will not touch this).

Certain commands are required for different objects (revoke for permissions, alter default privileges for default privileges and so on), unfortunately, there is no omnipotent ring to manage all dependencies even in one database.

2
  • The advice about getting better detail in the error messages produced by running the command directly in the database is great. My question explicitly calls out that due to the fact that this database is running on Google Cloud SQL I am unable to run REASSIGN OWNED. – Joe Hawkins Dec 8 '20 at 3:21
  • You can also control ownership with specific commands for each type of object: alter table ... owner to, alter sequence .. owner to, and so on. Yes, reassign owned is not always a possible solution. As I already wrote, you may need different commands for different depend objects. – Melkij Dec 8 '20 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.