Johann Oskarsson

Using identd to authenticate local connections to PostgreSQL

Saturday, September 10, 2011 1:48:47 AM

The steps needed to setup and use identd to authenticate PostgreSQL are not very well documented with few examples floating on the web.

Here we go over the steps needed. The examples as shown are done on a virgin CentOS installation. The presence of a PostgreSQL server is presumed.

Inetd

First we need the ident daemon. This may seem obvious but your system may not have it already.

yum install authd

This installs authd, a very simple identd.

We then edit /etc/xinetd.d/auth to both enable it and limit to local connections. In addition a firewall that prevents access to port 113 is also good. The file in its entirety (without comments) is show below.

service auth
{
        disable         = no
        socket_type     = stream
        wait            = no
        user            = ident
        cps             = 4096 10
        instances       = UNLIMITED
        server          = /usr/sbin/in.authd
        server_args     = -t60 --xerror --os -E
        only_from       = localhost
}

And now we reload.

service xinetd reload

PostgreSQL

On PostgreSQL we edit the file pg_hba.conf:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
local   all             all                                     ident map=pg
host    all             all             127.0.0.1/32            ident map=pg
host    all             all             ::1/128                 ident map=pg

Here we set the method to ident and name the map pg we are going to use in pg_ident.conf:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
pg              johann                  postgres
pg              postgres                postgres

And now we reload the Postgres configuration, either with

service postgresql-9.0 reload

pg_ctl reload

This means we allow users johann and postgres to connect to the database as user postgres.

Say what?

Yes, in order to use this, user johann must use the -U switch to psql to connect as the superuser role postgres. As in

johann@asuka:~% psql -U postgres

The postgres system user does not need this switch however; under rare cases is any human logged in on that account though.

Using ident for authentication does not automatically create login roles for PostgreSQL.

Building a relocatable PostgreSQL serverPostgreSQL JDBC with GCJ