Drop Default Privileges?

On Tuesday, June 19, 2018, Louis Battuello <louis.battuello@etasseo.com>
wrote:

Is it possible to drop default privileges

https://www.postgresql.org/docs/10/static/sql-alterdefaultprivileges.html ?

David J.

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 couldedit the dump and replace the string 'ROLE postgres' withthe correct useron the RDS instance.

regards,

fabio pardi

On Jun 19, 2018, at 11:38 AM, Fabio Pardi <f.pardi@portavita.eu> 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

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@etasseo.com>
wrote:

вт, 19 июн. 2018 г. в 21:32, Pavan Teja <pavan.postgresdba@gmail.com>:

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

`psql` allows you to check default privileges via `\ddp` command (per
database). You can start `psql` with `-E` switch that will show you
internal queries used for displaying this information, or you can `\set
ECHO_HIDDEN on` with the same effect.

Also, you can do `pg_dumpall -s | grep -E 'DEFAULT PRIVILEGE|\\connect' and
it'll produce a list of all entries for all databases, along with database
name.

--
Victor Yegorov

вт, 19 июн. 2018 г. в 18:20, Louis Battuello <louis.battuello@etasseo.com>:

Is it possible to drop default privileges?

I’m attempting to run a pg_restore into an RDS instance, which doesn’t
have a “postgres” user.

I encounter many messages like so:

ALTER DEFAULT PRIVILEGES...

pg_restore: [archiver (db)] Error from TOC entry 10182; 826 253752252 DEFAULT
ACL DEFAULT PRIVILEGES FOR TABLES postgres

pg_restore: [archiver (db)] could not execute query: ERROR: role
"postgres" does not exist

Command was: ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA
abc_schema REVOKE ALL ON TABLES FROM PUBLIC;

I’d like to remove these default privileges on the source database to
avoid this error message, but I can’t find the syntax in the documentation
(or if it’s possible). I only see GRANT/REVOKE options.

If you see `ALTER DEFAULT PRIVILEGES … REVOKE …` and want to undo it, you
will have to GRANT corresponding privilege.

--
Victor Yegorov

On Tue, Jun 19, 2018 at 11:31 AM, Pavan Teja <pavan.postgresdba@gmail.com>
wrote:

Once I tried finding the list of default privileges, but left with no clue.

​Start here:

https://www.postgresql.org/docs/10/static/catalog-pg-default-acl.html

David J.
​