FOR SHARE permissions

From: David Underhill <dound07(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: FOR SHARE permissions
Date: 2010-12-12 03:47:50
Message-ID: AANLkTinbdL8RYpv=o+ThMHo23KL-vhDuAbEWx-3oncXH@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I have two tables. One has a foreign key referencing a serial field in the
other table. I've given INSERT privilege to a role other than the owner,
but I still can't insert into the table containing the foreign key unless I
grant the *owner* of the table UPDATE privilege on the table containing the
referenced field.
I don't quite understand why the *owner* needs to have UPDATE permission in
order for another distinct role (with INSERT privilege) to be able to insert
a row in this case.

This is a little confusing, so I've provided a boiled down example of my
issue.

createuser -U postgres testowner -DIRS --pwprompt
createdb -U postgres -O testowner testdb
createuser -U postgres testupdater -DIRS --pwprompt

psql -d testdb -U testowner
CREATE TABLE a ( id serial PRIMARY KEY );
CREATE TABLE b ( a_id integer REFERENCES a(id) );
GRANT SELECT,INSERT ON ALL TABLES IN SCHEMA public TO testupdater;
GRANT USAGE,UPDATE ON SEQUENCE a_id_seq TO testupdater;
REVOKE INSERT, UPDATE ON ALL TABLES IN SCHEMA public FROM testowner;
INSERT INTO a VALUES (DEFAULT); -- as expected: ERROR: permission denied
for relation a
\q

psql -d testdb -U testupdater
INSERT INTO a VALUES (DEFAULT);
SELECT id FROM a LIMIT 1; -- selects the first id (1)
INSERT INTO b VALUES (1); -- unexpected error:
\q

ERROR: permission denied for relation a
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."a" x WHERE "id"
OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"

However, the above insert works if I give testowner back the UPDATE
privilege (GRANT UPDATE ON a TO testowner;). Why does testowner need UPDATE
in this case?

Thanks,

~ David

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2010-12-12 16:11:19 Re: FOR SHARE permissions
Previous Message Samuel Stearns 2010-12-11 22:16:01 Re: Postgres Crash