Re: requests / suggestions to help with backups

Lou Duchez wrote:
>> 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.
Sorry, you're right on that one. I misread it. However, it shouldn't
be too hard to write a script, either in a procedural language or higher
level, to pull the existing table names from pg_class and invokes the
GRANT command for you "trusted" user on each.

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)