On Sat, Feb 20, 2010 at 6:57 PM, Nilesh Govindarajan <lists@xxxxxxxxxx> wrote: > On 02/20/2010 11:23 PM, Scott Marlowe wrote: >> >> On Sat, Feb 20, 2010 at 10:11 AM, Nilesh Govindarajan<lists@xxxxxxxxxx> >> wrote: >>> >>> I want a script to backup all databases table by table in different .sql >>> files. >> >> Why? It may be that pg_dump / pg_restore already provide the >> functionality you are looking for. >> >>> How to do this ? >> >> pg_dump -t tablename dbname >> >> rinse repeat >> >>> Also since it is a script, there has to be some role having read access >>> to >>> all tables on all databases. How to grant that ? >> >> Can they just be a superuser? Or can you grant them the role that has >> ownership of the tables? > > I don't feel safe to key in the root password in the script. Why would you need to have a password in your script? Just use .pgpass file for whichever unix account is going to connect, and then grant that user the SQL privileges to dump the db. I think you need to do more reading of the docs on user accounts etc under pgsql. > I was previously using MySQL, in which I had a user called backup without > password having SELECT access on all tables and databases. There are lots of ways to attack this, some of which do not require a password to be stored anywhere. At no time should a unix root account be involved. > I need something similar in PgSQL. A possible solution seems to me is to use > the trust auth for a user in pg_hba.conf, but if the user is given superuser > status, then it will have write access to all databases, which is extremely > dangerous. trust auth works but has the issues you mention. the .pgpass file is the easiest way to pass a password without including it in a script or anything. http://www.postgresql.org/docs/8.3/interactive/libpq-pgpass.html Note that the user account in unix doesn't have to map directly to the same name in postgresql. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
