CODEWITHSUNDEEP
phplinuxpostgresql-9.2
Darin Peterson
Darin Peterson

Reputation: 1282

Does setting a password on the postgres user mean that a password is required even when setting pg_hba.conf to trust?

I have done most of my PHP/PostgreSQL development under Windows 7, but am migrating to Linux. I have recently installed PHP and PostgreSQL on Debian Wheezy, and was having difficulty connecting to my PostgreSQL database with the postgres user. I found a post on stackoverflow that said to do the following:

template1=# ALTER USER postgres WITH PASSWORD 'password';

Initially, for the password, I used an empty string, which didn't work. Next I used a text string, which allowed me to connect via pg_connect().

After doing more research, I found that altering the pg_hba.conf file and making the postgres user trusted was really what I wanted to do... On making this change, I am still getting errors when a password isn't supplied via pg_connect() for the postgres user, so my question is:

Does altering the postgres user with a password cause pg_connect() to require a password even when the authentication method is set to trust? When I connect via the command line using:

psql -U postgres

i have no problems... The problems begin when connecting via PHP using pg_connect().

pg_hba.conf file:

local   all             postgres                                trust
local   all             all                                     trust
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

PHP connection line:

pg_connect('host=localhost dbname=dev user=postgres');

Upvotes: 1

Views: 1329

Answers (1)

Lorenzo Marcon
Lorenzo Marcon

Reputation: 8169

Change your pg_hba.conf to:

local   all             postgres                                trust
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

This will allow you to connect without password.

Remember to restart (or reload) postgresql after you edit pg_hba.conf.

In alternative, you can try to change your php code like this:

pg_connect('dbname=dev user=postgres');

This should work even without any change to pg_hba.conf.

Explanation:

You can connect to postgres via unix socket (suggested, faster) or via TCP/IP. The first and second line in pg_hba.conf are relative to socket, the third to ipv4 and the fourth to ipv6. If you specify localhost in your code, you're connecting via TCP/IP (ipv4), and that's why it didn't work and it asked you the password.

If you connect by console just with psql -U postgres, you're using unix socket, and that's why it worked without password.

Don't worry, even with changed configuration only local connection will be granted access with no password.

Upvotes: 5

Related Questions