postgresql - PostgreSQL 数据库转储：授予用户备份的所有权限有意义吗？

Question

我想对 postgresql 数据库进行数据库备份。我在我现有的数据库上做了这个：

sudo -u postgres psql oder psql -U postgres
CREATE USER backup;                                                                          
ALTER USER backup WITH PASSWORD 'new_password';
GRANT CONNECT ON DATABASE confluence TO backup; 
GRANT CONNECT ON DATABASE taiga TO backup;  
GRANT USAGE ON SCHEMA public TO backup; 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO backup;                                                               

如果我跑，pg_dump -Fc confluence > dumpfile我会得到

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation EVENTS
pg_dump: [archiver (db)] query was: LOCK TABLE public."EVENTS" IN ACCESS SHARE MODE

在Permission denied for relationship我读到这会有所帮助：

GRANT ALL PRIVILEGES ON TABLE confluence TO backup;

我想知道将所有权限授予没有写入数据库权限的备份用户是否是正确的方法。我希望它是只读用户。这是胡说八道的要求吗？

你建议我怎么做？

标签： postgresql

Answer 1

除了授予对数据库的 CONNECT 权限和对所有表的 SELECT 权限（在公共模式中）之外，您还需要对所有序列（在公共模式中）授予选择权限。

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO <user>

总结一下：

--Create user
CREATE USER backup;                                                                          
ALTER USER backup WITH PASSWORD 'new_password';

--Grant read only privileges
GRANT CONNECT ON DATABASE <database> TO backup; 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup;  -- new!

--Automatically grant read only privileges on new tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO backup;