FOR SHARE permissions
| От | David Underhill |
|---|---|
| Тема | FOR SHARE permissions |
| Дата | |
| Msg-id | AANLkTinbdL8RYpv=o+ThMHo23KL-vhDuAbEWx-3oncXH@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: FOR SHARE permissions
Re: FOR SHARE permissions |
| Список | 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
В списке pgsql-admin по дате отправления: