Re: pg_dump shell script with ~/.pgpass

On Thu, 27 Jan 2005, Richard Huxton wrote:

> MargaretGillon(at)chromalloy(dot)com wrote:

[snip]

>> I need to create a shell script for cron.daily to run that will do pg_dump
>> for my database. I am using Redhat 9 and Postgresql v7.3.4. Currently when
>> I run the dump manually the command I use is
>>
>> #pg_dump -u -C -D -f /tmp/owl.sql owl
>>
>> What should the command look like using ~/.pgpass ?
>
> Well, from cron, I'd add an explicit username "-U username" and then in your
> .pgpass add the host/port/db/user/password as described in the manuals
> (client interfaces/libc/files).

Personally I _hate_ having passwords in files, and avoid it whenever possible.
[1] It's another instance that I have to change when the password expires;
and [B] it's a possible avenue for a security failure (or, at least requires
real thought and attention to avoid turning into a security problem).

What I do instead is have the cron listing run a simple script which uses su :

su --command="INSERT YOUR PG COMMAND HERE" $PGNAME

where PGNAME has previously been set to, for example, 'postgres'.
This script can only be run by root (or the su will fail); and if run
by root, and the PGNAME is postgres, no additional password will be
needed for DB admin.

[how to accomplish this in WinXX I have no idea]

HTH,

-frank