Infrastructure at your Service

Daniel Westermann

Using operating system users to connect to PostgreSQL

PostgreSQL supports many authentication methods by default and one of them is Ident authentication. Using that method you can use the users defined in the operating system and map them to users in PostgreSQL. So how does that work?

To start, lets create a new operating system user we want to use for connecting to the database:

[email protected]:/u02/pgdata/DEV/ [PGDEV] sudo groupadd user1
[email protected]:/u02/pgdata/DEV/ [PGDEV] sudo useradd -g user1 -m user1

The next step is to create a so called user name map. A user map contains the name of the map, the operating system user and the user in PostgreSQL:

[email protected]:/u02/pgdata/DEV/ [PGDEV] echo "my-map       user1         user1" >> $PGDATA/pg_ident.conf
[email protected]:/u02/pgdata/DEV/ [PGDEV] tail -5 $PGDATA/pg_ident.conf
# Put your actual configuration here
# ----------------------------------

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
my-map       user1         user1

In our case the name of the PostgreSQL user and the name of the operating system user is the same. You might well map the operating system to another user in PostgreSQL, e.g. user2.

Obviously our user needs to exist in PostgreSQL, so:

[email protected]:/u02/pgdata/DEV/ [PGDEV] psql -c "create user user1 with login" postgres
CREATE ROLE

Finally we need to add an entry to pg_hba.conf that matches our map and authentication method:

[email protected]:/u02/pgdata/DEV/ [PGDEV] echo "host    all    all    192.168.22.0/24    ident map=my-map" >> $PGDATA/pg_hba.conf
[email protected]:/u02/pgdata/DEV/ [PGDEV] pg_ctl -D $PGDATA reload
server signaled

Lets try to connect to the database with our new user:

[email protected]:/u02/pgdata/DEV/ [PGDEV] sudo su - user1
[[email protected] ~]$ /u01/app/postgres/product/DEV/db_1/bin/psql -h 192.168.22.99 -p 5433 -U user1 postgres
psql: FATAL:  Ident authentication failed for user "user1"

… and that fails. When we check the PostgreSQL log file this is reported:

2019-03-19 18:33:26.724 CET - 1 - 8174 - 192.168.22.99 - [email protected] LOG:  could not connect to Ident server at address "192.168.22.99", port 113: Connection refused
2019-03-19 18:33:26.724 CET - 2 - 8174 - 192.168.22.99 - [email protected] FATAL:  Ident authentication failed for user "user1"
2019-03-19 18:33:26.724 CET - 3 - 8174 - 192.168.22.99 - [email protected] DETAIL:  Connection matched pg_hba.conf line 94: "host    all    all    192.168.22.0/24    ident map=my-map"

Our entry in pg_hba.conf matches, at least that is fine. But PostgreSQL is not able to connect to the Ident server and this confirms that nothing is listening on that port:

[email protected]:/u02/pgdata/DEV/ [PGDEV] sudo netstat -tulpen | grep 113

I am running CentOS 7 so the procedure for installing and starting an ident server is this:

[email protected]:/u02/pgdata/DEV/ [PGDEV] sudo yum search oident
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: pkg.adfinis-sygroup.ch
 * epel: pkg.adfinis-sygroup.ch
 * extras: mirror1.hs-esslingen.de
 * updates: mirror.softaculous.com
=============================================================================================== N/S matched: oident ===============================================================================================
oidentd.x86_64 : Implementation of the RFC1413 identification server

  Name and summary matches only, use "search all" for everything.

[email protected]:/u02/pgdata/DEV/ [PGDEV] sudo yum install oidentd
[email protected]:/u02/pgdata/DEV/ [PGDEV] systemctl list-unit-files | grep -i ident
oidentd.service                               disabled
[email protected]:/u02/pgdata/DEV/ [PGDEV] sudo systemctl enable oidentd.service
Created symlink from /etc/systemd/system/multi-user.target.wants/oidentd.service to /usr/lib/systemd/system/oidentd.service.
[email protected]:/u02/pgdata/DEV/ [PGDEV] sudo systemctl start oidentd.service
[email protected]:/u02/pgdata/DEV/ [PGDEV] sudo netstat -tulpen | grep 113
tcp        0      0 0.0.0.0:113             0.0.0.0:*               LISTEN      0          48553      8978/oidentd        

Lets try again:

[email protected]:/u02/pgdata/DEV/ [PGDEV] sudo su - user1
Last login: Tue Mar 19 18:33:25 CET 2019 on pts/1
[[email protected] ~]$ /u01/app/postgres/product/DEV/db_1/bin/psql -h 192.168.22.99 -p 5433 -U user1 postgres
psql (12devel)
Type "help" for help.

postgres=> 

… and now it works. We can connect using the operating system without specifying a password. To complete this post lets create another operating system user and map it to a different account in PostgreSQL:

[email protected]:/home/postgres/ [PGDEV] sudo groupadd user2
[email protected]:/home/postgres/ [PGDEV] sudo useradd -g user2 -m user2
[email protected]:/home/postgres/ [PGDEV] echo "my-map       user2         user1" >> $PGDATA/pg_ident.conf
[email protected]:/home/postgres/ [PGDEV] tail $PGDATA/pg_ident.conf
# a SIGHUP signal.  If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect.  You can
# use "pg_ctl reload" to do that.

# Put your actual configuration here
# ----------------------------------

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
my-map       user1         user1
my-map       user2         user1
[email protected]:/home/postgres/ [PGDEV] pg_ctl -D $PGDATA reload
server signaled

user2 should now be able to connect to user1 in PostgreSQL as well:

[email protected]:/u02/pgdata/DEV/ [PGDEV] sudo su - user2
Last login: Tue Mar 19 18:55:06 CET 2019 on pts/1
[[email protected] ~]$ /u01/app/postgres/product/DEV/db_1/bin/psql -h 192.168.22.99 -p 5433 -U user1 postgres
psql (12devel)
Type "help" for help.

postgres=> 

Finally, be careful with this authentication method. The documentation is very clear about that: “The drawback of this procedure is that it depends on the integrity of the client: if the client machine is untrusted or compromised, an attacker could run just about any program on port 113 and return any user name they choose. This authentication method is therefore only appropriate for closed networks where each client machine is under tight control and where the database and system administrators operate in close contact. In other words, you must trust the machine running the ident server. Heed the warning: The Identification Protocol is not intended as an authorization or access control protocol.”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure