Thread: Postgres jobs will not automatically login on Mac OSX
Hi All,
I have been trying to setup a simple backup job to backup up the individual
schemas but I cannot get it postgres to utilize the .pgpass password file.
Mac:
System Version: OS X 10.9 (13A603)
Kernel Version: Darwin 13.0.0
Postgres:
PostgreSQL 9.3.1 on x86_64-apple-darwin, compiled by
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build
5658) (LLVM build 2336.9.00), 64-bit
I have copied the .pgpass to many different directories (eg. my home,
postgres home, etc) but that had no affect. After trying many different
permutations with environment variables etc, I have put the script and
.pgpass files in the /Users/postgres/script directory. The script contains
PGPASSFILE=/Users/postgres/script/.pgpass
Here is a sample of the .pgpass file (password deleted):
localhost:5432:postgres:postgres:XXXXXX
When I run the pg_backup.sh as root, postgres prompts me for a password on
every transaction and creates the backups as expected. When I run the
script as any other user, postgres fails to authenticate.
Here is the pg_backup.sh file that I harvested from an online post:
#!/bin/bash
# Configure the configuration path
PGPASSFILE=/Users/postgres/script/.pgpass
HOSTNAME=localhost
USERNAME=postgres
BACKUP_DIR=/home/backup/postgres/
if [ "$(uname)" == "Darwin" ]; then
BACKUP_DIR=/Users/backup/postgres/
fi
echo -e "PWD: "`pwd`
echo -e "Home: $HOME"
echo -e "BACKUP_DIR: $BACKUP_DIR"
# If the directory could not be created, display error and abort
BACKUP_DIR_DATED=$BACKUP_DIR"`date +\%Y-\%m-\%d`/"
echo -e "BACKUP_DIR_DATED: $BACKUP_DIR_DATED"
if ! mkdir -p $BACKUP_DIR_DATED; then
echo -e "Cannot create backup directory in $BACKUP_DIR_DATED. Go and fix
it!"
exit 1;
fi;
# Get List of Schemas
echo -e "----- Full backups Begin -----"
FULL_BACKUP_QUERY="select datname from pg_database where datname not like
'template%';"
FULL_BACKUP_QUERY_LIST=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c
"$FULL_BACKUP_QUERY" postgres`
# If the list of Schemas is empty, display error and abort
if [ "$FULL_BACKUP_QUERY_LIST" = "" ]; then
echo -e "No schemas returned from database. This could be a password
issue."
exit 1
fi
# Loop through each schema
for DATABASE in $FULL_BACKUP_QUERY_LIST
do
BACKUP_FILE="$BACKUP_DIR_DATED$DATABASE.sql.gz"
echo -e "$DATABASE \t---> $BACKUP_FILE"
if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip >
$BACKUP_FILE.in_progress; then
echo -e "ERROR: Failed to backup $DATABASE"
else
mv $BACKUP_FILE.in_progress $BACKUP_FILE
fi
done
echo -e "----- Full backups End -----"
Here are the results of the script:
plademan@Patricks-MacBook-Pro /Users/postgres/script
$ ./pg_backup.sh
PWD: /Users/postgres/script
Home: /Users/plademan
BACKUP_DIR: /Users/backup/postgres/
BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-21/
----- Full backups Begin -----
psql: FATAL: password authentication failed for user "postgres"
No schemas returned from database. This could be a password issue.
plademan@Patricks-MacBook-Pro /Users/postgres/script
$
How do I configure postgres to utilize the .pgpass file?
Thank you,
Pat
Patrick Lademan <mjfrog14@gmail.com> writes:
> I have been trying to setup a simple backup job to backup up the individual
> schemas but I cannot get it postgres to utilize the .pgpass password file.
The .pgpass file has to not be readable/writable by anyone but you
(permissions rw------- or less). Since you didn't mention checking
permissions, my money is on it being a permissions issue.
regards, tom lane
Patrick Lademan <mjfrog14@gmail.com> writes:
> I forgot to mention... for testing purposes, I chmod 777 .pgpass
> Since everyone can read the file, postgres should be able to read it too.
Well, that's pretty silly. You might as well use TRUST mode for local
connections. Anyway, postgres will not consult a .pgpass file that has
such loose permissions. This is not a bug, it's entirely intentional.
regards, tom lane
Thanks for the tip. That got me a lot farther.
I have changed the permissions and run it as user postgres.
This time, it prompted for a password on the 2nd time through the loop.
postgres ~
$ ./pg_backup.sh
PWD: /Library/PostgreSQL/9.3
Home: /Library/PostgreSQL/9.3
BACKUP_DIR: /Users/backup/postgres/
BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-21/
----- Full backups Begin -----
Backing up postgres ---> /Users/backup/postgres/2013-11-21/postgres.sql.gz
Backing up rincon ---> /Users/backup/postgres/2013-11-21/rincon.sql.gz
Password:
Here is the code:
#!/bin/bash
# Configure the configuration path
HOSTNAME=localhost
USERNAME=postgres
BACKUP_DIR=/home/backup/postgres/
if [ "$(uname)" == "Darwin" ]; then
BACKUP_DIR=/Users/backup/postgres/
fi
echo -e "PWD: "`pwd`
echo -e "Home: $HOME"
echo -e "BACKUP_DIR: $BACKUP_DIR"
# If the directory could not be created, display error and abort
BACKUP_DIR_DATED=$BACKUP_DIR"`date +\%Y-\%m-\%d`/"
echo -e "BACKUP_DIR_DATED: $BACKUP_DIR_DATED"
if ! mkdir -p $BACKUP_DIR_DATED; then
echo -e "Cannot create backup directory in $BACKUP_DIR_DATED. Go and fix
it!"
exit 1;
fi;
# Get List of Schemas
echo -e "----- Full backups Begin -----"
FULL_BACKUP_QUERY="select datname from pg_database where datname not like
'template%';"
FULL_BACKUP_QUERY_LIST=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c
"$FULL_BACKUP_QUERY" postgres`
# If the list of Schemas is empty, display error and abort
if [ "$FULL_BACKUP_QUERY_LIST" = "" ]; then
echo -e "No schemas returned from database. This could be a password
issue."
exit 1
fi
# Loop through each schema
*for DATABASE in $FULL_BACKUP_QUERY_LIST*
*do*
BACKUP_FILE="$BACKUP_DIR_DATED$DATABASE.sql.gz"
echo -e "Backing up $DATABASE \t---> $BACKUP_FILE"
*if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip >
$BACKUP_FILE.in_progress; then*
echo -e "ERROR: Failed to backup $DATABASE"
else
mv $BACKUP_FILE.in_progress $BACKUP_FILE
fi
done
echo -e "----- Full backups End -----"
This time, it prompted for a password on the 2nd time through the loop.
How do I stop it from prompting for a password on the 2nd time through the
loop?
On Thu, Nov 21, 2013 at 2:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Patrick Lademan <mjfrog14@gmail.com> writes:
> > I forgot to mention... for testing purposes, I chmod 777 .pgpass
> > Since everyone can read the file, postgres should be able to read it too.
>
> Well, that's pretty silly. You might as well use TRUST mode for local
> connections. Anyway, postgres will not consult a .pgpass file that has
> such loose permissions. This is not a bug, it's entirely intentional.
>
> regards, tom lane
>
On 21-11-2013 18:06, Patrick Lademan wrote: > I have changed the permissions and run it as user postgres. > This time, it prompted for a password on the 2nd time through the loop. > That's because the database is 'postgres' in your password file. Try: localhost:5432:*:postgres:XXXXXX -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
That worked!!! Thanks!!! I have one last problem. It works correctly at the command line: postgres ~ $ ./pg_backup.sh User: postgres PWD: /Library/PostgreSQL/9.3 HOME: /Library/PostgreSQL/9.3 BACKUP_DIR: /Users/backup/postgres/ BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-21/ ----- Full backups Begin ----- Backing up postgres ---> /Users/backup/postgres/2013-11-21/postgres.sql.gz Backing up rincon ---> /Users/backup/postgres/2013-11-21/rincon.sql.gz ----- Full backups End ----- When I run it as a job in postgres, it fails. Here are the results: 662 Failed 1 Thu Nov 21 19:02:38 2013 Thu Nov 21 19:02:38 2013 00:00:00.016344 User: postgres PWD: /Library/PostgreSQL/9.3 HOME: /Library/PostgreSQL/9.3 BACKUP_DIR: /Users/backup/postgres/ BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-21/ ----- Full backups Begin ----- No schemas returned from database. This could be a password issue. It is behaving like it could not login to get the schema list. Since prompting for a password is not an error message, it is not displayed in the error log. How can I debug this job? Thanks, Pat On Thu, Nov 21, 2013 at 5:36 PM, Euler Taveira <euler@timbira.com.br> wrote: > On 21-11-2013 18:06, Patrick Lademan wrote: > > I have changed the permissions and run it as user postgres. > > This time, it prompted for a password on the 2nd time through the loop. > > > That's because the database is 'postgres' in your password file. Try: > > localhost:5432:*:postgres:XXXXXX > > > -- > Euler Taveira Timbira - http://www.timbira.com.br/ > PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento >
On Thu, Nov 21, 2013 at 07:11:46PM -0500, Patrick Lademan wrote:
- That worked!!! Thanks!!!
-
- I have one last problem.
-
- It works correctly at the command line:
- postgres ~
- $ ./pg_backup.sh
-
- User: postgres
- PWD: /Library/PostgreSQL/9.3
- HOME: /Library/PostgreSQL/9.3
- BACKUP_DIR: /Users/backup/postgres/
- BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-21/
- ----- Full backups Begin -----
- Backing up postgres ---> /Users/backup/postgres/2013-11-21/postgres.sql.gz
- Backing up rincon ---> /Users/backup/postgres/2013-11-21/rincon.sql.gz
- ----- Full backups End -----
-
- When I run it as a job in postgres, it fails. Here are the results:
- 662 Failed 1 Thu Nov 21 19:02:38 2013 Thu Nov 21 19:02:38 2013
- 00:00:00.016344
- User: postgres
- PWD: /Library/PostgreSQL/9.3
- HOME: /Library/PostgreSQL/9.3
- BACKUP_DIR: /Users/backup/postgres/
- BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-21/
- ----- Full backups Begin -----
- No schemas returned from database. This could be a password issue.
-
- It is behaving like it could not login to get the schema list.
- Since prompting for a password is not an error message, it is not displayed
- in the error log.
- How can I debug this job?
As a sanity check you might want to add the following to the if statement.
echo "Hostname: ${HOSTNAME}"
echo "Username: ${USERNAME}"
Those may be getting lost when the job is run via cron.
For example:
# If the list of Schemas is empty, display error and abort
if [ "$FULL_BACKUP_QUERY_LIST" = "" ]; then
echo "Hostname: ${HOSTNAME}" # <-------Add
echo "Username: ${USERNAME}" # <-------Add
echo -e "No schemas returned from database. This could be a password
issue."
exit 1
fi
Alternativly, add -x to the top line to enable debugging
#!/bin/bash -x
and then in your cron job output stdout and stderr to a file
0 0 0 0 0 0 backupjob.sh >/tmp/backup.out 2>&1
Thanks for your help. I added printing the Hostname and Username to the
beginning of the script. Everything is the same.
I tried the -x option but since pgagent only logs errors, no additional
information was returned.
Here is the run from the command line:
whoami: postgres
PWD: /Library/PostgreSQL/9.3
HOME: /Library/PostgreSQL/9.3
BACKUP_DIR: /Users/backup/postgres/
HOSTNAME: localhost
USERNAME: postgres
BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-22/
----- Full backups Begin -----
Backing up postgres ---> /Users/backup/postgres/2013-11-22/postgres.sql.gz
Backing up rincon ---> /Users/backup/postgres/2013-11-22/rincon.sql.gz
----- Full backups End -----
Here is the run from pgagent initiated by run now in pgadmin:
665 Failed 1 Fri Nov 22 12:55:44 2013 Fri Nov 22 12:55:44 2013
00:00:00.017515
whoami: postgres
PWD: /Library/PostgreSQL/9.3
HOME: /Library/PostgreSQL/9.3
BACKUP_DIR: /Users/backup/postgres/
HOSTNAME: localhost
USERNAME: postgres
BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-22/
----- Full backups Begin -----
No schemas returned from database. This could be a password issue.
What am I missing to get this backup job to run correctly from pgagent?
Thanks,
Pat
On Fri, Nov 22, 2013 at 12:35 PM, David Kerr <dmk@mr-paradox.net> wrote:
> On Thu, Nov 21, 2013 at 07:11:46PM -0500, Patrick Lademan wrote:
> - That worked!!! Thanks!!!
> -
> - I have one last problem.
> -
> - It works correctly at the command line:
> - postgres ~
> - $ ./pg_backup.sh
> -
> - User: postgres
> - PWD: /Library/PostgreSQL/9.3
> - HOME: /Library/PostgreSQL/9.3
> - BACKUP_DIR: /Users/backup/postgres/
> - BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-21/
> - ----- Full backups Begin -----
> - Backing up postgres --->
> /Users/backup/postgres/2013-11-21/postgres.sql.gz
> - Backing up rincon ---> /Users/backup/postgres/2013-11-21/rincon.sql.gz
> - ----- Full backups End -----
> -
> - When I run it as a job in postgres, it fails. Here are the results:
> - 662 Failed 1 Thu Nov 21 19:02:38 2013 Thu Nov 21 19:02:38 2013
> - 00:00:00.016344
> - User: postgres
> - PWD: /Library/PostgreSQL/9.3
> - HOME: /Library/PostgreSQL/9.3
> - BACKUP_DIR: /Users/backup/postgres/
> - BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-21/
> - ----- Full backups Begin -----
> - No schemas returned from database. This could be a password issue.
> -
> - It is behaving like it could not login to get the schema list.
> - Since prompting for a password is not an error message, it is not
> displayed
> - in the error log.
> - How can I debug this job?
>
>
> As a sanity check you might want to add the following to the if statement.
> echo "Hostname: ${HOSTNAME}"
> echo "Username: ${USERNAME}"
>
> Those may be getting lost when the job is run via cron.
>
> For example:
> # If the list of Schemas is empty, display error and abort
> if [ "$FULL_BACKUP_QUERY_LIST" = "" ]; then
> echo "Hostname: ${HOSTNAME}" # <-------Add
> echo "Username: ${USERNAME}" # <-------Add
> echo -e "No schemas returned from database. This could be a password
> issue."
> exit 1
> fi
>
> Alternativly, add -x to the top line to enable debugging
> #!/bin/bash -x
>
> and then in your cron job output stdout and stderr to a file
> 0 0 0 0 0 0 backupjob.sh >/tmp/backup.out 2>&1
>