> Lou Duchez wrote: > >Like everyone else, I use pg_dump for backup purposes; I have a cron job > >that runs a pg_dump whose output is then FTP'd elsewhere. Two things > >that would make my life easier: > > > >1) "grant select on database ..." or, hypothetically, "grant select on > >cluster". The goal would be to create a read-only PostgreSQL user, one > >who can read the contents of an entire database (or even the entire > >cluster) but make no changes. Currently, to do my cron job, I have to > >specify a "trusted" user, otherwise PostgreSQL will ask for a password; > >it sure would be nice if I could neuter my "trusted" user so he cannot > >do any damage. (Yes, I could set read-only privileges on a table-by-table > >basis. Obviously, that's a pain.) > > > >2) "pg_dumpall -E". If I could specify a single encoding for all my > >database dumps, I could use pg_dumpall. But I cannot. (My databases > >themselves are encoded as UTF-8, but the data in them is all LATIN1, and > >I'd like to dump it all as LATIN1.) There are quite possibly good > >reasons for not offering the "-E" option on pg_dumpall; in the wrong > >hands it could be nightmarish. But sensibly employed, it could be very > >useful. > > > >And, combining my two requests, a "grant select on cluster ..." would > >allow me to do something like: > > > >pg_dumpall -U neutereduser -E LATIN1 -f onehugefile.bak > > > >I could really go for that. Especially when there's a major upgrade to > >PostgreSQL. > I guess you missed this: > http://www.postgresql.org/docs/8.2/interactive/sql-grant.html > You want the third one down. So are you recommending I use "grant create", "grant connect", "grant temporary", "grant temp", or "grant all"? Those seem to be the only permissions that can be applied on a database level. Certainly, I've tried "grant select on database mydatabase to user myuser"; it doesn't work, because "select" is not a database-level privilege. So unless you know a database-level permission that means "read-only", I think I'm still stuck.
