Postgresql pgdump script on RHEL5: “failed: fe_sendauth: no password supplieddatabase “

databasedatabase-backuppostgresqlpsql

What I'm trying to accomplish is having a pg_dump script run as a cron job. Right now I'm just trying to get the script to run by invoking it myself at the command line. I've searched the postgresql mailing lists and followed the postgresql documentation with no success on a solution. I've looked on StackOverflow as well, but after reading a similar post on StackOverflow it seems like this sort of problem should be on this site.

System Specs:

RHEL5

Postgresql 9.0.3

This is what I do:

script is located here ~/database_backup/pg_dump_script

I run this from the directory

./pg_dump_script.sh

The script looks like this:

/home/myusername/lappstack-1.2-5/postgresql/bin/pg_dump --host=localhost 
--port 5433 --username "myusername" --no-password  --format plain --inserts 
--column-inserts --verbose
--file "/home/username/database_backup/$(%m.%d.%y_%1.%M%P).sql"
--table "schema.table_name" --table "schema.table_name1" "db_name"

This script is of course modified, and not the actual one, but gives you a sense of what I'm doing. I've tried other suggested host names: 127.0.0.1, 10.32.123.22 (not the real IP address of course).The port is correct. 5433 is what is in the pg_hba.conf

Error I receive:

./pg_dump_script.sh
" failed: fe_sendauth: no password supplieddatabase "
pg_dump.bin: *** aborted because of error
./pg_dump_script.sh: line 2: fg: no job control
./pg_dump_script.sh: line 2: --file: command not found
./pg_dump_script.sh: line 3: --table: command not found
./pg_dump_script.sh: line 4: --table: command not found
./pg_dump_script.sh: line 5: --table: command not found
./pg_dump_script.sh: line 6: --table: command not found
./pg_dump_script.sh: line 7: --table: command not found
./pg_dump_script.sh: line 8: --table: command not found
./pg_dump_script.sh: line 9: --table: command not found
./pg_dump_script.sh: line 10: --table: command not found
./pg_dump_script.sh: line 11: --table: command not found
./pg_dump_script.sh: line 12: --table: command not found
./pg_dump_script.sh: line 13: --table: command not found
./pg_dump_script.sh: line 14: --table: command not found
./pg_dump_script.sh: line 15: --table: command not found
./pg_dump_script.sh: line 16: --table: command not found
./pg_dump_script.sh: line 17: --table: command not found
./pg_dump_script.sh: line 18: --table: command not found
./pg_dump_script.sh: line 19: --table: command not found
./pg_dump_script.sh: line 20: --table: command not found
./pg_dump_script.sh: line 21: --table: command not found
./pg_dump_script.sh: line 22: --table: command not found
./pg_dump_script.sh: line 23: --table: command not found
./pg_dump_script.sh: line 24: --table: command not found
./pg_dump_script.sh: line 25: --table: command not found
./pg_dump_script.sh: line 26: --table: command not found
./pg_dump_script.sh: line 27: --table: command not found
./pg_dump_script.sh: line 28: --table: command not found
./pg_dump_script.sh: line 29: --table: command not found
./pg_dump_script.sh: line 30: --table: command not found
./pg_dump_script.sh: line 31: --table: command not found
./pg_dump_script.sh: line 32: --table: command not found
./pg_dump_script.sh: line 33: --table: command not found
./pg_dump_script.sh: line 34: --table: command not found
./pg_dump_script.sh: line 35: --table: command not found
./pg_dump_script.sh: line 36: --table: command not found
./pg_dump_script.sh: line 37: --table: command not found
./pg_dump_script.sh: line 38: --table: command not found
./pg_dump_script.sh: line 39: --table: command not found
./pg_dump_script.sh: line 40: --table: command not found
./pg_dump_script.sh: line 41: --table: command not found
./pg_dump_script.sh: line 42: --table: command not found
./pg_dump_script.sh: line 43: --table: command not found
./pg_dump_script.sh: line 44: --table: command not found

Other files that were needed to be configured

I have a .pgpass in my ~ directory. Following this convention:

http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html

I also made sure I did chmod 0600 ~/.pgpass

I am able to run pg_dump using PgAdmin3 on my windows machine. So I don't think it's a connection issue. I am also able to run psql without supplying a password, so this should mean the pgpass is working correctly?

Let me know if anymore information is needed!

Updates

I've been using everyone's suggestions and I've gotten to the point where I'm debugging one parameter at a time.

This is what I have:

/home/username/lappstack-1.2-5/postgresql/bin/pg_dump --host=127.0.0.1 --port=5433 --username=myusername mydb_name

This will give the error:

$ ./pg_dump_script.sh
Password:
" does not exist  database "my_dbnamenection to database "mydb_name

^ It doesn't output the error correctly. That's not a spelling mistake. I just replaced the actual db name with "mydb_name".

I have it prompting for the password, which it does and I then enter my password. I appreciate the comments. They are definitely helping with the troubleshooting process and will save me time in the future!

Best Answer

According to your error messages, you should try using backslashes \:


/home/myusername/lappstack-1.2-5/postgresql/bin/pg_dump --host=localhost \
--port 5433 --username "myusername" --no-password  --format plain --inserts \
--column-inserts --verbose \
--file "/home/username/database_backup/$(%m.%d.%y_%1.%M%P).sql" \
--table "schema.table_name" --table "schema.table_name1" "db_name"

Related Solutions

Windows – Postgres pgpass windows – not working

Please use this script for generating backup.

PGPASSWORD='database password'
PGUSER='database user'
PGHOST='localhost'
PGPORT='psql portno'
PGDATABASE='database name'
pg_dump -Ft <database name> -x -O -f '<backup file name.tar>'

Postgresql – psql: fe_sendauth: no password supplied

sudo does not retain most environment variables. If you want to specify environment variables to a command run under sudo, do so though sudo:

sudo PGPASSWORD="postgres" -u postgres psql -d pg_ldap -w --no-password -h localhost -p 5432 -t -c "SELECT id FROM radusers WHERE id=1"

Whether or not sudo permits this will depend on the security policy in force on your site.

I don't recommend this approach as it exposes the password in the command-line history and in the process list. It's much better to use a .pgpass file, or preferably set pg_hba.conf up for peer authentication of local connections from user postgres.

You can use a .pgpass file, but it must be the .pgpass of the user you're sudo'ing to, not the user you're sudoing from; it'd need to be ~postgres/.pgpass in this case. Think about it: psql running as postgres doesn't know you ran it via sudo from your account, it doesn't know what your user account is, and even if it did it doesn't have read permission as user postgres to ~youruser/.pgpass.

Additionally, -w is the same as --no-password. There's no point specifying both.

Best Answer

Related Solutions

Windows – Postgres pgpass windows – not working

Postgresql – psql: fe_sendauth: no password supplied

Related Topic