Re: Foreign Key Validation after Reference Table Ownership Change
Agreed. It would certainly make sense that user_2 have usage on the schema in order to operate against the table owned by user_2. I just found it confusing that the discrepancy would cause an issue for user_1, which had all necessary privileges on the schema and references on the reference table. Why would an issue with user_2’s ACL manifest itself with a foreign key validation on insert by user_1 on a table owned by user_1?
Re: Foreign Key Validation after Reference Table Ownership Change
> > The point is you can't resolve a name like "schema_1.something" unless > you have USAGE on schema_1. So the RI-checking query, which is run as > the owner of the table, fails at parse time. That certainly makes sense for user_2 that owns the reference table and is blocked by not having usage on the reference table’s schema. But, user_1 owns both schemas and has usage on both but no longer owns the reference table in one schema. Why is user_1’s insert on the referencing table failing? Is the validation of the FK no longer done as user_1?
Re: Foreign Key Validation after Reference Table Ownership Change
> On Mar 21, 2018, at 2:36 PM, David G. Johnston wrote: > > And altering an owner of a table to one lacking usage and create permissions > on the schema is possible but unadvisible. > > David J. Exactly. The cause of my mistake was changing the REFERENCED table ownership to a role without granting usage on the schema, too. However, with the error occurring when acting as user_1, I wasn’t clear on where the privilege mismatch occurred and which role’s privilege required correction.
Drop 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. Thanks, Louis
Re: Drop Default Privileges?
> On Jun 19, 2018, at 11:38 AM, Fabio Pardi 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
