sql - 授予对 PostgreSQL 中 future 表的特权？

Question

我正在运行 PostgreSQL 9.3.1。我有 test 数据库和用于备份数据库的 backup 用户。我对所有当前表授予权限没有问题，但每次将新表添加到架构时我都必须授予权限。

createdb test

psql test
test=# create table foo();
CREATE TABLE
test=# grant all on all tables in schema public to backup;
GRANT
test=# create table bar();
CREATE TABLE

psql -U backup test
test=> select * from foo;
test=> select * from bar;
ERROR:  permission denied for relation bar

是否可以在不让用户成为表所有者的情况下授予对将来创建的表的访问权限？

Answer 1

看起来解决方案是更改backup 用户的默认权限:

alter default privileges in schema public grant all on tables to backup;
alter default privileges in schema public grant all on sequences to backup;

来自 Matt Schaffer 的评论:

As caveat, the default only applies to the user that executed thealter statement. This confused me since I was driving most of mypermissions statements from the postgres user but creating tables froman app user. In short, you might need something like this depending onyour setup:

ALTER DEFAULT PRIVILEGES FOR USER webapp IN SCHEMA public GRANT SELECT ON SEQUENCES TO backup;
ALTER DEFAULT PRIVILEGES FOR USER webapp IN SCHEMA public GRANT SELECT ON TABLES TO backup;

其中 webapp 是将在未来创建新表的用户，backup 是能够从 创建的新表中读取的用户网络应用程序。