Thread: Perl and pg_dump
This may not be the right place to post this question, but I had to start somewhere.
I have a small perl script that is supposed to run pg_dump daily to backup databases.
The script is called via the cron on a Redhat 6.1 system with PostgreSQL 6.5.
The error that I receive when cron attempts to run the script is:
Connection to database 'template1' failed.
FATAL 1: SetUserId: user 'root' is not in 'pg_shadow'
I know root isn't in pg_shadow, and I don't want it there (I _do_ know better than to do things as root ;)).
How can I tell either Linux, PostgreSQL or the script to use another user (Postgres?) to run the script?
A copy of the script is below.
TIA-
Jim
--------------------------------------------
#!/bin/sh
PSQL=/usr/bin/psql
DUMP=/usr/bin/pg_dump
PREFIX=`date +%j`
BACKUP_DIR=/data/pgsql/data
Databases=`$PSQL -tq -d template1 -c "select datname from pg_database"`
renice 20 $$
echo Backup started ...
for db in `echo $Databases`
do
echo "time: `date +%H%M%S` - Backup of $db in progress ..."
$DUMP -D $db > $BACKUP_DIR/$PREFIX.$db
echo "time: `date +%H%M%S` - Backup of $db finished ..."
done
echo Backup finished
----------------------------------------------
easy, just "su -" into that account, and then plug in your crontab entry it will then run with the effective permissions of whatever user is setup On Mon, 10 Jul 2000, James Hall wrote: > This may not be the right place to post this question, but I had to start > somewhere. > > I have a small perl script that is supposed to run pg_dump daily to backup > databases. > The script is called via the cron on a Redhat 6.1 system with PostgreSQL > 6.5. > > The error that I receive when cron attempts to run the script is: > > Connection to database 'template1' failed. > FATAL 1: SetUserId: user 'root' is not in 'pg_shadow' > > I know root isn't in pg_shadow, and I don't want it there (I _do_ know > better than to do things as root ;)). > > How can I tell either Linux, PostgreSQL or the script to use another user > (Postgres?) to run the script? > > A copy of the script is below. > > TIA- > Jim > -------------------------------------------- > #!/bin/sh > > PSQL=/usr/bin/psql > DUMP=/usr/bin/pg_dump > PREFIX=`date +%j` > BACKUP_DIR=/data/pgsql/data > > Databases=`$PSQL -tq -d template1 -c "select datname from pg_database"` > renice 20 $$ > > echo Backup started ... > for db in `echo $Databases` > do > > echo "time: `date +%H%M%S` - Backup of $db in progress ..." > $DUMP -D $db > $BACKUP_DIR/$PREFIX.$db > echo "time: `date +%H%M%S` - Backup of $db finished ..." > done > echo Backup finished > ---------------------------------------------- > ----------------------------------------------------------------------------- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" -----------------------------------------------------------------------------
On Mon, Jul 10, 2000 at 04:54:43PM -0500, James Hall wrote: > I have a small perl script that is supposed to run pg_dump daily to backup > databases. > The script is called via the cron on a Redhat 6.1 system with PostgreSQL > 6.5. > > The error that I receive when cron attempts to run the script is: > > Connection to database 'template1' failed. > FATAL 1: SetUserId: user 'root' is not in 'pg_shadow' > > I know root isn't in pg_shadow, and I don't want it there (I _do_ know > better than to do things as root ;)). Hmmm... that needn't stop you from putting "root" into the postgres list, but that won't help if you're using password authentication. I ran into this when I installed postgres here (debian system - has a stock cron job that, fortunately, only needed to have some options added). > PSQL=/usr/bin/psql > DUMP=/usr/bin/pg_dump > Databases=`$PSQL -tq -d template1 -c "select datname from pg_database"` > $DUMP -D $db > $BACKUP_DIR/$PREFIX.$db The problem is that (as far as I can see) there's not any conveninent way to supply a valid user name and password to these utilities - they only have an option that will cause them to prompt for them, which is useless for scripting. I'm afraid I can't see any simpler solution than using the -u option and arranging to have the name and password stuffed into the commands' stdin. Because it has worked well for me in the past the first thing I thought of for this was 'expect', but surely it is overkill for this...
How about 'su -c scriptname postgres' called from cron where scriptname is the name of the script you're wanting to run and postgres is the postgres super user. Basically this would be like su'ing to user postgres and then running the script. Pat -----Original Message----- From: James Hall [mailto:James.Hall@RadioShack.com] Sent: Monday, July 10, 2000 3:55 PM To: pgsql-novice@postgresql.org Subject: [NOVICE] Perl and pg_dump This may not be the right place to post this question, but I had to start somewhere. I have a small perl script that is supposed to run pg_dump daily to backup databases. The script is called via the cron on a Redhat 6.1 system with PostgreSQL 6.5. The error that I receive when cron attempts to run the script is: Connection to database 'template1' failed. FATAL 1: SetUserId: user 'root' is not in 'pg_shadow' I know root isn't in pg_shadow, and I don't want it there (I _do_ know better than to do things as root ;)). How can I tell either Linux, PostgreSQL or the script to use another user (Postgres?) to run the script? A copy of the script is below. TIA- Jim -------------------------------------------- #!/bin/sh PSQL=/usr/bin/psql DUMP=/usr/bin/pg_dump PREFIX=`date +%j` BACKUP_DIR=/data/pgsql/data Databases=`$PSQL -tq -d template1 -c "select datname from pg_database"` renice 20 $$ echo Backup started ... for db in `echo $Databases` do echo "time: `date +%H%M%S` - Backup of $db in progress ..." $DUMP -D $db > $BACKUP_DIR/$PREFIX.$db echo "time: `date +%H%M%S` - Backup of $db finished ..." done echo Backup finished ----------------------------------------------
true, but with the su, you only do it once to set up the crontab it more "oo" this way On Mon, 10 Jul 2000, WOLF, PATRICK wrote: > How about 'su -c scriptname postgres' called from cron where scriptname is > the name of the script you're wanting to run and postgres is the postgres > super user. Basically this would be like su'ing to user postgres and then > running the script. > > Pat > > -----Original Message----- > From: James Hall [mailto:James.Hall@RadioShack.com] > Sent: Monday, July 10, 2000 3:55 PM > To: pgsql-novice@postgresql.org > Subject: [NOVICE] Perl and pg_dump > > > > This may not be the right place to post this question, but I had to start > somewhere. > > I have a small perl script that is supposed to run pg_dump daily to backup > databases. > The script is called via the cron on a Redhat 6.1 system with PostgreSQL > 6.5. > > The error that I receive when cron attempts to run the script is: > > Connection to database 'template1' failed. > FATAL 1: SetUserId: user 'root' is not in 'pg_shadow' > > I know root isn't in pg_shadow, and I don't want it there (I _do_ know > better than to do things as root ;)). > > How can I tell either Linux, PostgreSQL or the script to use another user > (Postgres?) to run the script? > > A copy of the script is below. > > TIA- > Jim > -------------------------------------------- > #!/bin/sh > > PSQL=/usr/bin/psql > DUMP=/usr/bin/pg_dump > PREFIX=`date +%j` > BACKUP_DIR=/data/pgsql/data > > Databases=`$PSQL -tq -d template1 -c "select datname from pg_database"` > renice 20 $$ > > echo Backup started ... > for db in `echo $Databases` > do > > echo "time: `date +%H%M%S` - Backup of $db in progress ..." > $DUMP -D $db > $BACKUP_DIR/$PREFIX.$db > echo "time: `date +%H%M%S` - Backup of $db finished ..." > done > echo Backup finished > ---------------------------------------------- > ----------------------------------------------------------------------------- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" -----------------------------------------------------------------------------