Re: Running pg_dump under vcron

On Mon, Jun 23, 2003 at 07:09:43PM -0600, Keary Suska wrote:
> Check your root email. STDERR should be emailed to the cron user‹this will
> tell you more specifically what is wrong. Also set PGUSER and PGPASSWORD
> either in your crontab or script. Make sure pg_dump is in your path.

The 7.3 documentation says:

"PGPASSWORD sets the password used if the backend demands
password authentication. This functionality is deprecated
for security reasons; consider migrating to use the
$HOME/.pgpass file."

But the release 7.3 notes say:

"Libpq
Add $HOME/.pgpass to store host/user password combinations (Alvaro Herrera)"

So I'm guessing $HOME/.pgpass does nothing for releases prior to
7.3. I think that means that before 7.2 cron activated stuff is
stuck using the less secure PGPASSWORD method.

I do something like this:

------------------------------------------------------------------------------------------------------------
#!/usr/bin/perl -w

use strict;
my $dfltSqlScriptDir=qq(/root/.secretscripts); # make sure /root/.secretscripts permission is 700
my $dataOutputDirectory=qq(/home/less_secret_output); # make sure /home/less_secret_output permission is 755
my $script2run = qq($dfltSqlScriptDir/sql_script.sh);
my $shell_script = <<END_OF_SCRIPT;
#!/bin/bash
set -x
export PGPASSWORD=so_secret_that_even_the_owner_cant_know_it
export PGUSER=manifest
/usr/bin/psql -f ${dfltSqlScriptDir}/sql_script.sql >> ${dataOutputDirectory}/output.log 2>&1
END_OF_SCRIPT

open SHELL_FILE, qq(>$script2run) || die qq(cant open output data file $script2run\n);
print SHELL_FILE $shell_script;
close SHELL_FILE || die qq(cant close output data file $script2run\n);
chmod 0700, $script2run;

system($script2run);
------------------------------------------------------------------------------------------------------------

This perl file is what cron executes.

Can anyone suggest a better method? This works, but I'm using a pile driver to set a ten penny nail.

Cheers,

--JPK