pam_pgsql 0.7

Introduction

This module allows PAM-enabled applications to use authentication information stored in PostgreSQL tables.

This module is based in part on the FreeBSD pam_unix module, and Debian's pam_mysql module, but was written from scratch using the two as a reference.

There is another pam_pgsql module, but the sources appear to have vanished, hence this module.

Changes since last release

See the file CHANGELOG.

Compilation & Installation

pam_pgsql uses autoconf, thus, compiling should be a matter of:

$ ./configure
$ make
$ make install

Or if you're using a Git version, run this command before them all:

$ ./autogen.sh

Compilation has been tested on Debian GNU/Linux, ArchLinux, FreeBSD 7.2 and CentOS 7.

Debian GNU/Linux specifics

Debian provides a libpam-pgsql package, but if you decide to build your own from sources, you will need packages libb64-dev, libgcrypt-dev, libpam0g-dev and libpq-dev packages, as well as gcc, autoconf, automake and libtool to compile.

FreeBSD

On FreeBSD you will have to install the postgresql/postgresql8*-client port.

CentOS

On CentOS you will have to install libgcrypt, postgresql-devel and libgcrypt-devel installed, as well as gcc-c++. Then autoreconf with:

$ autoreconf --install

once that's done, you can now ./configure, make and make install.

Programming Examples

See authenticate.c and chpass.c for an example application that authenticates and changes passwords using this module.

This version only works with PostgreSQL versions 7.4 or newer.

Configuration

PAM Configuration

Edit the /etc/pam.d/<service> for the service that will use PostgreSQL to authenticate, or /etc/pam.conf, adding the relevant lines.

For example:

auth        required    pam_pgsql.so 
account     required    pam_pgsql.so
password    required    pam_pgsql.so
session     required    pam_pgsql.so

Or:

password    required    pam_cracklib.so
password    required    pam_pgsql.so authtok

The following module-specific PAM directives can be used

• debug: Enable debugging output to syslog.

• config_file: Alternative location for configuration file.

• timeout: Optional wait, specified in seconds, before the module gives up while trying to connect to the database.

The following standard PAM directives can be used to adapt to any stack of authentication modules.

• echo_pass: Display password while its being typed.

• authtok: Make pam_pgsql provide the authentication token down the PAM stack so the next module can use it to continue authentication.

• use_first_pass: Require authtok from previous entry in PAM stack in order to use it as password. This is useful when pam_pgsql is being used after a PAM module that requests or manipulates the authentication token, e.g.

  	password   required  pam_cracklib.so 
  	password   required  pam_pgsql.so  use_first_pass
  

• try_first_pass: Same intent as authtok and use_first_pass, but doesn't fail if previous module doesn't provide a password.

Simple database configuration

If you have a single table holding user names, passwords, account expiration, and password renewal information, configuration only requires database connection credentials, table and column names.

For instance, if database sysdb holds a table accounts with columns user_name, user_password, expired and force_change, then configuration file /etc/pam_pgsql.conf should look like

    database = sysdb
    user = ljb
    password = secret
    table = accounts
    user_column = user_name
    pwd_column = user_password
    expired_column = expired
    newtok_column = force_change
    debug = 1

where user and password are the credentials needed in order to connect to the database. Columns expired_column and force_change are presumed boolean.

Note that for backwards compatibility with earlier versions, options specified in the configuration file can be supplied as module arguments as well. Module arguments will override the configuration file.

Simple configuration can use the following directives:

• database: Name of database to connect to.

• host: Host or IP address where database server is running on. If defined empty, connection will be attempted via local UNIX sockets.

• port: TCP port where database server is running on. If defined empty, connection will be attempted via local UNIX sockets.

• user: Username used to connect to database.

• password: Password for user to connect to database.

• table: Table holding authentication information.

• user_column: Column in table table holding the authentication user names.

• pwd_column: Column in table table holding the authentication passwords.

• expired_column: Column in table table indicating the account has expired. It could contain numbers (1), a single character (y), or be a boolean.

• newtok_column: Column in table table indicating the account must change its password. It could contain numbers (1), a single character (y), or be a boolean.

Flexible database configuration

From version 0.6 onwards there's a new flexible configuration style allowing multiple tables and queries to be used for extracting authentication information from the database. Note that using this new configuration style overrides legacy values, so they can't be mixed.

The simple configuration above could translate to

connect = dbname=sysdb user=ljb password=secret connect_timeout=15
auth_query = select user_password from accounts where user_name = %u
acct_query = select expired, force_change, (user_password IS NULL OR user_password = '') from accounts where user_name = %u
pwd_query = update accounts set user_password = %p where user_name = %u

Flexible configuration can use the following directives:

• connect: A database connection string as used by libpq. See https://www.postgresql.org/docs/current/static/libpq.html for details. This directive overrides other connection specific options.

• pw_type: Specifies the password encryption scheme that is stored in the database table. Can be one of:

  • clear: Password is stored in clear text.

  • md5: Password is stored as MD5 as hashed by libmhash.

  • crypt_md5: Password is stored as MD5 as hashed by crypt(), using a special salt.

  • md5_postgres: Password is stored using PostgreSQL's internal algorithm where hash is MD5 || MD5(password + login). This is useful to authenticate against PostgreSQL own users (those created via createuser and stored in table pg_catalog.pg_shadow).

  • function: Use a database function to generate the hash. For this to work, auth_query should be written such that it uses the provided password and return a single boolean value stating whether or not user has been authenticated.

  • salted_hash: Use the salted-hash mechanism as provided by Perl's Crypt::SaltedHash module. Given any of the supported hash algorithms and a salt, the password should be hashed as HASH(password+salt) || SALT, Base64 encoded, and prefixed with the hash name in front, e.g.

        {SSHA}f767n4vI5smFY1pUNnM0GYkd0mgYOCLj
    

    would be stored for password secret using SHA-1 as hashing algorithm. The module supports SSHA, SMD5, SSHA224, SSHA256, SSHA384 and SSHA512.

  • salt_size: Specify a custom salt size to be used with the salted_hash method. The default value is 4, which is the standard salt size for Crypt::SaltedHash as well as most salt-hash libraries.

• auth_query: A SQL query used for authentication. This query should return a single row with a single column holding the current password for the given user, as stored in the database tables.

• auth_succ_query: An optional SQL query to be executed after successful authentication.

• auth_fail_query: An optional SQL query to be executed after failed authentication.

• session_open_query: An optional SQL query to be executed on session start.

• session_close_query: An optional SQL query to be executed on session close.

• acct_query: A SQL query to obtain account options for a given username. The query should return 3 or 4 boolean columns: account expired, new password required, password is null, and possibly a PAM_PERM_DENIED column.

• pwd_query: A SQL query to perform password changing for a given username.

The queries should be written as a single long line. Queries can use special variables that will be substituted with information coming from the PAM authentication process:

• %u -- username.

• %p -- the password, either the one to use for authentication, or the new one while changing it.

• %h -- client hostname as specified by PAM subsystem. If %h is unavailable but used, system substitutes NULL in query, but does not fail. You can fail it manually by using %s IS NOT NULL somewhere in the query.

• %i -- client IP as per gethostbyname(%h). If gethostbyname fails then if rhost is empty or doesn't contain any periods ("."), %i is replaced with NULL. Otherwise pam_pgsql will return PAM_AUTH_ERR.

--

Ernesto Hernández-Novich <[email protected]> 2017-11-07

Luis Muñoz <[email protected]> 2017-11-07