Re: Drop Default Privileges?

Pavan Teja <pavan.postgresdba@xxxxxxxxx> · Wed, 20 Jun 2018 00:01:34 +0530

Hi Louis,
In order to remove the default privileges for any particular user/role, we should know the list of default privileges. 

If we know them we can revoke them as a super user. Once I tried finding the list of default privileges, but left with no clue. But I strongly believe that if we know the list of default privileges that will be assigned we may revoke them as a super user.

Regards,
Pavan

On Tue, Jun 19, 2018, 11:55 PM Louis Battuello <louis.battuello@xxxxxxxxxxx> wrote:

On Jun 19, 2018, at 11:38 AM, Fabio Pardi <f.pardi@xxxxxxxxxxxx> wrote:

    Hi
          Louis,
    I think 'alter user' can do the
                job for you.
    https://www.postgresql.org/docs/current/static/sql-alteruser.html

    Else, as alternative: before running pg_restore, you
              could edit the dump
                    and replace the
                      string 'ROLE
                      postgres' with the correct user on the RDS
                            instance.
    regards,
    fabio pardi

Thanks for your replies, David and Fabio.

I thought about editing the dump file or attempting some sort of reassignment of the default privileges, but that still leaves the larger question: can default privileges ever be removed specific to a single schema?

If I set a default of GRANT SELECT, is my only option to change it to REVOKE SELECT? Is there a way to “get rid of the default privileges entry for the role,” as referenced in the Notes section of the of the ALTER DEFAULT PRIVILEGES documentation? Reversing the change from GRANT to REVOKE still leaves a catalog reference to the postgres user oid in pg_default_acl.

I don’t want to reverse the default behavior. I’d like to remove it entirely.

Thanks,
Louis