Обсуждение: Drop Default Privileges?
I’m attempting to run a pg_restore into an RDS instance, which doesn’t have a “postgres” user.
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;
Is it possible to drop default privileges
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
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.Thanks,Louis
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
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
In order to remove the default privileges for any particular user/role, we should know the list of default privileges.
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.
Once I tried finding the list of default privileges, but left with no clue.