Can't remove default permissions entry

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Can't remove default permissions entry

Christophe Pettus-2
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    |

db=>

--
-- Christophe Pettus
   [hidden email]



Reply | Threaded
Open this post in threaded view
|

Re: Can't remove default permissions entry

Adrian Klaver-4
On 5/27/20 10:06 AM, 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!

Have you tried?:

https://www.postgresql.org/docs/12/sql-alterdefaultprivileges.html

"If you wish to drop a role for which the default privileges have been
altered, it is necessary to reverse the changes in its default
privileges or use DROP OWNED BY to get rid of the default privileges
                   ^^^^^^^^^^^^^
entry for the role."

So:

https://www.postgresql.org/docs/12/sql-drop-owned.html

>
> 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    |
>
> db=>
>
> --
> -- Christophe Pettus
>     [hidden email]
>
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Can't remove default permissions entry

Laurenz Albe
In reply to this post by Christophe Pettus-2
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