libnss-pgsql2 - getent group fails

Question

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:

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.

Answer 1

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.