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 relation中，我读到这句话可能会有所帮助：

GRANT ALL PRIVILEGES ON TABLE confluence TO backup;

我想知道是否将所有特权授予备份用户是正确的做法，因为该用户不应具有写入数据库的权限，我希望它只是一个只读用户。

这是一个无意义的要求吗？

您建议我应该做什么？

- Stefatronik

Answer 1

除了在数据库上授予CONNECT权限和在所有表（在public模式中）上授予SELECT权限之外，您还需要在所有序列（在public模式中）上授予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;   

- JonT