PostgreSQL read-only user for pg_dumpall

backuppostgresqlpostgresql-9.4

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 backup who

  • 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 public.

Best Answer

Backups from a normal user are a nightmare. If you're using PostgreSQL on Linux, I'd recommend relying on sudo (and the /etc/sudoers file) to accomplish this, so that a given specific user can sudo -u postgres pg_dumpall <params> with specific parameters, so that's all they can do as user postgres (and therefore have no write access to the database).

Related Question