job.answiz.com
  • 4
Votes
name

I've recently experimented with the settings in pg_hba.conf. I read the PostgreSQL documentation and I though that the "password" auth method is what I want. There are many people that have access to the server PostgreSQL is working on so I don't want the "trust" method. So I changed it. But then PHP stopped working with the database.

The message I get is "Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: password authentication failed for user "myuser" in /my/path/to/connection/class.php on line 35". It is kind of strange because I can connect via phppgadmin without any problems and also I can connect from my home computer with psql - again without any problems.

This is my pg_hba.conf:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     password
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
# IPv6 local connections:
host    all             all             ::1/128                 password

The connection string I'm using with pg_conenct is:

$connect_string = "host=localhost port=5432 dbname=mydbname user=auser password=apassword";
$dbConnection = pg_connect($connection_string);

Does anybody know why is this happening ? Did I misconfigured something ?

It appears that when you have password that contains slashes ( /) you need to escape them in the pg_connect connection url. After escaping that I had no problems connecting to the database via php. Hope this helps somebody having this problem :)

  • 4
Reply Report

Client authentication is controlled by a configuration file, which traditionally is named pg_hba.conf and is stored in the database cluster's data directory. (HBA stands for host-based authentication.) A default pg_hba.conf file is installed when the data directory is initialized byinitdb. It is possible to place the authentication configuration file elsewhere, however; see the hba_file configuration parameter.

The general format of the pg_hba.conf file is a set of records, one per line. Blank lines are ignored, as is any text after the # comment character. Records cannot be continued across lines. A record is made up of a number of fields which are separated by spaces and/or tabs. Fields can contain white space if the field value is double-quoted. Quoting one of the keywords in a database, user, or address field (e.g., all or replication) makes the word lose its special character, and just match a database, user, or host with that name.

Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

  • 0
Reply Report

If you want to specify the ip adresses in postgresql.conf, you will have to confiure ALL the ips your clients will be comming from (including in this case your PC ip or VPN if you are using).

That's is why it works when you use '*', because it will listen for clients comming from all ips (including yours).

pg_hba.conf will work as a firewall, so you will need to configure your entrance there too.

In your case , I think is better to use listen_addresses='*' ,and configure the ip adrresses in pg_hba, as the parameter listen_addresses requires a restart when you want to change it.

pg_hba.conf: host all all xxx.xxx.xx.xx/0 md5
postgresql.conf: listen_addresses = '*'

Just tell me if you need further help to configure your pg_hba.

Good luck!

Note: further information on the listen_adresses parameterhttp://www.postgresql.org/docs/9.4/static/runtime-config-connection.html

  • 0
Reply Report