Обсуждение: 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 >