Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it true that it is IMPOSSIBLE to have readonly backup user in Postgresql ? I tried to hack around this as I've been advised on IRC channel that you simply can't have a backup only user with no ownership privileges, but I just find it very strange so I want to make sure I'm not missing something.

Below is what I tried but it doesn't not get me the results - as 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!

share|improve this question
add comment

1 Answer

I have done some proper research and there DOES seem to be a solution for this. I've come across this blog post which perfectly explains what needs to be done. I hope this helps to people who are looking for the same answer as I was. Obviously - restoring the backups done this way is a different question.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.