I'm running a PostgreSQL instance which is used by several related applications, each of which has its own database. For backups I want to create snapshots of the complete data in the instance and I plan on using
pg_dumpall to do that. The backups are done from another host and should use a separate PostgreSQL user for security reasons.
I am therefore trying to create a PostgreSQL user
- can read all data in the instance, both in existing databases/tables and in databases/tables that may be added in the future by other users
- cannot modify anything
So far I have managed the following:
-- Revoke default privileges REVOKE ALL ON SCHEMA public FROM PUBLIC; -- Allow user to connect and use database GRANT CONNECT ON DATABASE db TO backup; GRANT USAGE ON SCHEMA public TO backup; -- Grant read privileges for existing tables GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup; -- Grant read privileges when new tables are created ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO backup;
This works, but only for a single, fixed database. Is there a way to do this for all existing databases and databases that might be created in the future?
I'm currently on PostgreSQL 9.4 and I'm not using any schemas aside from