Re: [GENERAL] Blocked inserts on tables with FK to tables for which UPDATE has been revoked
Samuel Gilbert samuel.gilb...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Blocked inserts on tables with FK to tables for which UPDATE has been revoked
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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Blocked inserts on tables with FK to tables for which UPDATE has been revoked
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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Blocked inserts on tables with FK to tables for which UPDATE has been revoked
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
