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.