26

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!

1

6 Answers 6

21

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;
    
2
  • I noticed that when running ALTER DEFAULT PRIVILEGES ... myReadonlyUser, 2 additional lines are added to the dump: ALTER DEFAULT PRIVILEGES FOR ROLE root IN SCHEMA public REVOKE ALL ON TABLES FROM PUBLIC; ALTER DEFAULT PRIVILEGES FOR ROLE root IN SCHEMA public REVOKE ALL ON TABLES FROM root;. Sounds like this will mean root won't be able to do anything on new tables. Is this true?
    – dthor
    Mar 11, 2019 at 16:26
  • 1
    Also, if your tables use bigint your readonly user will likely also need GRANT SELECT ON ALL SEQUENCES
    – dthor
    Mar 11, 2019 at 16:27
9

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.

4
  • 13
    Ewww. A superuser to backup? He specifically asked for read only. That user is one SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE or ALTER USER backadm set default_transaction_read_only = off; away from having unrestricted access to the database. Jan 12, 2017 at 5:10
  • This user will still able to drop tables/schemas/databases regardless of that the transactions are read only. May 6, 2019 at 8:54
  • Why would you grant such elevated rights for a backup user?
    – sharadov
    Apr 3, 2020 at 13:27
  • 1
    To be fair, a lot of backup software uses the postgres user by default... there isn't really a good solution.
    – Mark Lopez
    May 26, 2021 at 21:50
7

After testing back up with Evan Caroll's solution I came across this error:

ERROR: permission denied for relation 'table'

there's one other permission missing:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA mySchema TO myReadonlyUser

Adding this permission enabled me to do back up with my read-only user.

6

Note the blog referenced in the answer given by @Gyre will not work for creating an "application" read-only user (i.e, for creating a read only role for a web application to connect to the database), and might open up a serious security vulnerability, since its easily circumventable, as explained in this postgresql list response. For reference, by the client overriding the session settings:

SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE

Refer 'Managing rights in postgresql' presentation linked in the postgres wiki for a more detailed method, similar to the one posted in the question.

1
  • This is not really an answer, it's a critique on the other answers for being insecure which I've copied as a comment. I've answered this question properly now. Jan 12, 2017 at 5:28
3

I have done some proper research and there DOES seem to be a solution for this. I've come across this blog post which perfectly explains what needs to be done. I hope this helps to people who are looking for the same answer as I was. Obviously - restoring the backups done this way is a different question.

3
  • 1
    Instead of simply linking to the blog post, you should add some details to your answer in case the blog post suffers from link-rot (see en.wikipedia.org/wiki/Link_rot )
    – Hannah Vernon
    Jul 8, 2014 at 13:26
  • The basic idea from the blogpost is ALTER USER set default_transaction_read_only = on;, which does not prevent the user from changing it.
    – blueyed
    Mar 29, 2015 at 5:24
  • 2
    Ewww. A superuser to backup? He specifically asked for read only. That user is one SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE or ALTER USER backadm set default_transaction_read_only = off; away from having unrestricted access to the database. Jan 12, 2017 at 5:10
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 running:

CREATE ROLE backup WITH ROLE pg_read_all_data LOGIN PASSWORD '<password>';
1
  • in order to use pg_basebackup I needed to also add REPLICATION role
    – pieca
    Dec 13, 2021 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.