Postgres jobs will not automatically login on Mac OSX

From: Patrick Lademan <mjfrog14(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Postgres jobs will not automatically login on Mac OSX
Date: 2013-11-21 16:49:38
Message-ID: CADMm_6Ay7M4=QHcvsYG-iYbSwPsCP-DmaXjZBivp4Unhe=mVPQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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(at)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(at)Patricks-MacBook-Pro /Users/postgres/script
$

How do I configure postgres to utilize the .pgpass file?

Thank you,

Pat

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2013-11-21 17:33:43 Re: Postgres jobs will not automatically login on Mac OSX
Previous Message Oliver Munyao 2013-11-21 15:29:08 pgdump not dumping my database