PostgreSQL从另一个表复制权限 [英] PostgreSQL copy permissions from another table

问题描述

是否可以将用户权限从PostgreSQL数据库的一个表复制到另一个表？仅仅是将目标表的pg_class.relacl列值更新为源表的值，就像这样：

Is it possible to copy the user permissions from one table in a PostgreSQL database to another table? Is it just a matter of updating the pg_class.relacl column value for the target table to the value for the source table, as in:

UPDATE pg_class
SET relacl=(SELECT relacl FROM pg_class WHERE relname='source_table')
WHERE relname='target_table';

这似乎可行，但我是否想念其他可能需要做的事情或其他'陷阱'

This seems to work, but am I missing anything else that may need to be done or other 'gotchas' with this method?

在此先感谢您的答复。

推荐答案

如果可以使用命令行而不是SQL，那么更安全的方法是使用pg_dump：

If you can use command-line instead of SQL then a safer approach would be to use pg_dump:

pg_dump dbname -t oldtablename -s \
| egrep '^(GRANT|REVOKE)' \
| sed 's/oldtablename/newtablename/' \
| psql dbname

我假设使用unix服务器。在Windows上，我将对文件使用 pg_dump -s ，手动对其进行编辑，然后将其导入数据库。

I assume a unix server. On Windows I'd use pg_dump -s to a file, manually edit it and then import it to a database.

也许您还需要将权限复制到该表拥有的序列中-pg_dump将起作用。

Maybe you'll also need to copy permissions to sequences owned by this table - pg_dump will work.

这篇关于PostgreSQL从另一个表复制权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！