19

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?

  • 1
    Try sudo su -c psql postgres psql ... instead. – Flup Jul 25 '13 at 10:18
  • The .pgpass is in your home directory? Not in the postgres user's one? – Milen A. Radev Jul 25 '13 at 14:37
  • Yes it is in my home directory, i.e. /home/server2/ – alibaba Jul 25 '13 at 15:57
15

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.

  • 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 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.