How can I create readonly user for backups in PostgreSQL?
28
votes
3
answers
43298
views
Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL?
I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges. I find it very strange so I want to make sure I'm not missing something.
Below is what I tried but it doesn't give me the results I'm looking for. When I do
pg_dump
on a given table I'm getting Permission denied for relation...
:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup;
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO backup;
Any help would be greatly appreciated!
Asked by milosgajdos
(429 rep)
Nov 2, 2013, 01:05 AM
Last activity: Dec 19, 2024, 01:38 PM
Last activity: Dec 19, 2024, 01:38 PM