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 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!

share|improve this question

2 Answers 2

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
    
Instead of simply linking to the blog post, you should add some details to your answer in case the blog post suffers from link-rot (see en.wikipedia.org/wiki/Link_rot ) –  Max Vernon Jul 8 at 13:26

Note the blog referenced in the answer given by @Gyre will not work for creating an "application" read-only user (i.e, for creating a read only role for a web application to connect to the database), and might open up a serious security vulnerability, since its easily circumventable, as explained in this postgresql list response. For reference, by the client overriding the session settings:

SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE

Refer 'Managing rights in postgresql' presentation linked in the postgres wiki for a more detailed method, similar to the one posted in the question.

share|improve this answer

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.