你可以在postgresql中将表权限从一个表复制到另一个表吗？

I've got a import process that copies a table schema with the code below and then populates the table with data. However it doesn't copy over the roles granted.

我有一个导入过程,使用下面的代码复制表模式,然后用数据填充表。但是,它不会复制授予的角色。

CREATE TABLE TOTABLE (LIKE FROMTABLE INCLUDING INDEXES)

Is there a way I can copy privileges when the schema is copied, or I can apply the privileges afterwards from the "FROMTABLE"?

有没有办法在复制模式时复制权限,或者我可以从“FROMTABLE”后面应用权限?

2 个解决方案

#1

Information about tables in postgresql are stored in the pg_class table. The field containing table privileges is relacl.

有关postgresql中表的信息存储在pg_class表中。包含表特权的字段是relacl。

So something like the following would work:

所以类似下面的东西会起作用:

update pg_class set relacl = (select relacl from pg_class where relname = 'from_table') where relname='to_table';

update pg_class set relacl =(从pg_class中选择relcl,其中relname ='from_table')其中relname ='to_table';

Note that pg_class has metadata for all tables -- so you should also take care to make sure you are using the right schema (relnamespace) in case there are tables of the same name in multiple schemas.

请注意,pg_class具有所有表的元数据 - 因此,如果多个模式中存在同名表,则还应注意确保使用正确的模式(relnamespace)。

#2

Be very careful when manipulating catalog tables directly. It's generally advisable to use DDL statements exclusively. Catalog tables are not meant to be written by users. If you mess this up, your DB cluster might be corrupted beyond repair. You have been warned.

直接操作目录表时要非常小心。通常建议仅使用DDL语句。目录表不是由用户编写的。如果你搞砸了,你的数据库集群可能会被破坏而无法修复。你被警告了。

Update: Turns out, the above warning is quite right. This was a bad idea to begin with. Standard GRANT / REVOKE commands (as well as the default privilege system) also make entries in pg_shdepend table to remember dependencies between objects and roles mentioned in the access control list (except for the owner, which is linked anyway). The manual:

更新:事实证明,上述警告是完全正确的。这是一个糟糕的主意。标准GRANT / REVOKE命令(以及默认权限系统)也在pg_shdepend表中创建条目,以记住访问控制列表中提到的对象和角色之间的依赖关系(除了所有者之外,它仍然是链接的)。手册:

The catalog pg_shdepend records the dependency relationships between database objects and shared objects, such as roles. This information allows PostgreSQL to ensure that those objects are unreferenced before attempting to delete them.

目录pg_shdepend记录数据库对象和共享对象(如角色)之间的依赖关系。此信息允许PostgreSQL在尝试删除它们之前确保这些对象未被引用。

By manipulating the access control list (relacl for relations) directly, dependencies fall out of sync, which can lead to "strange" problems when trying to drop roles later.

通过直接操作访问控制列表(关系的relacl),依赖关系不同步,这可能在以后尝试删除角色时导致“奇怪”问题。

There was a recent discussion about "Copying Permissions" on pgsql-hackers recently (Nov 2016), but nothing has been implemented, yet.

最近有一篇关于pgsql-hackers的“复制权限”的讨论(2016年11月),但还没有实现。

---

Outdated, do not use:

过时,不要使用:

The query presented by @Robert has a bug (as he noted): relname is not unique. There can be any number of tables with the same name in multiple schemas of the same db. To fix:

@Robert提出的查询有一个错误(正如他所说):relname不是唯一的。在同一db的多个模式中可以有任意数量的具有相同名称的表。修理:

UPDATE pg_class c_to
SET    relacl = c_from.relacl 
FROM   pg_class c_from
WHERE  c_from.oid  = 'public.from_table'::regclass
AND    c_to.oid    = 'public.to_table'::regclass

Differences

• The cast to regclass picks a table deterministically, even without schema-qualification. Details:

  即使没有模式限定,regclass的强制转换也会确定性地选择一个表。细节:

  • How do I speed up counting rows in a PostgreSQL table?

  如何加快PostgreSQL表中的行计数?

• If one of the tables is not found, you get an exception immediately (the cast to regclass fails).
  @Robert's query would happily set relacl to NULL if from_table is not found.

  如果找不到其中一个表,则会立即收到异常(转换为regclass失败)。如果找不到from_table,@ Robert的查询会愉快地将relacl设置为NULL。