How can I create readonly user for backups in PostgreSQL?

Question

Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL?

I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges. I find it very strange so I want to make sure I'm not missing something.

Below is what I tried but it doesn't give me the results I'm looking for. When I do pg_dump on a given table I'm getting Permission denied for relation...:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup; 
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO backup;

Any help would be greatly appreciated!

Answer 1

No, it's easy (now anyway).

1. Grant the connect permission on a new user

   GRANT CONNECT ON DATABASE mydb TO myReadolyUser;
   

2. Grant the permissions on all the current database objects. This is schema-specific, and you'll have to run one copy for every schema you wish for your user to use,

   GRANT SELECT ON ALL TABLES IN SCHEMA mySchema TO myReadonlyUser;
   

   From the docs, ALL TABLES includes everything you'd want.

   There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, and functions (but note that ALL TABLES is considered to include views and foreign tables.

3. Then ALTER DEFAULT PRIVLEGES to grant future SELECT privileges for objects not yet created.

   ALTER DEFAULT PRIVILEGES IN SCHEMA mySchema
   GRANT SELECT ON TABLES TO myReadonlyUser;
   

Answer 2

If you want (or can live with) a backup user having read-only access to all databases, you may use the pg_read_all_data role since the release of Postgres 14.

You can create such new user with permissions to read everything by using WITH IN ROLE:

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

Answer 3

The simple and nice way is to create a superuser with read only permission.

• Login psql as postgres or other superuser.

• Create the new superuser role and set it to read only :

  CREATE USER backadm SUPERUSER  password '<PASS>';
  ALTER USER backadm set default_transaction_read_only = on;
  

  • Replace <PASS> by your choosen password.
  • You can replace backadm by the choosen username. (I put backadm for Backup Administrator).
  • Do NOT forgot the single quotes for the password.

You can now use this role to backup.