PostgreSQL – Troubleshooting pg_hba.conf Configuration

configurationpostgresqlpostgresql-9.1

this my configuration:

#postgres.conf:

listen_addresses = 'localhost'

#pg_hba.conf:

local all postgres md5
local all appuser trust
local all devuser trust
local all all peer

i'm connecting in local using the C-Library libpq.
My connection string is:

host=localhost user=appuser dbname=mydbname port='5432'

However, unless I have a .pgpass file in the home of my developer user (devuser), (the one who launches the application that is linked with libpq), my executable cannot connect, I receive the error fe_sendauth : no password supplied

As I said, if the .pgpass file is here, then it connects ok.

What I don't understand: I configured local all appuser trust.
Why do I need a .pgpass file all the same?

(It's not a serious problem in itself, because later on for security measure, it will no longer be at trust, but md5, so I'll have the .pgpass file anyway. But I'd like to know)

Best Answer

I can see how one might think listen_addresses = 'localhost' is somehow related to the authentication method local. But it is not. I quote the manual on listen_addresses:

listen_addresses (string)

Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications.

And the chapter The pg_hba.conf File in the manual:

local

This record matches connection attempts using Unix-domain sockets. Without a record of this type, Unix-domain socket connections are disallowed.

Bold emphasis mine.

For connections via localhost (TCP/IP, local loopback) you actually need an entry for the authentication method host in your pg_hba.conf file (not for local).

Related Solutions

Postgresql – Correct pg_hba.conf to allow script to run locally with a specific db-user

Since you want the backup user to connect locally from the database server. I assume the backup is running on a dedicated backup account on the OS.

Using pg_hba.conf you can specify like:

# TYPE  DATABASE USER   ADDRESS METHOD
local   all      backup_user    peer map=local_peers_backup_user

(method peer (with local/peer an address is not needed))

combine this with a pg_ident.conf like:

# MAPNAME                SYSTEM-USERNAME   PG-USERNAME
local_peers_backup_user  postgres          backup_user
local_peers_backup_user  OSBKPUSER         backup_user

This allows the os user postgres and OSBKUSER to connect to all databases in the cluster as the postgres database user named backup_user, using peer authentication. This works using sockets, on linux.

connected as your OSBKPUSER running psql suffices to get connected as backup_user, assuming that you already prepared the environment variables for the OSBKPUSER and OSBKPUSER has only 1 map. Otherwise run psql -U backup_user.

This connect method is secure, if you trust that the access to your dbserver is secured.

Postgresql – Connect to PgAdmin III via localhost

The basic misunderstanding: "localhost" is not a local connection. Your "trust" line in pg_hba.conf is not applicable for your connection:

local   all   all   trust

That only applies for connections via Unix-domain socket. Best choice for you is to connect via this route. Per pgAdmin documentation:

The host is the IP address of the machine to contact, or the fully qualified domain name. On Unix based systems, the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine, or be set to an alternate path containing a PostgreSQL socket. If a path is entered, it must begin with a “/”. The port number may also be specified.

Bold emphasis mine.

More about connecting without password in this related answer on SO:
Run batch file with psql command without password

Best Answer

Related Solutions

Postgresql – Correct pg_hba.conf to allow script to run locally with a specific db-user

Postgresql – Connect to PgAdmin III via localhost

Related Question