Postgresql – ERROR: role “user1” cannot be dropped because some objects depend on it

google-cloud-sqlpostgresqlpostgresql-9.6

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

Best Answer

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.

Related Solutions

PostgreSQL Role – How to Restore Superuser Role to Postgres User

Your problem is this line:

c:\Program Files\PostgreSQL\9.5\bin>postgres.exe --single -D c:\Program Files\PostgreSQL\9.5\data

If there are spaces in a command line parameter you need to quote like this

c:\Program Files\PostgreSQL\9.5\bin>postgres.exe --single -D "c:\Program Files\PostgreSQL\9.5\data"

This isn't really a problem with Postgres but just how command line works.

PostgreSQL 9.3 – How to Refresh Materialized Views with Different Users

You can do this with a function that runs in the security context of its owner.

Function that refreshes the view (create it with the user that owns the MV/table):

CREATE OR REPLACE FUNCTION refresh_mvw1()
RETURNS void
SECURITY DEFINER
AS $$
BEGIN
REFRESH MATERIALIZED VIEW mvw1 with data;
RETURN;
END;
$$ LANGUAGE plpgsql;

Grant execute on the function to any user that you want to be able to refresh the view:

-- Users have 'execute' permissions by default on functions!
revoke all on function refresh_mvw1() from public;
grant execute on function refresh_mvw1() to u1;

To refresh:

select refresh_mvw1();

From the Postgres docs:

SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it.

Version that supports a parameter:

CREATE OR REPLACE FUNCTION refresh_mv_xxx(table_name text)
RETURNS void
SECURITY DEFINER
AS $$
DECLARE sql text; 
BEGIN
sql := 'REFRESH MATERIALIZED VIEW ' || table_name || ' with data';
EXECUTE sql;
RETURN;
END;
$$ LANGUAGE plpgsql;

... but I'm not sure if dynamic SQL will still execute as the definer.

Important information:

Best Answer

Related Solutions

PostgreSQL Role – How to Restore Superuser Role to Postgres User

PostgreSQL 9.3 – How to Refresh Materialized Views with Different Users

Related Question