I'm trying to automate the creation of a user for database backup on Postgresql. What grants are required to run
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