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
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 |