25

I have an up and running PostgreSQL database. Now I would like to automate some operations on my database but I have a problem with the password.

I am using the following bash script:

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

When I run the bash script I get the following error:

psql: fe_sendauth: no password supplied

I have even tried to configure the .pgpass file in my home directory but to no avail:

*:*:*:postgres:postgres

I have run the following command:

sudo chmod 0600 .pgpass

Nevertheless it does not seem that any of the methods works. Does anyone have any idea? Am I forgetting to do something?

alibaba
  • 427
  • 2
  • 6
  • 14

3 Answers3

18

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.

Craig Ringer
  • 11,083
  • 9
  • 40
  • 61
  • 2
    You could also setup sudo to allow you to use the `-E` option which will preserve your environment (including `PGPASSWORD`). This prevents it showing on the process list. – fukawi2 Jul 26 '13 at 01:16
1

This occurs if the password for the database is not given.

default="postgres://postgres:password@127.0.0.1:5432/DBname"
Aishwarya kabadi
  • 11
  • 2
0

I have even tried to configure the .pgpass file in my home directory but to no avail:

You need to create the .pgpass file in the home directory of the account running the psql command, i.e. the postgres account, not yours!

psql -d pg_ldap -w --no-password ...

I think these options might be confusing things as well.

Both mean the same things, specifically "don't use a password".

I'd suggest removing these. psql will acknowledge that a password is required but should detect a correctly-placed .pgpass file and use that instead of asking for a password.

And, of course, your postgres account password isn't really "postgres", is it? :-)

Phill W.
  • 1,479
  • 7
  • 7