Postgres Pro
Downloads
Home   >   mailing lists

Thread: Which commands are guaranteed to drop role

Which commands are guaranteed to drop role

From
"Andrus"
Date:
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

Re: Which commands are guaranteed to drop role

From
Laurenz Albe
Date:
On Sun, 2020-04-12 at 00:25 +0300, Andrus wrote:
> User "currentuser"  tries to delete role "roletodelete" from this database using 
> 
> 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 ?

You cannot write such a script, but you will have to REVOKE and change ownership
and ALTER DEFAULT PRIVILEGES until no more dependencies on the role exist.

That is why you are well advised not to grant permissions to a role that you
plan to drop.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Which commands are guaranteed to drop role

From
"Andrus"
Date:
Hi!

>You cannot write such a script, but you will have to REVOKE and change ownership
and ALTER DEFAULT PRIVILEGES until no more dependencies on the role exist.

I ran script as superuser. In this case more detailed information appears:

ERROR: role "roletodelete" cannot be dropped because some objects depend on it

DETAIL:  privileges for default privileges on new relations belonging to role currentuser in schema public
privileges for default privileges on new relations belonging to role currentuser schema firma1

I changed script to 

do $$
    DECLARE r record;
    begin
      for r in select * from pg_views where schemaname IN ('public','firma1')
      loop
        execute 'revoke all on ' || quote_ident(r.schemaname) ||'.'|| quote_ident(r.viewname) || ' from roletodelete
cascade';
      end loop;
    end $$;
GRANT roletodelete TO currentuser;
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 all functions in schema public,firma1 from roletodelete cascade;
revoke all on schema public,firma1 from roletodelete cascade;
REVOKE CONNECT ON DATABASE mydb from roletodelete cascade;
revoke all on database mydb from roletodelete cascade;

revoke mydb_owner  from roletodelete  cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES from roletodelete cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  revoke all ON sequences from roletodelete cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  revoke all ON functions from roletodelete cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1  revoke all ON types from roletodelete cascade;
ALTER DEFAULT PRIVILEGES revoke all ON schemas from roletodelete cascade;

REVOKE USAGE ON SCHEMA public,firma1 FROM roletodelete cascade;
reassign owned by roletodelete to mydb_owner;
drop owned by roletodelete;
drop role roletodelete;

In this case it deleted user if was run under superuser postgres.

Non-superuser still cannot delete user using this script. How to allow non-superuser to dete user also ?

>That is why you are well advised not to grant permissions to a role that you
>plan to drop.

Role represents person. ODBC connection is used. Person rights should be restricted in database in this case.

How to simplify this script so that user will always deleted ?
Maybe some parts of script are not necessary.

Why postgres does not have simple command like 

drop role roletodelete reassign owned to currentuser cascade

but requires 25-line script for this.
Some parts of this script will not work in Postgres 9.0 probably. How to add 9.0+ support for it. 
Revoking privileges from view in not required in earlier releases.

Andrus.

AW: Which commands are guaranteed to drop role

From
"Zwettler Markus (OIZ)"
Date:
> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe <laurenz.albe@cybertec.at>
> Gesendet: Sonntag, 12. April 2020 22:09
> An: Andrus <kobruleht2@hot.ee>; pgsql-general <pgsql-general@postgresql.org>
> Betreff: Re: Which commands are guaranteed to drop role
> 
> On Sun, 2020-04-12 at 00:25 +0300, Andrus wrote:
> > User "currentuser"  tries to delete role "roletodelete" from this
> > database using
> >
> > 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 ?
> 
> You cannot write such a script, but you will have to REVOKE and change
> ownership and ALTER DEFAULT PRIVILEGES until no more dependencies on the
> role exist.
> 
> That is why you are well advised not to grant permissions to a role that you plan to
> drop.
> 
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
> 
> 



Is there a query to get all missing privileges not revoked yet?

Markus