0

i have set up pam and nss on a linux server (ubuntu 16.04) to get users and groups from a remote postgresql database. Login on the server is fine, but groups can not be loaded out of the database.

If entries exist in the table group_table, getent group fails with the message 'column number 3 is out of range 0..2'. All sqls used in the nss-pgsql.conf seems fine.

i use the following db-tables:

table passwd

usergroups

group_table

Content of nss-pgsql.conf:

connectionstring        = hostaddr=123.456.789.10 dbname=nobodyexpects user=the password=spanishinquisition connect_timeout=1
getgroupmembersbygid    = SELECT username FROM passwd_table WHERE gid = $1
getpwnam        = SELECT username, passwd, gecos, homedir, shell, uid, gid FROM passwd_table WHERE username = $1
getpwuid        = SELECT username, passwd, gecos, homedir, shell, uid, gid FROM passwd_table WHERE uid = $1
allusers        = SELECT username, passwd, gecos, homedir, shell, uid, gid FROM passwd_table
getgrnam        = SELECT groupname, passwd, gid FROM group_table WHERE groupname = $1
getgrgid        = SELECT groupname, passwd, gid FROM group_table WHERE gid = $1
groups_dyn      = SELECT ug.gid FROM passwd_table JOIN usergroups ug USING (uid) where username = $1 and ug.gid <> $2
allgroups       = SELECT groupname, passwd, gid  FROM group_table

If the table group_table contains no data, i get the message that the name of the group cannot be found, and getent group works with local groups. Hope someone can help.

  • Looks like getent is looking for an additional column to be returned from the query? username would constitute 0, passwd 1 and gid 2. – Raman Sailopal Jan 29 '18 at 13:41
  • Yes @RamanSailopal. Found, that the group-sqls needs an additional column containing the list of usernames. thank you. – rnick Jan 29 '18 at 14:25
2

Found, that the group-SQL need an additional list of the usernames. Changing the SQLs to

getgrnam        = SELECT g.groupname, 'x' AS passwd, g.gid, ARRAY(SELECT p.username FROM passwd_table p INNER JOIN usergroups ug ON ug.uid=p.uid WHERE ug.gid = g.gid) AS members FROM group_table g WHERE g.groupname = $1
getgrgid        = SELECT g.groupname, 'x' AS passwd, g.gid, ARRAY(SELECT p.username FROM passwd_table p INNER JOIN usergroups ug ON ug.uid=p.uid WHERE ug.gid = g.gid) AS members FROM group_table g WHERE g.gid = $1
groups_dyn      = SELECT ug.gid FROM passwd_table JOIN usergroups ug USING (uid) where username = $1 and ug.gid <> $2
allgroups       = SELECT groupname, passwd, gid, ARRAY(SELECT p.username FROM passwd_table p INNER JOIN usergroups ug ON ug.uid=p.uid WHERE ug.gid = g.gid) AS members  FROM group_table

resolves the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.