Re: [GENERAL] requests / suggestions to help with backups
On Thu, Feb 15, 2007 at 22:39:13 -0500, Lou Duchez [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] requests / suggestions to help with backups
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 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 [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] requests / suggestions to help with backups
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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] requests / suggestions to help with backups
On 2/16/07, Lou Duchez [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] requests / suggestions to help with backups
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 [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] requests / suggestions to help with backups
Erik Jones [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
