11

I'm trying to automate the creation of a user for database backup on Postgresql. What grants are required to run pg_dump?

2 Answers 2

8

You need CONNECT on the database in question, USAGE on all schemas and SELECT on all tables, views and sequences.

11

Not sure what Milen is on about since there is no grant statement for views and granting SELECT on tables is going to be enough.

Here are some SQL statements to get it done:

CREATE USER db_backup WITH PASSWORD 'p@55wurd_h3r3' LOGIN;
GRANT CONNECT ON DATABASE "some_db_name_here" to db_backup;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO db_backup;

I have tested this with v 9.6

2
  • I was missing the grant select on all sequences. Thank you. Just out of interest... Why are sequences something that need specific grants?
    – Zach Smith
    Feb 1, 2021 at 11:16
  • 1
    Actually - here is a question on dba.stackexchange - dba.stackexchange.com/questions/284425/…
    – Zach Smith
    Feb 1, 2021 at 11:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .