I am sure many of you have encountered the problems I am experiencing
with automating the pg_dump command from a cron. The problem arises from
a failure of this command to use the environmental variable,
PG_PASSWORD. (PGUSER, on the other hand, is picked up during command
execution.) The cron seems to fail completely when crontabbed as the
PGSQL superuser, postgres. I do, however, get partial execution when I
su to the user postgres from a cron crontabbed as root. Let me layout
some relevant info:
root orginated cron:
30 * * * * su - postgres --command="cd /var/lib/pgsql/skybuilders;
/var/lib/pgsql/skybuilders/dbbackup.pl; echo `date` > timeRun.txt"
skybuilders is a directory where I copy and dump PostgreSQL files and
DBs to be picked up by an rsync cron on an archiving machine.
dbbackup.pl is a perl script that generates a bash shell. I will excerpt
the pertinant (failing) section of the generated script
(dbbackupGenerated.sh):
PGUSER=someuser
export PGUSER
PG_PASSWORD=somepassword
export PG_PASSWORD
pg_dump -o somedb > /var/lib/pgsql/skybuilders/dbbackup/somedb.dump
When I run the script from the command line, after having su'ed into the
postgres account, this script stops at the pg_dump line waiting for a
password.
Finally I will excerpt some of the final lines of my pg_hba.conf file:
# TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
AUTH_ARGUMENT
#local all trust
#host all 127.0.0.1 255.255.255.255 trust
# Using sockets credentials for improved security. Not available everywhere,
# but works on Linux, *BSD (and probably some others)
#local all ident sameuser
### --> Added by JSB <--
local skyTemplate md5
local sameuser md5
local all md5 admins
# use following line to perform commands as postgres without password
challenge
# local all trust
host all 10.10.40.25 255.255.255.255 md5
I understand were I to change the AUTH_TYPE setting from "md5" to
"trust" the password challenge emanating from the pg_dump command will
go away. However, I wish to maintain the use of md5 authentication.
Ideas anybody?
--
Jesse Burkhardt
jesse@skybuilders.com (w) 617-876-5680
goose@aerogoose.com (h) 617-354-5523