Re: Can't remove default permissions entry

On Wed, 2020-05-27 at 10:06 -0700, Christophe Pettus wrote:
> On RDS (thus, no superuser) we are trying to drop a user.  The only remaining item that the user owns is an "empty"
defaultpermissions entry, but we can't seem to get rid of it so that the user can
 
> be dropped:
> 
> I'm sure I'm missing something obvious!
> 
> Logged in as xyuser:
> 
> db=> \ddp+
>                     Default access privileges
>    Owner    |    Schema     |   Type   |    Access privileges     
> ------------+---------------+----------+--------------------------
>  xyuser     |               | table    | 
> 
> db=> ALTER DEFAULT PRIVILEGES FOR USER xyuser REVOKE ALL ON TABLES FROM xyuser;
> ALTER DEFAULT PRIVILEGES
> db=> \ddp+
>                     Default access privileges
>    Owner    |    Schema     |   Type   |    Access privileges     
> ------------+---------------+----------+--------------------------
>  xyuser     |               | table    | 

That's tricky one.

The answer must be that the empty entry is *not* a NULL (meaning default
privileges), but actually an empty entry, meaning nobody gets any privileges,
including the table owner.

The solution is to restore the default situation:

ALTER DEFAULT PRIVILEGES FOR ROLE xyuser GRANT ALL ON TABLES TO xyuser;

Then the offending entry should be gone.

It's probably too late to fix that, but in my opinion it was a BAD
design decision to use NULL to represent default privileges, at least
on display.

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