-   Linux - Software (
-   -   psql and PGPASSWORD and a little php - AWS vps - Ubuntu (

dcdon 08-17-2011 02:43 PM

psql and PGPASSWORD and a little php - AWS vps - Ubuntu
I have a shell script that is being called from php.

the shell script needs to execute a sql command using psql . www-data is the user that calls the script, but I need to run the psql as a different user and password.

the script basically runs this:

echo 'Select * from farms;' >> /tmp/x.sql
psql -U joe -d mydb -f /tmp/x.sql > /tmp/log.txt

obviously, this will not work because the password for account joe cannot be found. So I have tried and tried to set the PGPASSWORD. I can even run export PGPASSWORD=thepassword and then printenv and see it there. But it fails when I try it saying:

psql: FATAL: Ident authentication failed for user "joe"

in troubleshooting, I sudo bash'd over to root and tried this at the command line:

export PGPASSWORD=thepassword
psql -U joe -d mydb -f /tmp/x.sql > /tmp/log.txt
--- same error, failed to authenticate.

I also loaded up all of the variables and checked them with printenv:
PGPORT, PGDATABASE, PGUSER, PGPASSWORD, PGHOST . It still says "ident authentication failed".

I can log into this account joe in various ways so I know the password is good.

Any advice will be quite appreciated.

dcdon 08-17-2011 05:50 PM

Just figured it out after 3 days.

it was the configuration in my pg_hba.conf . I had to change the 'local' authentication from 'ident' (value after standard install) to 'md5' and it started working. I hope someone finds this helpful one day.

Tinkster 08-17-2011 06:18 PM

Thanks for posting the update.

And a silly question: why do you need to create the shell-script
and run it, rather than doing this from within PHP?


dcdon 08-17-2011 06:28 PM

well the shell script is actually much more complicated, doing some stuff that would just get nasty trying to do it from php. I had just chopped it down to simplify things for testing that permissions issue.

All times are GMT -5. The time now is 07:26 PM.