Thread: Blocked inserts on tables with FK to tables for which UPDATE has been revoked
Blocked inserts on tables with FK to tables for which UPDATE has been revoked
From
Samuel Gilbert
Date:
Hello, I have encountered a problem with inserts failing because of permissions issues when the table in which I try to insert has foreign key constraints to tables for which UPDATE has been revoked. The script bellow show how to reproduce the problem with a bare-bones test case. Reproducibility is 100% on PostgreSQL 8.2.5 (I know it's not even the latest revision of the 8.2 line, but it's what I have to work with :( ) I Googled the error message and a couple of meaningful keywords, searched the documentation and the mailing list archives without success. ---------------------------------------- CREATE DATABASE test WITH OWNER afsugil ENCODING 'LATIN1'; CREATE ROLE test WITH NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT LOGIN; \c test afsugil CREATE TABLE station ( id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE ); INSERT INTO station (name) VALUES ('Montreal'); INSERT INTO station (name) VALUES ('Toronto'); INSERT INTO station (name) VALUES ('Calgary'); INSERT INTO station (name) VALUES ('Vancouver'); INSERT INTO station (name) VALUES ('Halifax'); SELECT * FROM station; -- id | name -- ----+----------- -- 1 | Montreal -- 2 | Toronto -- 3 | Calgary -- 4 | Vancouver -- 5 | Halifax CREATE TABLE observation ( station INTEGER NOT NULL REFERENCES station (id) MATCH FULL, date TIMESTAMP NOT NULL, value REAL, PRIMARY KEY (station, date) ); -- The insert below works INSERT INTO observation (station, date, value) VALUES ( 1, '2010-07-22 14:00:00', 42 ); REVOKE UPDATE ON station FROM afsugil; -- The insert below no longer works INSERT INTO observation (station, date, value) VALUES ( 2, '2010-07-22 14:00:00', 14 ); -- ERROR: permission denied for relation station -- CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."station" x WHERE "id" = $1 FOR SHARE OF x" \c postgres afsudev DROP DATABASE test; DROP USER test; ---------------------------------------- This is a pretty severe issue for me since, I don't want users that need to input data to also have the right to modify references tables. This is, also, not how I would have expected the permissions to behave. Any help to resolve this issue will be greatly appreciated! Best Regards, Samuel
Re: Blocked inserts on tables with FK to tables for which UPDATE has been revoked
From
Alban Hertroys
Date:
On 23 Jul 2010, at 20:39, Samuel Gilbert wrote: > Hello, > > I have encountered a problem with inserts failing because of permissions > issues when the table in which I try to insert has foreign key constraints to > tables for which UPDATE has been revoked. > > The script bellow show how to reproduce the problem with a bare-bones test > case. Reproducibility is 100% on PostgreSQL 8.2.5 (I know it's not even the > latest revision of the 8.2 line, but it's what I have to work with :( ) I > Googled the error message and a couple of meaningful keywords, searched the > documentation and the mailing list archives without success. It's not a solution to your problem, but one observation I made in your test script: > CREATE DATABASE test WITH OWNER afsugil ENCODING 'LATIN1'; > > CREATE ROLE test WITH NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT > LOGIN; > \c test afsugil You create a new user, but you still connect with the user who created the database. > REVOKE UPDATE ON station FROM afsugil; And then you revoke rights from that user instead of from the test user. Effectively you're not using the 'test' user at all in your script. Was that intended? Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4c4abba6286216566810360!
Re: Blocked inserts on tables with FK to tables for which UPDATE has been revoked
From
Samuel Gilbert
Date:
Hello Alban, You are right on both count of me not using the "test" user. At first, it was set-up to use the "test" user, but later on, I wondered if owner of a database got treated differently. The behaviour is the same regardless of if it's the owner or any other role. In the end, I got mixed up and sent the wrong version of the test script to the list. However, even if it is not the right user, the problem remains. Samuel On Saturday, July 24, 2010 06:08:23 Alban Hertroys wrote: > On 23 Jul 2010, at 20:39, Samuel Gilbert wrote: > > Hello, > > > > I have encountered a problem with inserts failing because of permissions > > > > issues when the table in which I try to insert has foreign key > > constraints to tables for which UPDATE has been revoked. > > > > The script bellow show how to reproduce the problem with a bare-bones > > test case. Reproducibility is 100% on PostgreSQL 8.2.5 (I know it's not > > even the latest revision of the 8.2 line, but it's what I have to work > > with :( ) I Googled the error message and a couple of meaningful > > keywords, searched the documentation and the mailing list archives > > without success. > > It's not a solution to your problem, but one observation I made in your test script: > > CREATE DATABASE test WITH OWNER afsugil ENCODING 'LATIN1'; > > > > CREATE ROLE test WITH NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT > > LOGIN; > > \c test afsugil > > You create a new user, but you still connect with the user who created the > database. > > > REVOKE UPDATE ON station FROM afsugil; > > And then you revoke rights from that user instead of from the test user. > Effectively you're not using the 'test' user at all in your script. Was > that intended? > > Alban Hertroys > > -- > Screwing up is an excellent way to attach something to the ceiling. > > > !DSPAM:737,4c4abba6286216566810360!
Re: Blocked inserts on tables with FK to tables for which UPDATE has been revoked
From
Tom Lane
Date:
Samuel Gilbert <samuel.gilbert@ec.gc.ca> writes: > I have encountered a problem with inserts failing because of permissions > issues when the table in which I try to insert has foreign key constraints to > tables for which UPDATE has been revoked. Yeah, this is because the insertion has to take a row lock on the referenced row, which it does with a SELECT FOR SHARE operation. You could argue that that should require weaker privilege than UPDATE; but on the other hand mere SELECT privilege doesn't seem like enough, since a SELECT FOR SHARE can block update/delete operations. Short of inventing a new privilege type just for SELECT FOR SHARE, it's unclear what to do. Thinking about it, I'm tempted to propose that maybe SELECT FOR SHARE should be allowed if you have either UPDATE or REFERENCES privilege on the target table. The implications would need a lot more thought than I've given it though; and it certainly wouldn't be a change we'd consider back-patching. 8.2's behavior is what it is, so you'll have to live with it. > This is a pretty severe issue for me since, I don't want users that need to > input data to also have the right to modify references tables. The privileges that are relevant for the FK action are those of the owner of the referencing table. I'd suggest you consider that your data entry users probably ought not be the owners of *any* tables. They ought to be separate accounts that just have insert/update privs on the tables you want them to touch. regards, tom lane