Proper roles for a backup-only user on PostgreSQL



  • 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?



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2