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" default permissions 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