3

I want to make a database backup of a postgresql database. I did this on my existing database: 

sudo -u postgres psql oder psql -U postgres
CREATE USER backup;                                                                          
ALTER USER backup WITH PASSWORD 'new_password';
GRANT CONNECT ON DATABASE confluence TO backup; 
GRANT CONNECT ON DATABASE taiga TO backup;  
GRANT USAGE ON SCHEMA public TO backup; 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO backup;

If I run pg_dump -Fc confluence > dumpfile I get 

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation EVENTS
pg_dump: [archiver (db)] query was: LOCK TABLE public."EVENTS" IN ACCESS SHARE MODE

In Permission denied for relation I read that this would help:

GRANT ALL PRIVILEGES ON TABLE confluence TO backup;

I wonder if it is the rigth way to give all privileges to a backup user who shall not have the permissions to write the database. I want it to be a read only user. Is this a nonsense requirement?

What do you suggest me to do instead?

Stefatronik
  • 312
  • 2
  • 16

1 Answers1

1

In addition to grant CONNECT permission on the database and SELECT permission on all tables (in the public schema), you also need to grant select on all sequences (in the public schema):

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO <user>

So to summarize:

--Create user
CREATE USER backup;                                                                          
ALTER USER backup WITH PASSWORD 'new_password';

--Grant read only privileges
GRANT CONNECT ON DATABASE <database> TO backup; 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup;  -- new!

--Automatically grant read only privileges on new tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO backup;
JonT
  • 55
  • 9