Linux users and groups in PostgreSQL database

Standard Linux user and group accounts are defined in three files:

• /etc/passwd
• /etc/shadow
• /etc/group

These files store user accounts and group information one per line, as fields separated by “:”. That kind of structure suffices for most user and group authentication needs, but the problem arises when you would like to have e.g. centralized authentication database in your network or more flexible means for managing user accounts.

I am currently designing and developing solution for hosting servers that requires defining customer Linux accounts. I could use for e.g. LDAP service for storing this information, but I prefer database, since it’s easier to develop tools and generally more flexible solution in the context of whole system.

I am a fan of PostgreSQL database server and it seems that there is a little nifty plugin for NSS (Name Service Switch) which allows you to store this information in database. The Name Service Switch is a standard Linux facility for common information and name resolution, which allows you to combine this information from multiple sources (flat files, LDAP, NIS and also various databases). We will use this facility to implement user and group information and authentication stored in PostgreSQL database.

Enough talk, let’s get to action. The plugin you need for NSS is named libnss-pgsql2. In some systems it may be lib64nss-pgsql2, but also it may be named libnss-pgsql or lib64nss-pgsql. Beware however that you need plugin version 2, and some systems use package name without number at the end, but it is still the correct version. Some systems however use libnss-pgsql to indicate an older version of the plugin, which you can use, but this is out of scope of this post. I am using Linux Debian 7.8 system, so the following commands are for this system, but this tutorial still will be relevant for other distributions after slightly changing commands (i.e. package manager, etc.).

We’ll begin by installing our plugin from terminal:

$ sudo apt-get install libnss-pgsql2

After this operation is completed you’ll have default configuration files ready. We’ll talk about it soon. Remember that you should install this package using sudo if you are normal user (i.e. non-administrative one) or you should issue these commands as root user. You can also take notice that when installing this plugin, apt-get suggests installing nscd. This is a Name Service Cache Daemon which will speed up resolving users and groups by caching them in memory, but for now we don’t want it to interfere with setting up libnss-pgsql2 plugin. We’ll get back to nscd later.

After installing NSS plugin we have to create our PostgreSQL database and database users which will be used to access system user and group information. I assume you already have PostgreSQL server installed. If not, install it and set it up first before you continue!

Let’s login as postgres user:

$ sudo su - postgres

We should now see a prompt like this:

postgres@localhost:~$

Now we create two users for accessing passwd, group and shadow information:

• nss – which will be used to access passwd and group information
• nssadmin – which will be used to access shadow information

Remember that we have to create two distinct users, because we don’t want to give access for non-administrative users to information stored in shadow since there are password hashes, which may be used by malicious users (i.e. hackers). Access to shadow information should be only available to administrative user (mostly root account). Let’s create those two users:

postgres@localhost:~$ createuser -P nss
Enter password for new role: PASSWORD
Enter it again: PASSWORD
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n


This will create new PostgreSQL user (role) named nss. Remember to provide password for user nss substituting PASSWORD with your own. We also disallow this user to be superuser, disallow creating databases and more PostgreSQL user accounts (which are called roles). We repeat this procedure for user (role) nssadmin, changing password to be different than nss role:

postgres@localhost:~$ createuser -P nssadmin
Enter password for new role: PASSWORD
Enter it again: PASSWORD
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n


Now we need to create and set up database used by libnss-pgsql2. Still being logged in as user postgres we do:

postgres@localhost:~$ createdb -O postgres -E utf-8 unix

This will create database named unix which is owned by role postgres and has encoding set to UTF-8. Fairly standard. However if your postgres role is not considered secure, you have changed your PostgreSQL administrative role to some other or would like to have another dedicated user that may be used for updating and managing system user accounts database, consider changing option -O postgres to -O your_role_name. Just remember that you have to create this role first if it does not exist (you can do this like I’ve shown you before).

Now let’s verify that we have access to this newly created database. Still being logged as postgres user let’s type in:

postgres@localhost:~$ psql unix
psql (9.1.15)
Type "help" for help.

unix=#

If you see no errors and something like above, we have our database working. Type in “\q” to quit PostgreSQL shell. Now we have to create the database structure. Here’s what it should look like:

-- Default table setup for nss-pgsql

CREATE SEQUENCE group_id MINVALUE 1000 MAXVALUE 2147483647 NO CYCLE;
CREATE SEQUENCE user_id MINVALUE 1000 MAXVALUE 2147483647 NO CYCLE;

CREATE TABLE "group_table" (
"gid" int4 NOT NULL DEFAULT nextval('group_id'),
"groupname" character varying(16) NOT NULL,
"descr" character varying,
"passwd" character varying(20),
PRIMARY KEY ("gid")
);

CREATE TABLE "passwd_table" (
"username" character varying(64) NOT NULL,
"passwd" character varying(128) NOT NULL,
"uid" int4 NOT NULL DEFAULT nextval('user_id'),
"gid" int4 NOT NULL,
"gecos" character varying(128),
"homedir" character varying(256) NOT NULL,
"shell" character varying DEFAULT '/bin/bash' NOT NULL,
PRIMARY KEY ("uid")
);

CREATE TABLE "usergroups" (
"gid" int4 NOT NULL,
"uid" int4 NOT NULL,
PRIMARY KEY ("gid", "uid"),
CONSTRAINT "ug_gid_fkey" FOREIGN KEY ("gid") REFERENCES "group_table"("gid"),
CONSTRAINT "ug_uid_fkey" FOREIGN KEY ("uid") REFERENCES "passwd_table"("uid")
);

CREATE TABLE "shadow_table" (
"username" character varying(64) NOT NULL,
"passwd" character varying(128) NOT NULL,
"lastchange" int4 NOT NULL,
"min" int4 NOT NULL,
"max" int4 NOT NULL,
"warn" int4 NOT NULL,
"inact" int4 NOT NULL,
"expire" int4 NOT NULL,
"flag" int4 NOT NULL,
PRIMARY KEY ("username")
);

This SQL defines two sequences: one for groups and one for user accounts. You can adjust MINVALUE to set starting UID and GID accordingly. The above SQL defines four tables:

• group_table – which is equivalent for /etc/group
• passwd_table – which is equivalent for /etc/passwd
• shadow_table – which is equivalent for /etc/shadow
• usergroups – which stores a relation between passwd_table and group_table, that defines additional groups to which user is also assigned (primary group is stored in passwd_table, so you shouldn’t define this group in usergroups table)

You should save the above SQL definition in a file db_schema.sql and then under user postgres do:

postgres@localhost:~$ psql unix < db_schema.sql
CREATE SEQUENCE
CREATE SEQUENCE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

If no errors occured you should have your database schema set up in database unix. Now let’s verify that everything is ok. Type in psql unix and issue “\d” after logging in to unix database:

postgres@srv01:~$ LC_ALL=en_US.UTF8 psql unix
psql (9.1.15)
Type "help" for help.

unix=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner
--------+--------------+----------+----------
 public | group_id     | sequence | postgres
 public | group_table  | table    | postgres
 public | passwd_table | table    | postgres
 public | shadow_table | table    | postgres
 public | user_id      | sequence | postgres
 public | usergroups   | table    | postgres
(6 rows)

If you see something similar it means that the database schema is properly set up. Now still being in PostgreSQL shell we have to grant priviledges to our two new roles we have defined before. You can do this typing in:

unix=# grant select on passwd_table to nss;
GRANT
unix=# grant select on group_table to nss;
GRANT
unix=# grant select on passwd_table to nssadmin;
GRANT
unix=# grant select on group_table to nssadmin;
GRANT
unix=# grant select on shadow_table to nssadmin;
GRANT
unix=# grant select on usergroups to nssadmin;
GRANT
unix=# grant select on usergroups to nss;
GRANT

This will grant SELECT priviledge on tables passwd_table, group_table and usergroups to role nss, and it will also grant SELECT priviledge to role nssadmin on all tables. We don’t want to grant any other priviledges on those tables to these two users, since they will be used only as read only by NSS facility. Watch out for granting shadow_table priviledge to nss role. You shouldn’t do it!

Now we can quit PostgreSQL shell by typing “\q” and then logout from postgres system account by typing in “exit” or pressing CTRL+D. Let’s verify if our new roles namely nss and nssadmin have access to our database. Under normal user account type in:

wolverine@localhost:~$ psql -U nss -W unix
Password for user nss:
psql (9.1.15)
Type "help" for help.

unix=>

and then if no errors occured, type in PostgreSQL shell:

unix=> select * from passwd_table;
 username | passwd | uid | gid | gecos | homedir | shell
----------+--------+-----+-----+-------+---------+-------
(0 rows)

unix=> select * from group_table;
 gid | groupname | descr | passwd
-----+-----------+-------+--------
(0 rows)

unix=> select * from usergroups;
 gid | uid
-----+-----
(0 rows)

unix=> select * from shadow_table;
ERROR:  permission denied for relation shadow_table

This shows that we have SELECT priviledge for role nss to tables: passwd_table, group_table, usergroups, but not to shadow_table – which is exactly what we want. Do the same verification for user nssadmin and you should see something like this:

wolverine@srv01:~$ psql -U nssadmin -W unix
Password for user nssadmin:
psql (9.1.15)
Type "help" for help.

unix=> select * from passwd_table;
 username | passwd | uid | gid | gecos | homedir | shell
----------+--------+-----+-----+-------+---------+-------
(0 rows)

unix=> select * from group_table;
 gid | groupname | descr | passwd
-----+-----------+-------+--------
(0 rows)

unix=> select * from usergroups;
 gid | uid
-----+-----
(0 rows)

unix=> select * from shadow_table;
 username | passwd | lastchange | min | max | warn | inact | expire | flag
----------+--------+------------+-----+-----+------+-------+--------+------
(0 rows)

This shows that role nssadmin has permissions to SELECT on all tables. If any errors occured during the above verification, you have to make sure that roles nss and nssadmin have SELECT permission properly granted. It may be necessary sometimes to grant access to database schema itself. Consult PostgreSQL documentation on how to do it.

You may wonder why I have given so much time to ensure proper priviledges to the database. It seems that if you fail to do it properly, you will have hard time debugging why the libnss-pgsql is not working. The scarce documentation for libnss-pgsql doesn’t help either and there is literally no information available on Google if you are looking for help. So, make sure you have your database server working properly and that the roles have necessary priviledges to access database tables. Unfortunatelly, there is no way to debug or see the logs for libnss-pgsql plugin, so you have to be extra careful with this step.

When the database is properly setup we can setup configuration files for libnss-pgsql. There are two files in /etc directory which handles querying information from your database and feeding it to NSS facility.

The first one is /etc/nss-pgsql.conf and should look like this:

connectionstring        = hostaddr=127.0.0.1 dbname=unix user=nss password=PASSWORD connect_timeout=1
# you can use anything postgres accepts as table expression

# Must return "usernames", 1 column, list
getgroupmembersbygid    = SELECT username FROM passwd_table WHERE gid = $1
# Must return passwd_name, passwd_passwd, passwd_gecos, passwd_dir, passwd_shell, passwd_uid, passwd_gid
getpwnam        = SELECT username, passwd, gecos, homedir, shell, uid, gid FROM passwd_table WHERE username = $1
# Must return passwd_name, passwd_passwd, passwd_gecos, passwd_dir, passwd_shell, passwd_uid, passwd_gid
getpwuid        = SELECT username, passwd, gecos, homedir, shell, uid, gid FROM passwd_table WHERE uid = $1
# All users
allusers        = SELECT username, passwd, gecos, homedir, shell, uid, gid FROM passwd_table
# Must return group_name, group_passwd, group_gid
getgrnam        = SELECT groupname, passwd, gid FROM group_table WHERE groupname = $1
# Must return group_name, group_passwd, group_gid
getgrgid        = SELECT groupname, passwd, gid FROM group_table WHERE gid = $1
# Must return gid.  %s MUST appear first for username match in where clause
groups_dyn      = SELECT ug.gid FROM passwd_table JOIN usergroups USING (uid) where username = $1 and ug.gid <> $2
allgroups       = SELECT groupname, passwd, gid  FROM group_table

Remember to substitute PASSWORD with your nss role password.

The second file is /etc/nss-pgsql-root.conf and should look like this:

# example configfile for PostgreSQL NSS module
# this file must be readable for root only

shadowconnectionstring = hostaddr=127.0.0.1 dbname=unix user=nssadmin password=PASSWORD connect_timeout=1

#Query in the following format
#shadow_name, shadow_passwd, shadow_lstchg, shadow_min, shadow_max, shadow_warn, shadow_inact, shadow_expire, shadow_flag
shadowbyname = SELECT * FROM shadow_table WHERE username = $1
shadow = SELECT * FROM shadow_table

Also remember to substitute PASSWORD with nssadmin role password. If you fail to do this, you may render your system completely unaccessible! Both configuration files must be owned by root and the second one should be readable only by root. Ensure it has proper permissions set:

wolverine@localhost:~$ sudo chown root:root /etc/nss-pgsql.conf /etc/nss-pgsql-root.conf
wolverine@localhost:~$ sudo chmod 644 /etc/nss-pgsql.conf
wolverine@localhost:~$ sudo chmod 600 /etc/nss-pgsql-root.conf

Now we have to be extra careful! I recommend you to leave another terminal open with editor open on /etc/nsswitch.conf, until we verify everything works as it should. If there are errors or the plugin is not working properly YOU WILL DISABLE ACCESS TO THE WHOLE SYSTEM (i.e. ssh, login and other services depending on system user accounts). Do not log out from root account at least on one terminal before you make sure everything works properly!

Let’s login as root:

sudo su

and then open up /etc/nsswitch.conf in vim or another console editor. Do the same on another terminal console (just so we can be sure to revert to previous configuration if anything goes wrong). When you have opened /etc/nsswitch.conf in editor, you have to change three lines to look like this:

passwd:     pgsql compat
group:      pgsql compat
shadow:     pgsql compat

Instead of compat you may have files, so if you do substitute compat to files and you should be ok:

passwd: pgsql compat
group: pgsql compat
shadow: pgsql compat

Save the file and close it (leave it open in another terminal). What we have done now is we say to NSS to first look for user in database and if it fails, fall back to /etc/passwd, /etc/shadow and /etc/group files.

WARNING! The documentation for libnss-pgsql2 plugin states that you should state compat or files first and after this pgsql. THIS IS WRONG AND MAY RENDER YOUR SYSTEM UNUSABLE! The same goes for “[SUCCESS=continue]”. Do not use this statement in /etc/nsswitch.conf because it DOESN’T WORK PROPERLY and WILL DENY ACCESS TO ALL USERS!

Now we have to test if NSS is still resolving users and groups. You can do this by typing in:

getent group
getent passwd
getent shadow

Do this under root and under normal user. For root user you should see entries for group, passwd and shadow (essentially what is currently available in /etc files). The normal user should see group and passwd entries, but running getent shadow should not return anything. Here’s an example:

root@localhost:~# getent group
root:x:0:
bin:x:1:
daemon:x:2:
sys:x:3:
adm:x:4:
tty:x:5:
disk:x:6:
lp:x:7:
mem:x:8:
kmem:x:9:
wheel:x:10:wolverine
mail:x:12:postfix
news:x:13:
uucp:x:14:
man:x:15:
...

If any of the getent commands hang up or are not returning entries it indicates problem with libnss-pgsql2 configuration or nsswitch.conf. In this case I recommend to revert back to original /etc/nsswitch.conf and make sure you have made everything properly especially if PostgreSQL server is running, if the database exists and has proper schema and also if roles have proper priviledges. Make sure that your pg_hba.conf is set up properly and that PostgreSQL is accessible through TCP socket on localhost (127.0.0.1) or any other address if you are using another server for PostgreSQL.

If all getent commands behaved properly as described and returned entries when they should it should mean that everything is working properly and our plugin is used by NSS facility.

Now we can create our first user in the database and see if we can log in. Let’s start by logging in as postgres user and then psql to our unix database:

wolverine@localhost:~$ sudo su - postgres
[sudo] password for wolverine:
postgres@localhost:~$ psql unix
psql (9.1.15)
Type "help" for help.

unix=# insert into group_table (groupname) values ('testgroup');
INSERT 0 1

Now let’s verify our group is inserted into table and get it’s gid, which we will need for setting up user group:

unix=# select * from group_table;
  gid  | groupname | descr | passwd
-------+-----------+-------+--------
 10000 | testgroup |       |
(1 row)

unix=# insert into passwd_table (username, passwd, gid, homedir) values ('testuser', 'x', 10000, '/home/testuser');
INSERT 0 1

and verify if the user passwd entry is set:

unix=# select * from passwd_table;
 username | passwd |  uid  |  gid  | gecos |    homedir     |   shell
----------+--------+-------+-------+-------+----------------+-----------
 testuser | x      | 10000 | 10000 |       | /home/testuser | /bin/bash
(1 row)

As you can see the passwd entry exists. We have set ‘x’ as user password which means that we will use shadow_table to store password instead of plain text password in passwd_table (exactly the same as /etc files are doing). Let’s set up shadow_table entry for our user. First we need to create extension PGCrypto on our database:

unix=# create extension pgcrypto;
CREATE EXTENSION

Remember that you must have PGCrypto installed for your PostgreSQL server installation for this to work. You can also create extension on your database only with administrative role (e.g. postgres). Now let’s insert shadow information for our user:

unix=# insert into shadow_table values ('testuser', crypt('mypassword', gen_salt('md5')), cast(extract(epoch from now()) as INTEGER) / 86400, 0, 99999, 7, 0, -1, 0);
INSERT 0 1

Let’s stop here for a moment. Since shadow_table and /etc/shadow format may not be very obvious I’ll explain each field here:

• username – name of the user stored as username in passwd_table
• passwd – encrypted hash for password
• lastchange – number of days since epoch (1970-01-01)
• min – minimal number of days before user is allowed to change password
• max – maximum number of days after which user must change password
• warn – number of days before maximum when user is warned to change password
• inact – number of days after password expires that account will be disabled
• expire – number of days since epoch (1970-01-01) account will be disabled and cannot be used to login
• flag – reserved field

Our insert to shadow_table may not be obvious since we have used two value constructs:

crypt('mypassword', gen_salt('md5')) 
cast(extract(epoch from now()) as INTEGER) / 86400

The first one uses PGCrypto extension to generate salted password hash from password “mypassword” and salt using md5 algorithm. YOU SHOULD NOT USE MD5 for salting, because MD5 is insecure. PGCrypto however doesn’t support newer hash algorithms like SHA-256 or SHA-512 which are considered secure. For salting with these algorithms you have to devise your own solution, which is beyond the scope of this article.

The second one is just a simple algorithm that extracts UNIX TIMESTAMP (epoch) from current date (now) and since this is FLOAT, casts it to INTEGER and then divides the number of seconds by number of seconds in one day (86400) to obtain number of days since 1970-01-01. We need this value inserted into lastchange field.

Now, verify that the shadow entry was inserted properly:

unix=# select * from shadow_table;
 username |               passwd               | lastchange | min |  max  | warn | inact | expire | flag
----------+------------------------------------+------------+-----+-------+------+-------+--------+------
 testuser | $1$dksgT54M$JVwFYQS/j8NkZHeGVgbki0 |      16575 |   0 | 99999 |    7 |     0 |     -1 |    0
(1 row)

If everything was ok, close psql shell. In case you are not logged in under normal user (i.e. you are root), logout. Now you should be able to test if you can log in with your newly created user in the database by typing in:

wolverine@srv01:~$ id testuser
uid=10000(testuser) gid=10000 groups=10000
wolverine@localhost:~$ su - testuser
Password:
No directory, logging in with HOME=/
testuser@localhost:/$

Congratulations! Authentication through PostgreSQL database now works and you can define your new users simply by inserting records to the database. Of course, you have to create user directories and skeleton files, since you cannot use useradd, usermod, groupadd and other such tools. You should build your own solutions for adding, modifying and deleting users in the database and ensuring to properly manage home directories for each newly added or modified user.

The last thing we should do is installing nscd. nscd is a Name Service Caching Daemon which will cache entries from your PostgreSQL database in memory. This will significantly speed up user and group lookups and decrease performance impact on PostgreSQL server. This is especially important when user and group databases are large and there are many queries for this information. You can install nscd by typing in:

wolverine@localhost:~$ sudo apt-get install nscd

That’s it! Authenticating user accounts through PostgreSQL database is now fully set up. If you have any questions or comments, I’d love to hear them.