6

I am attempting to drop a role and have severed all ties to it that I can locate, but there is one lingering issue I cannot resolve. When I run this:

drop role hank

It tells me:

ERROR:  role "hank" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new functions belonging to role brandon in schema alteryx
privileges for default privileges on new relations belonging to role brandon in schema alteryx

This DDL exists on the schema:

ALTER DEFAULT PRIVILEGES IN SCHEMA alteryx
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
TO hank;

ALTER DEFAULT PRIVILEGES IN SCHEMA alteryx
GRANT EXECUTE ON FUNCTIONS TO hank;

And when I execute the revoke on them, the command is successful, but the privileges remain intact.

I have scoured the DDL and can't locate how to resolve this without attempting a drop-cascade.

Any guidance is welcome.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Hambone
  • 15,600
  • 8
  • 46
  • 69

1 Answers1

17

You have to run the following two statements to get rid of the default privileges that block you:

ALTER DEFAULT PRIVILEGES FOR ROLE brandon IN SCHEMA alteryx
   REVOKE ALL ON TABLES FROM hank;
ALTER DEFAULT PRIVILEGES FOR ROLE brandon IN SCHEMA alteryx
   REVOKE ALL ON FUNCTIONS FROM hank;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    What is `brandon` and what is `hank` in this example? – Gajus Jul 06 '22 at 19:33
  • 1
    @Gajus Those are database users (roles) mentioned in the question. – Laurenz Albe Jul 06 '22 at 19:38
  • I am struggling to wrap my head around a similar scenario. In my case, I am trying to `drop role avnadmin` and I am getting an error "owner of default privileges on new functions belonging to role avnadmin in schema payments". I am not clear what is eqv. of `hank` in my case. I tried: `ALTER DEFAULT PRIVILEGES FOR ROLE ??? IN SCHEMA payments REVOKE ALL ON TABLES FROM avnadmin;` – Gajus Jul 06 '22 at 19:41
  • @Gajus Use `avnadmin` instead of both `brandon` and `hank` (and use the proper schema). – Laurenz Albe Jul 06 '22 at 19:47