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?
Best Answer
sudo
does not retain most environment variables. If you want to specify environment variables to a command run undersudo
, do so thoughsudo
: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 setpg_hba.conf
up forpeer
authentication oflocal
connections from userpostgres
.You can use a
.pgpass
file, but it must be the.pgpass
of the user you'resudo
'ing to, not the user you'resudo
ing from; it'd need to be~postgres/.pgpass
in this case. Think about it:psql
running aspostgres
doesn't know you ran it viasudo
from your account, it doesn't know what your user account is, and even if it did it doesn't have read permission as userpostgres
to~youruser/.pgpass
.Additionally,
-w
is the same as--no-password
. There's no point specifying both.