Обсуждение: 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

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!

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!

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