This site will look much better in a browser that supports web standards, but it is accessible to any browser or Internet device.

How to use Postgres as an NSS module
December 21st, 2001

1. Introduction to NSS

The GNU GLIBC page on NSS should cover all your [technical] questions. Basically NSS is a 'plugin' methodology. If you have PAM installed, you're familiar with this methodology already. NSS is part of libc and operates under PAM. The chain for PAM is like this. GLIBC -> NSS -> PAM -> Application. Applications do not need to be PAM aware to use NSS, all of NSS is part of GLIBC, and all applications that use getpwnam() and family (i.e. 99.99999% of *nix software), understand it already.

2. Discussion of reason

Why do it this way? What are the pros and cons? Why Postgres? These are some of the questions that come up when someoen is introduced to the idea. Here are some of the answers.

What are the benefits?
It allows for centralized disjointed management. It allows for rapid development and maintenance of management interfaces based on a common network technology. You can use PHP to rapidly develop a simple or complex management system or if you already manage your network with PHP, you can drop in a plugin for this. Basically, any application that understands getpwnam() and it's friends can now authenticate in a much more versatile manner.
What problems or drawbacks might there be?
Few really. It takes just a little bit of time to set it up. The SQL conversation isn't encrypted by default installation of Postgres, if you are running this system in a public group of servers then you will want to turn on SSL for postgres and do likewise w/ the library client so the usernames and passwords are not sniffed.
Why am I recommending Postgres?
Postgres is my RDBMS of choice. I like it more then mysql for a number of reasons and those reasons are based on past experience of several years. Some of those reasons are SQL standardization adherence, stability, granularity of operations (table locking v.s. row locking), and resource footprint (memory/cpu use). I'd rather not evangelise about it, it's a matter of personal preference in most cases. Everything SQL on Blue-Labs.org uses Postgres so that's what I'll write about.

3. Setting up Postgres for libnss_pgsql

You'll need to create a username, database, and the tables initially. Then all you do is populate and update these tables. Nothing more too it.

Create the username
I actually recommend using three usernames. Two to do the lookups and one to do the updates. This way you are slighly more protected when you write your PHP web pages or other applications. They are distinct and isolated. They are synonymous to: /etc/password lookups, /etc/shadow lookups, and changing information in both of those files. 
  1. CREATE USER nss_update;
  2. CREATE USER pwlookup;
  3. CREATE USER shlookup;
Create the database
  1. CREATE DATABASE pg_auth;
Create the tables
There are three tables involved here and two sequences. The sequences give you easy access to the next available UID/GID if you choose to go that route. There are constraints built into the tables to help prevent exploitation. First, no duplicate UIDs, and more importantly, all UIDs must be above 1000. Feel free to adjust this to your policies. 
  1. CREATE SEQUENCE "uid_seq" start 1000 increment 1 maxvalue 2147483647 minvalue 1000 cache 1;
  2. CREATE SEQUENCE "gid_seq" start 1000 increment 1 maxvalue 2147483647 minvalue 1000 cache 1;
  3. CREATE TABLE "nss_passwd" (
	"username" TEXT NOT NULL,
	"passwd" CHARACTER(1) DEFAULT 'x' NOT NULL,
	"uid" INTEGER DEFAULT nextval('uid_seq'::text) NOT NULL,
	"gid" INTEGER,
	"gecos" TEXT,
	"homedir" TEXT,
	"shell" TEXT,
	CONSTRAINT "nss_passwd_check_uid_gt_1k" CHECK ((uid >= 1000)),
	CONSTRAINT "nss_passwd_pkey" PRIMARY KEY ("username", "uid")
);
  4. CREATE TABLE "nss_shadow" (
	"username" TEXT NOT NULL,
	"passwd" TEXT NOT NULL,
	"lastchange" INTEGER,
	"min" INTEGER DEFAULT 0,
	"max" INTEGER DEFAULT 1,
	"warn" INTEGER DEFAULT 7,
	"inact" INTEGER DEFAULT -1,
	"expire" INTEGER DEFAULT -1,
	"flag" INTEGER DEFAULT -1,
	CONSTRAINT "nss_shadow_pkey" PRIMARY KEY ("username")
);
  5. CREATE TABLE "nss_group" (
	"groupname" TEXT NOT NULL,
	"passwd" TEXT,
	"gid" INTEGER DEFAULT nextval('gid_seq'::text) NOT NULL,
	"members" TEXT,
	CONSTRAINT "nss_group_check_gid_gt_1k" CHECK ((gid >= 1000)),
	CONSTRAINT "nss_group_pkey" PRIMARY KEY ("groupname", "gid")
);
Set the permissions on the tables and sequences
  1. REVOKE ALL on "uid_seq" from PUBLIC;
GRANT ALL on "uid_seq" to "postgres";
GRANT UPDATE,DELETE,SELECT on "uid_seq" to "nss_update";
  2. REVOKE ALL on "gid_seq" from PUBLIC;
GRANT ALL on "gid_seq" to "postgres";
GRANT UPDATE,DELETE,SELECT on "gid_seq" to "nss_update";
  3. REVOKE ALL on "nss_passwd" from PUBLIC;
GRANT ALL on "nss_passwd" to "postgres";
GRANT INSERT,UPDATE,DELETE,SELECT on "nss_passwd" to "nss_update";
GRANT SELECT on "nss_passwd" to "pwlookup";
GRANT UPDATE,DELETE,SELECT on "nss_passwd" to "nss_user";
  4. REVOKE ALL on "nss_shadow" from PUBLIC;
GRANT ALL on "nss_shadow" to "postgres";
GRANT INSERT,UPDATE,DELETE,SELECT on "nss_shadow" to "nss_update";
GRANT SELECT on "nss_shadow" to "shlookup";
GRANT UPDATE,DELETE,SELECT on "nss_shadow" to "nss_user";

  5. REVOKE ALL on "nss_group" from PUBLIC;
GRANT ALL on "nss_group" to "postgres";
GRANT INSERT,UPDATE,DELETE,SELECT on "nss_group" to "nss_update";
GRANT SELECT on "nss_group" to "pwlookup";

4. Setting up NSS for libnss_pgsql

Obtain the code for libnss_pgsql, compile it, install the library in /lib. Run ldconfig. Create two files in /etc, /etc/nss-pgsql-root.conf and /etc/nss-pgsql.conf. These are used for (a) root access, i.e. getting the hash from /etc/shadow, and (b) for /etc/password type lookup. Of course this pertains to the password and shadow table, not the password and shadow files. Finally, edit /etc/nsswitch.conf and add 'pgsql' to the end of 'password', 'shadow', and 'group'.

The /etc/nss-pgsql-root.conf file
host=127.0.0.1 port=5432 dbname=pg_auth user=shlookup password=R92Sfva8
The /etc/nss-pgsql.conf file
host=127.0.0.1 port=5432 dbname=pg_auth user=pwlookup password=a92nf2a

Note that these two are the same save for the username and password.

5. Managing it

I don't have publically available PHP web pages for this yet (they are sloppy and need rewritten). Sorry, but it is quite easy.

6. Resources

Get your copy of libnss_pgsql from Freshmeat. Note, there are several versions flying around, adjust the above instructions as needed if you choose a package that differs. (Don't use the PAM module project)

7. Maintenance of this page

This page is actively maintained and updated. Both users and vendors are encouraged to submit updates to this page. Please send all comments and page updates to David Ford.

8. Credits

This page is based on the preliminary works of Alessandro. I modified his source code in the library I am using now. I'll post my works when time permits.