[SOLVED] Unable to drop default privileged user in Postgresql

Unable to drop default privileged user in Postgresql

When we are trying to drop 1 particular user in PostgreSQL Datbase we are getting below error.

postgres=# DROP user xyz;

ERROR: role "xyz" cannot be dropped because some objects depend on it
DETAIL: owner of default privileges on new functions belonging to role xyz

Then we tried to check the default privileges by below command.

postgres=# \ddp xyz

    Default access privileges
  Owner  | Schema |   Type   | Access privileges
---------+--------+----------+-------------------
     xyz |       | function |

Also we tried below command but no luck for us.

postgres=# ALTER DEFAULT PRIVILEGES FOR ROLE xyz REVOKE ALL ON FUNCTIONS FROM xyz ;
ALTER DEFAULT PRIVILEGES
postgres=# ALTER DEFAULT PRIVILEGES FOR ROLE xyz REVOKE ALL ON FUNCTIONS FROM xyz ;
ALTER DEFAULT PRIVILEGES
postgres=# \ddp xyz

     Default access privileges
  Owner  | Schema |   Type   | Access privileges
---------+--------+----------+-------------------
     xyz |       | function |

Solution

Since the “default” default privilege for functions is “EXECUTE for everyone and the owner”. you'll have to restore that default:

ALTER DEFAULT PRIVILEGES FOR ROLE xyz GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE xyz GRANT EXECUTE ON FUNCTIONS TO xyz;

Then you should be able to drop the role;