5

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

5

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

0

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

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.