Ruby -> PostgreSQL connection with pg_hba.conf set to "ident sameuser" instead of "trust"

Question

I've tried every Google search term I can think of but everything I dig up keeps saying to set local connections to trust in pg_hba.conf (seems like a security hole if anyone locally can log in and access the DB as anyone they say they are).

In pg_hba.conf local connections are set to ident sameuser. The script should be running as that user, but I get this error:

A database error occurred: 
fe_sendauth: no password supplied

The Ruby code is pretty generic:

conn_str = "DBI:pg:dbname=mydb;host=" + localhost
@connection = DBI.connect(conn_str, "myuser", '')

I can work around this by creating a ~/.pgpass file as described here, but I'd prefer being able to let users log in and just access the DB server.

Anyone ever been able to get PostgreSQL's ident sameuser to work properly for local scripts?

Answer 1

I suspect this:

In pg_hba.conf local connections are set to ident sameuser. The script should be running as that user, but I get this error [...]

conn_str = "DBI:pg:dbname=mydb;host=" + localhost
@connection = DBI.connect(conn_str, "myuser", '')

Please note that a "local" connection is not the same as a connection to "localhost". As soon as you mention "localhost" in the connection URL a TCP/IP socket is created. These are managed by the host rules in `pg_hba.conf.

To use a real "local" connection Unix Domain Sockets must be used. But I don't know whether or not the Ruby DBI connector supports them.

Answer 2

The ident setting works for me for local system users and PostgreSQL 8.4. You may have to adjust your pg_hba.conf settings.

Check your database log files to see where the connections come from exactly and whether the system user name matches the database role name. You may have to activate log_connections in your postgresql.conf for that.

The manual really does a nice job explaining authentication methods.

If system user name and database role name don't match, you'll have to use a .pgpass file. But your passwords should still be safe. Only the system user postgres gets to read it. I quote the manual:

On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass.