sql - 角色权限不会分配给从公共(public)模式移动的表

Question

我想创建新模式并将公共(public)模式中的表传输到这些模式，但是每当我将表从公共(public)模式移动到另一个模式时，对新模式具有使用权限的用户/角色，如以及它的表(包括 future 的表)，无法访问新移动的表。

表(在public 架构中):

CREATE TABLE atable(ID INT);
INSERT INTO atable VALUES(1);
INSERT INTO atable VALUES(2);

新用户:

create user x_user with login password 'x_user';

新架构:

create schema dw;  

然后我授予它对新模式及其表的所有访问权限:

GRANT USAGE ON SCHEMA dw TO x_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA dw to x_user;
GRANT SELECT ON ALL TABLES IN SCHEMA dw TO x_user;  

对于将来添加到架构中的表

ALTER DEFAULT PRIVILEGES IN SCHEMA dw GRANT SELECT ON TABLES TO x_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA dw GRANT USAGE ON SEQUENCES TO x_user;  

现在我将 atable 的架构更改为 dw:

ALTER TABLE atable SET SCHEMA dw;  

此外，我在 dw 架构中创建了另一个表:

CREATE TABLE dw.btable(id int);
INSERT INTO dw.btable VALUES(3);
INSERT INTO dw.btable VALUES(4);  

现在，当我连接到数据库时，使用新的用户凭据并运行:

SELECT * FROM dw.atable;

我得到:错误:关系表 1 语句失败的权限被拒绝。

而如果我对在 dw 模式中创建的 btable 运行相同的查询，它就可以工作。

SELECT * FROM dw.btable;
id
---
3
4

当我将表从一个模式移动到另一个模式时，它也可以工作，但是当我将表从 public 模式移动到另一个模式时，它就不起作用了。

我在这里做错了什么？

Answer 1

GRANT ... ON ALL TABLES IN SCHEMA 仅影响架构的当前内容。

ALTER DEFAULT PRIVILEGES IN SCHEMA 影响在架构中创建的表。

当将表从一个模式移动到另一个模式时，这些都没有任何影响，而且我不知道有什么影响。

应该可以通过创建一个 event trigger 来做到这一点它在任何 ALTER TABLE 命令上触发并应用适当的 GRANT。不幸的是，虽然您可以在 PL/pgSQL 中编写这些触发器函数，但我认为它(目前)没有提供任何方法来找出实际命令是什么；你需要:

• 编写一个C 函数来检查pg_event_trigger_ddl_commands() 返回的pg_ddl_command 结构| , 或
• 在每个 ALTER TABLE 之后盲目地运行一个GRANT，不管它是否是一个SET SCHEMA命令。

如果适合您的用例，一个更简单的选择是编写一个 move_table() 函数，它结合了 ALTER 和 GRANT命令。