Thread: requests / suggestions to help with backups
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.
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. -- erik jones <erik@myemma.com> software development emma(r)
Erik Jones <erik@myemma.com> writes: > Lou Duchez wrote: >> 2) "pg_dumpall -E". If I could specify a single encoding for all my >> database dumps, I could use pg_dumpall. > I guess you missed this: > http://www.postgresql.org/docs/8.2/interactive/sql-grant.html Also, on the second point, you can do export PGCLIENTENCODING=whatever before running pg_dumpall. A -E switch might be more obvious but it's not like you can't do it now. regards, tom lane
> 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.
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@myemma.com> software development emma(r)
> >Certainly, I've > >tried "grant select on database mydatabase to user myuser"; it doesn't > >work, because "select" is not a database-level privilege. > 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. That could be done, but my big worry is all the non-table components of a database such as views and functions -- I'd hate to accidentally be creating incomplete dumps simply because I forgot to programmatically assign permissions on my operator classes (or whatever). So I'd still like to see a "read" or "readonly" permission at the database level, but until then, it seems the best bet is to use an overprivileged trusted account for my backups. The security risks can be managed, and they are worth it to make sure I've got a complete and cohesive dump.
On 2/16/07, Lou Duchez <lou@paprikash.com> 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; A .pgpass file can fix this... I don't know if that gets you any closer to your objective. - Ian
On Thu, Feb 15, 2007 at 22:39:13 -0500, Lou Duchez <lou@paprikash.com> wrote: > > 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.) You can use ident authentication instead of trust. That may make using the postgres db account for the cronjob's connection an acceptible risk.