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.
