postgres backup script not as postgres user

Question

I want to create a postgres backup script but I don't want to use the postgres user because the unix system I am in has few restrictions. What I want to do is run this script as a regular user of the unix system (network) on a crontab and use database admin account in the script.

Just to make it clear in the unix system (network) I have user foo, now we have postgres user who is default postgres user, and then for the database we have a database admin user my_db_admin. I script to execute as network user foo using my_db_admin user and credentials in the script.

This is what I started with but the problem is when I execute the script it gives an error saying password was not provided.

#!/bin/bash
export PASSWORD="MyPassword"
backup_dir="/BACKUP/LOCATION"
# name of the backup file has the date
backup_date=`date +%d-%m-%Y`
# only keep the backup for 30 days (maintain low storage)
number_of_days=30
pg_dump -Fc -Umy_db_admin MyDatabase -w > $backup_dir\_$backup_date
find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;

Here is the error:

pg_dump: [archiver (db)] connection to database "MyDatabase" failed: fe_sendauth: no password supplied

Cocke

Where do you supply that PASSWORD to pg_dump? – Samons

@MilenA.Radev I guess I am not sure how I can put that in pg_dump – Cocke

Well, reading the fine docs is a good start. – Samons

possible duplicate of How to execute PostgreSQL script-file from command line without userinput / password – Tumbling

Answer 1

Use a .pgpass file to save a password for pg_dump, or set the PGPASSWORD environment variable. Or modify pg_hba.conf to allow connection without a password using peer authentication.

This is covered in the manual:

• .pgpass.
• Environment variables
• Client authentication

and a search for the error message would've found relevant information.

Bise