1

I am working on new processes and policies for my PostgreSQL databases when they are first created, and I am trying to define what role attributes are necessary for a role specifically used for backups. I have found for MySQL that the grants SELECT, PROCESS, LOCK TABLES, SHOW VIEW, TRIGGER are enough for a user to perform backups, but I can't figure out how to translate grants like this to PostgreSQL.

Any recommendations, other than having to rely on Superuser?

Improve this question
1
  • 1
    For pg_dump to work (which isn't really a backup) the user only needs SELECT privilege on all tables (and sequences) that should be dumped. The predefined role pg_read_all_data might be what you are looking for
    – user1822
    Commented Feb 18, 2022 at 16:30

1 Answer 1

Reset to default
3

For a logical backup (dump), you need CONNECT on the database, USAGE on all schemas and SELECT on tables and sequences. The role must be defined as LOGIN.

For a file system backup with pg_dump, you need a role with the REPLICATION setting.

Improve this answer
1
  • Thanks! I set up all of the grants accordingly, and the backup user works. Just need to test the backup, but all appears good!
    – vPilot
    Commented Feb 21, 2022 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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