Which commands are guaranteed to drop role

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: Which commands are guaranteed to drop role
Date: 2020-04-11 21:25:08
Message-ID: E71C41A2E324446CB790F4421E3CABB1@dell2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

Database "mydb" is owned by role "mydb_owner".

User "currentuser" tries to delete role "roletodelete" from this database using

revoke all on all tables in schema public,firma1 from "roletodelete" cascade;
revoke all on all sequences in schema public,firma1 from "roletodelete" cascade;
revoke all on database mydb from "roletodelete" cascade;
revoke all on all functions in schema public,firma1 from "roletodelete" cascade;
revoke all on schema public,firma1 from "roletodelete" cascade;
revoke mydb_owner from "roletodelete" cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES from "roletodelete";
GRANT "roletodelete" TO "currentuser";
reassign owned by "roletodelete" to mydb_owner;
drop owned by "roletodelete";
drop role "roletodelete";

But got error

ERROR: role "roletodelete" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public;

How to create script which is guaranteed to delete role ?

This script already contains:

revoke all on schema public,firma1 from "roletodelete" cascade;

Why postgres complains that privileges for schema public depend on this role if they are revoked ?
How to fix this?

Andrus

Responses

Browse pgsql-general by date

  From Date Subject
Next Message AC Gomez 2020-04-11 21:44:12 Looping though schemas to grant access will work in PUBLIC loop iteration but fails on next iteration of user schema at: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA
Previous Message Adrian Klaver 2020-04-10 22:39:58 Re: Forcibly disconnect users from one database