pam-pgsql with sshd

Question

This is my configuration for pam_pgsql.conf

host = localhost
database = x
user = xx
password = xxx
table = userdb_user
user_column = username
pwd_column = password
pw_type = clear
debug = 1

This is my configuration for sshd

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

The query for the password is executed because I have this in /var/log/postgresql

2015-12-07 12:00:02 CET [5603-1] db LOG:  Ausführen <unnamed>: select password from userdb_user where username = $1
2015-12-07 12:00:02 CET [5603-2] db DETAIL:  Parameter: $1 = 'admin'

I'm always getting this in /var/log/auth.log

PAM_pgsql[5788]: couldn't authenticate user admin

I really don't know what is the problem anymore because the query for the password reaches the db level and gets executed as shown in the above pgsql log!!

Answer 1

The configuration you've specified states to check the column password of table userdb_user for a plain text match of the password - i.e. the field password will contain an unencrypted version of the password, and it will also verify the username.

Without knowing data, you'll have to follow the usual trial-and-error checks:

1. Check that username and password can log onto the database in question:

   psql -h localhost -U xx x

2. Check if the database exists, and the table exists, using the username and password you specified:

   select password from userdb_user where username='admin'

3. Check that the password entered matches the password returned from the userdb_user table - they're plain text matches.

There is also an ssh requirement is that there must be a passwd entry available for the user account - i.e. getent passwd admin must return some data.

You can use the libnss-pgsql module (also called sysauth-pgsql) to configure passwd and group entries for users.

In this case, you would need to have pgsql in the /etc/nsswitch.conf file on the passwd and group lines, so that they read something like:

passwd:         compat pgsql
group:          compat pgsql

That configures the name service switch to start looking to pgsql for account information - you'll have to restart sshd to have this change picked up.

Then you add a /etc/nss-pgsql.conf file which references the postgres database:

connectionstring        = hostaddr=127.0.0.1 dbname=x user=xx password=xxx connect_timeout=1  
getpwnam        = SELECT p.username, '*' AS passwd, p.username, p.homedir, p.shell, p.uid, p.gid FROM userdb_passwd p WHERE p.username = $1
getpwuid        = SELECT p.username, '*' AS passwd, p.username, p.homedir, p.shell, p.uid, p.gid FROM userdb_passwd p WHERE p.uid = $1
allusers        = SELECT p.username, '*' AS passwd, p.username, p.homedir, p.shell, p.uid, p.gid FROM userdb_passwd p
getgrnam        = SELECT g.groupname, 'x' AS passwd, g.gid, ARRAY(SELECT p.username FROM userdb_passwd p INNER JOIN userdb_user_group ug ON ug.uid=p.uid WHERE ug.gid = g.gid) AS members FROM userdb_groups g WHERE g.groupname = $1
getgrgid        = SELECT g.groupname, 'x' AS passwd, g.gid, ARRAY(SELECT p.username FROM userdb_passwd p INNER JOIN userdb_user_group ug ON ug.uid=p.uid WHERE ug.gid = g.gid) AS members FROM userdb_groups g WHERE g.gid = $1
allgroups       = SELECT g.groupname, 'x' AS passwd, g.gid, ARRAY(SELECT p.username FROM userdb_passwd p INNER JOIN userdb_user_group ug ON ug.uid=p.uid WHERE ug.gid = g.gid) AS members FROM userdb_groups g
getgroupmembersbygid    = SELECT p.username FROM userdb_passwd p INNER JOIN userdb_user_group ug ON ug.uid=p.uid WHERE ug.gid = $1
groups_dyn      = SELECT ug.gid FROM userdb_user_group ug INNER JOIN userdb_passwd p ON p.uid=ug.uid WHERE p.username = $1 AND $2 = $2

This requires three tables: userdb_passwd, userdb_groups, and userdb_user_group, of the following forms (this is taken from the dbschema.sql file of the project):

-- sequences, to deal with the userid and groupids
CREATE SEQUENCE group_id MINVALUE 10000 MAXVALUE 2147483647 NO CYCLE;
CREATE SEQUENCE user_id MINVALUE 10000 MAXVALUE 2147483647 NO CYCLE;

-- group table - all groups
CREATE TABLE "userdb_groups" (
    "gid" int4 NOT NULL DEFAULT nextval('group_id'),
    "groupname" character varying(16) NOT NULL,
    "descr" character varying,
    "passwd" character varying(20),
    PRIMARY KEY ("gid")
);

-- passwd entry tables - the passwd field is unused because of PAM against userdb_users
CREATE TABLE "userdb_passwd" (
    "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 UNIQUE INDEX passwd_table_uid ON userdb_passwd USING btree (uid);

CREATE TABLE "userdb_user_group" (
    "gid" int4 NOT NULL,
    "uid" int4 NOT NULL,
    PRIMARY KEY ("gid", "uid"),
    CONSTRAINT "ug_gid_fkey" FOREIGN KEY ("gid") REFERENCES "userdb_groups"("gid"),
    CONSTRAINT "ug_uid_fkey" FOREIGN KEY ("uid") REFERENCES "userdb_passwd"("uid")
);

Once you add the tables, you can load the users in question.

Without the user and group information, the user accounts would not be well specified for logging into the system and could introduce vulnerabilities.