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.

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.

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?


Cheers,
Tink

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.