权限：为什么在这种情况下表所有者需要UPDATE权限？

Question

我有两个表格。其中一个表格有一个外键，引用另一个表格中的序列字段。我已经将INSERT权限赋予了除所有者以外的角色，但是除非我授予包含引用字段的表格的所有者UPDATE权限，否则我仍然无法向包含外键的表格插入记录。 我不太理解为什么在这种情况下，所有者需要具有UPDATE权限，才能使另一个独立的角色（具有INSERT权限）能够插入一行。这可能有点令人困惑，因此我提供了一个简化版本的示例说明我的问题。

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: see below
\q

错误: 拒绝访问关系a

上下文: SQL语句 "SELECT 1 FROM ONLY "public"."a" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"

然而，如果我给testowner授予UPDATE权限(GRANT UPDATE ON a TO testowner;)，上述插入操作就可以正常工作。为什么在这种情况下testowner需要UPDATE权限？

注意：GRANT UPDATE ON a TO testupdater;无效；似乎我必须将UPDATE权限授予testowner角色。

- David Underhill

---

如果在testowner上给用户读取权限，它是否有效？在testupdater上进行插入操作需要读取testowner中外键的值，以确保插入操作是有效的。 - Macy Abbey

@Macy，我从未撤销过“testowner”的SELECT权限。同时，“testupdater”也被授予了SELECT权限，因此它应该能够读取表a以检查引用。 - David Underhill

这边运行良好，没有意外错误。 - Frank Heikens

@Frank Weird - 我正在使用v9.0；你在用什么？ - David Underhill

Answer 1

我假设问题出在那个SELECT语句中的"FOR SHARE OF"，为了创建行锁，你需要至少对表有某种写入权限。例如，如果我创建了一个表，只授予自己选择权限：

postgres@testdb=# create table t(t1_id serial primary key, value text);
NOTICE:  CREATE TABLE will create implicit sequence "t_t1_id_seq" for serial column "t.t1_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
CREATE TABLE
postgres@testdb=# insert into t(value) values('foo');
INSERT 0 1
postgres@testdb=# grant select on t to steve;
GRANT

现在，虽然我可以从表中读取行，但我无法锁定它们：

steve@testdb@[local] => select * from t;
 t1_id | value 
-------+-------
     1 | foo
(1 row)

steve@testdb@[local] => select * from t for share;
ERROR:  permission denied for relation t

现在猜测一下...外键的实现应该是通过检查外部表中的目标行是否存在，并基于源表或目标表的所有者设置授权上下文来工作...说实话，我从未撤销过表所有者的权限，所以以前没有遇到过这种情况。

我认为这是因为您不希望一个有权访问所有表的帐户仅因为它们创建了这些表而拥有此权限？我建议：

• 将模式更改为'postgres'或其他在pg_hba.conf中具有有限访问权限的超级用户
• 或者，将模式更改为某个用户（例如数据库所有者），该用户没有登录访问权限，使用set session authorization从'postgres'或其他超级用户

- araqnid