PostgreSQL read-only user for pg_dumpall

Question

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.

Answer 1

This has become much easier with the release of Postgres 14, which introduced the pg_read_all_data role.

You can create a new user with permissions to read everything by running:

CREATE ROLE backup WITH ROLE pg_read_all_data LOGIN PASSWORD '<password>';

Note that even with pg_read_all_data, the user appears to be prohibited from reading the pg_authid catalog and for me, pg_dumpall still resulted in an error. An additional grant on pg_authid did not work either.

However, as long as you can live with Postgres user passwords being omitted from the dump, you may pass the --no-role-passwords option to pg_dump and it will work fine.

Answer 2

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).