Home / server / Questions / 249172
Accepted
Daniel C. Sobral
Asked: 2011-03-19 13:27:54 +0800 CST

What grants are required to run pg_dump?

  • 772

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

backup postgresql

2 Answers

  1. Best Answer

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

    • 7

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

    • 7