sql - 权限:为什么在这种情况下表所有者需要更新？

Question

我有两张 table 。一个有一个外键引用另一个表中的序列字段。我已将 INSERT 权限授予所有者以外的角色，但我仍然无法插入包含外键的表，除非我授予表所有者对包含引用字段的表的 UPDATE 权限。我不太明白为什么所有者需要拥有更新权限才能让另一个不同的角色(具有插入权限)能够在这种情况下插入一行。

这有点令人困惑，所以我提供了一个我的问题的简化示例。

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"

但是，如果我将 UPDATE 权限还给测试所有者(GRANT UPDATE ON a TO testowner;)，上面的插入将起作用。为什么测试所有者在这种情况下需要更新？

注意:GRANT UPDATE ON a TO testupdater; 没有帮助；看来我必须 GRANT UPDATE 到 testowner 角色。

Answer 1

我假设问题出在该选择语句中的“FOR SHARE OF”——为了能够创建该行锁，您至少需要对该表进行某种写访问。

例如如果我创建一个表并只授予自己对它的 SELECT 访问权限:

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

现在猜测...大概外键的实现是通过检查外部表中是否存在目标行来工作的，并根据源表或目标表的所有者为其设置授权上下文。 . TBH 我从来没有撤销过表所有者的特权，所以我以前没有遇到过这种情况。

我假设这是因为您不希望一个帐户仅仅因为它们创建就可以访问所有表？我建议:

• 以“postgres”或其他在 pg_hba.conf 中具有有限访问权限的 super 用户身份执行模式更改
• 或者，使用“postgres”或其他 super 用户的set session authorization，以某些没有登录权限的用户(例如数据库所有者)的身份执行模式更改