<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>NSS/PAM PgSQL</title><meta name="generator" content="DocBook XSL Stylesheets V1.78.1" /></head><body><div class="book"><div class="titlepage"><div><div><h1 class="title"><a id="nsspampgsql"></a>NSS/PAM PgSQL</h1></div><div><div class="authorgroup"><div class="author"><h3 class="author"><span class="firstname">Jörg</span> <span class="surname">Wendland</span></h3><div class="affiliation"><div class="address"><p><br />
<code class="email"><<a class="email" href="mailto:jorgland@sol.wohnheim.uni-ulm.de">jorgland@sol.wohnheim.uni-ulm.de</a>></code><br />
</p></div></div></div><div class="author"><h3 class="author"><span class="firstname">Bret</span> <span class="surname">Mogilefsky</span></h3><div class="affiliation"><div class="address"><p><br />
<code class="email"><<a class="email" href="mailto:mogul-sysauth-pgsql@gelatinous.com">mogul-sysauth-pgsql@gelatinous.com</a>></code><br />
</p></div></div></div><div class="author"><h3 class="author"><span class="firstname">Wichert</span> <span class="surname">Akkerman</span></h3><div class="affiliation"><div class="address"><p><br />
<code class="email"><<a class="email" href="mailto:wichert@wiggy.net">wichert@wiggy.net</a>></code><br />
</p></div></div></div><div class="author"><h3 class="author"><span class="firstname">Christian</span> <span class="surname">Bayle</span></h3><div class="affiliation"><div class="address"><p><br />
<code class="email"><<a class="email" href="mailto:bayle@debian.org">bayle@debian.org</a>></code><br />
</p></div></div></div><div class="author"><h3 class="author"><span class="firstname">Russell</span> <span class="surname">Smith</span></h3><div class="affiliation"><div class="address"><p><br />
<code class="email"><<a class="email" href="mailto:mr-russ@pws.com.au">mr-russ@pws.com.au</a>></code><br />
</p></div></div></div></div></div><div><p class="copyright">Copyright © 2001, 2004, 2005 Jörg Wendland, Wichert Akkerman, Russell Smith</p></div><div><div class="legalnotice"><a id="idm140454815749920"></a><p>
Permission is granted to copy, distribute and/or modify this document
under the terms of the GNU Free Documentation License, Version 1.1 or
any later version published by the Free Software Foundation. There
are no invariant sections. A copy of the license is included in the
section entitled "GNU Free Documentation License".
</p></div></div></div><hr /></div><div class="toc"><p><strong>Table of Contents</strong></p><dl class="toc"><dt><span class="preface"><a href="#preface">Preface</a></span></dt><dt><span class="chapter"><a href="#prerequisites">1. Prerequisites</a></span></dt><dt><span class="chapter"><a href="#idm140454819686000">2. Module and Database Setup</a></span></dt><dd><dl><dt><span class="section"><a href="#dbstructure">required database structure</a></span></dt><dd><dl><dt><span class="section"><a href="#tables">tables and fields</a></span></dt></dl></dd></dl></dd><dt><span class="chapter"><a href="#configuration">3. configuring the module</a></span></dt><dt><span class="chapter"><a href="#nss-setup">4. Name Service Switch Setup</a></span></dt><dt><span class="appendix"><a href="#appendix">A. Examples</a></span></dt></dl></div><div class="list-of-examples"><p><strong>List of Examples</strong></p><dl><dt>A.1. <a href="#dbschema">Database schema</a></dt><dt>A.2. <a href="#conffile">Configuration file</a></dt></dl></div><div class="preface"><div class="titlepage"><div><div><h1 class="title"><a id="preface"></a>Preface</h1></div></div></div><p>
This document covers setup and use of the Name Service Switch (NSS)
Module <code class="filename">libnss-pgsql.so</code>. Its purpose is replace the
flatfile user, group and shadow databases in <code class="filename">/etc</code> with a
relational database on a PostgreSQL server. It is highly configurable to
fit onto most existing databases.
</p></div><div class="chapter"><div class="titlepage"><div><div><h1 class="title"><a id="prerequisites"></a>Chapter 1. Prerequisites</h1></div></div></div><p>
For installation instructions please see the files
<code class="filename">README</code> and <code class="filename">INSTALL</code> in the
source distribution.
</p><p>
To use this module you will need a PostgreSQL database containing some
sort of user account information. See <a class="xref" href="#dbschema" title="Example A.1. Database schema">Example A.1, “Database schema”</a> for an
example database.
</p></div><div class="chapter"><div class="titlepage"><div><div><h1 class="title"><a id="idm140454819686000"></a>Chapter 2. Module and Database Setup</h1></div></div></div><div class="toc"><p><strong>Table of Contents</strong></p><dl class="toc"><dt><span class="section"><a href="#dbstructure">required database structure</a></span></dt><dd><dl><dt><span class="section"><a href="#tables">tables and fields</a></span></dt></dl></dd></dl></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="dbstructure"></a>required database structure</h2></div></div></div><p>
To use this module with a database you will need at least three tables
in that database. One for account data (the information usually stored
in <code class="filename">/etc/passwd</code>), one for group data
(<code class="filename">/etc/group</code>) and another one storing information
about groupmembership (there is a m:n relation between passwd and group
so you need this weak entity). If you have an existing database you do
not want to modify you can use views or table expressions (see
<a class="xref" href="#conffile" title="Example A.2. Configuration file">Example A.2, “Configuration file”</a>), of course. You can also optionally store
shadow information (<code class="filename">/etc/shadow</code>) in the database
as well. Depending on your security requirements another table or view
may be needed.
</p><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="tables"></a>tables and fields</h3></div></div></div><p>
There have to be some required fields in those tables. They are
described below.
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term">passwd table (see <span class="citerefentry"><span class="refentrytitle">getpwnam</span>(3)</span>
for more information)</span></dt><dd><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>login name</p></li><li class="listitem"><p>password</p></li><li class="listitem"><p>numerical user id (uid)</p></li><li class="listitem"><p>numerical primary group id (gid)</p></li><li class="listitem"><p>login shell</p></li><li class="listitem"><p>description (gecos)</p></li><li class="listitem"><p>home directory</p></li></ul></div></dd><dt><span class="term">group table (see <span class="citerefentry"><span class="refentrytitle">getgrnam</span>(3)</span>)</span></dt><dd><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>group name</p></li><li class="listitem"><p>group password</p></li><li class="listitem"><p>numerical group id (gid)</p></li></ul></div></dd></dl></div><p>
An example database schema can be found in <a class="xref" href="#dbschema" title="Example A.1. Database schema">Example A.1, “Database schema”</a>. See there for how to construct the
groupmember table.
</p></div></div></div><div class="chapter"><div class="titlepage"><div><div><h1 class="title"><a id="configuration"></a>Chapter 3. configuring the module</h1></div></div></div><p>
Having a working database, you must write your configfile
<code class="filename">/etc/nss-pgsql.conf</code>. If you also want shadow
support, you must write a second config file
<code class="filename">/etc/nss-pgsql-root.conf</code>
These files have a simple syntax:
</p><div class="informalexample"><pre class="programlisting">
LINE := COMMENT | EMPTY | STATEMENT
COMMENT := '#' <text>
EMPTY := ''
STATEMENT := KEYWORD '=' <value>
</pre></div><p>
With <span class="command"><strong>KEYWORD</strong></span> being one of the following (all are
required):
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term">connectionstring</span></dt><dd><p>The connection string to connect to the server. You can include any valid PostgreSQL options.</p></dd><dt><span class="term">getpwnam</span></dt><dd><p>A query that returns passwd_name, passwd_passwd, passwd_gecos, passwd_dir, passwd_shell, passwd_uid, passwd_gid for a given username</p></dd><dt><span class="term">getpwuid</span></dt><dd><p>A query that returns passwd_name, passwd_passwd, passwd_gecos, passwd_dir, passwd_shell, passwd_uid, passwd_gid for a given uid</p></dd><dt><span class="term">getgrnam</span></dt><dd><p>A query that returns group_name, group_passwd, group_gid for a given group name</p></dd><dt><span class="term">getgrgid</span></dt><dd><p>A query that returns group_name, group_passwd, group_gid for a given gid</p></dd><dt><span class="term">allusers</span></dt><dd><p>A query that returns username, password, gecos, home_dir, shell, uid, primarygid for every user</p></dd><dt><span class="term">allgroups</span></dt><dd><p>A query that returns group_name, group_passwd, group_gid for every group</p></dd><dt><span class="term">groups_dyn</span></dt><dd><p>A query that returns a list of gid's a particular user is in. excluding their primary group.</p></dd><dt><span class="term">getgroupmembersbygid</span></dt><dd><p>A query that returns a list of usernames for a given gid</p></dd><dt><span class="term">shadowbyname</span></dt><dd><p>A query returning shadow_name, shadow_passwd, shadow_lstchg, shadow_min, shadow_max, shadow_warn, shadow_inact, shadow_expire, shadow_flag
for a given username</p></dd><dt><span class="term">shadow</span></dt><dd><p>A query returning shadow_name, shadow_passwd, shadow_lstchg, shadow_min, shadow_max, shadow_warn, shadow_inact, shadow_expire, shadow_flag
for all users</p></dd></dl></div></div><div class="chapter"><div class="titlepage"><div><div><h1 class="title"><a id="nss-setup"></a>Chapter 4. Name Service Switch Setup</h1></div></div></div><div class="caution" style="margin-left: 0.5in; margin-right: 0.5in;"><table border="0" summary="Caution"><tr><td rowspan="2" align="center" valign="top" width="25"><img alt="[Caution]" src="caution.png" /></td><th align="left">Caution</th></tr><tr><td align="left" valign="top"><p>
You can do serious damage to your system if you do not know what you
are doing! If you are trying changes in the Name Service Switch for the
very first time, consider doing that in a chroot environment or a test
machine. Have at least an editor with
<code class="filename">/etc/nsswitch.conf</code> open to revert your changes
easily. You have been warned!
</p></td></tr></table></div><p>
Setting up the Name Service Switch is fairly easy, given a
working installation of the libnss-pgsql module. Just add an
entry <span class="command"><strong>pgsql</strong></span> the <span class="command"><strong>passwd</strong></span> and
<span class="command"><strong>group</strong></span> lines of
<code class="filename">/etc/nsswitch.conf</code>. You can tune the behaviour with
options between the entries to get a fully working system. Useful lines
in <code class="filename">/etc/nsswitch.conf</code> would look like these:
</p><div class="informalexample"><pre class="programlisting">
passwd: files [SUCCESS=continue] pgsql
group: files [SUCCESS=continue] pgsql
shadow: files [SUCCESS=continue] pgsql
</pre></div><p>
This will make your libc look into the standard
<code class="filename">passwd</code> and <code class="filename">group</code> files first
and then use libnss-pgsql. The option
<span class="command"><strong>[SUCCESS=continue]</strong></span> ensures that all accounts or groups
are retrieved when using the iteration functions
<span class="citerefentry"><span class="refentrytitle">getpwent</span>(3)</span> and
<span class="citerefentry"><span class="refentrytitle">getgrent</span>(3)</span>.
</p></div><div class="appendix"><div class="titlepage"><div><div><h1 class="title"><a id="appendix"></a>Appendix A. Examples</h1></div></div></div><div class="example"><a id="dbschema"></a><p class="title"><strong>Example A.1. Database schema</strong></p><div class="example-contents"><pre class="programlisting">
-- 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 ("username")
);
CREATE TABLE "usergroups" (
"gid" int4 NOT NULL,
"uid" int4 NOT NULL,
PRIMARY KEY ("gid", "uid"),
CONSTRAINT "ug_gid_fkey" FOREIGN KEY ("gid") REFERENCES "groups"("gid"),
CONSTRAINT "ug_uid_fkey" FOREIGN KEY ("uid") REFERENCES "accounts"("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")
);
</pre></div></div><br class="example-break" /><div class="example"><a id="conffile"></a><p class="title"><strong>Example A.2. Configuration file</strong></p><div class="example-contents"><pre class="programlisting">
connectionstring = hostaddr=127.0.0.1 dbname=unix user=postgres password=XXX 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
</pre><pre class="programlisting">
# example configfile for PostgreSQL NSS module
# this file must be readable for root only
connectionstring = hostaddr=127.0.0.1 dbname=unix user=postgres 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
</pre></div></div><br class="example-break" /></div></div></body></html>
