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
You need CONNECT
on the database in question, USAGE
on all schemas and SELECT
on all tables, views and sequences.
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
-
I was missing the
grant select on all sequences
. Thank you. Just out of interest... Why are sequences something that need specific grants? Feb 1, 2021 at 11:16 -
1Actually - here is a question on dba.stackexchange - dba.stackexchange.com/questions/284425/… Feb 1, 2021 at 11:19