[Nsspampgsql-devel] Bug#500890: libnss-pgsql2: Wrong SQL querys in nss-pgsql.conf

[Torsten Irländer]

Package: libnss-pgsql2
Version: 1.4.0debian-2
Severity: important
Tags: patch

Dear maintainers, 

I run into some problems with the nss-pgsql.conf file comming in this package
which does not work for me out of the box. 

After creating all needed tables and putting the exampe config in place doing
a "getnet group" results in the following errors in the postgres log:

2008-10-02 13:12:17 CEST FEHLER:  Spalte »username« existiert nicht bei Zeichen 105
2008-10-02 13:12:17 CEST ANWEISUNG:  DECLARE nss_pgsql_internal_allgroups_curs SCROLL CURSOR FOR SELECT groupname, passwd, gid, ARRAY(SELECT username FROM usergroups WHERE usergroups.gid = group_table.gid) AS members FROM group_table FOR READ ONLY
2008-10-02 13:12:17 CEST FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert
2008-10-02 13:12:17 CEST ANWEISUNG:  FETCH FROM nss_pgsql_internal_allgroups_curs

After tweaking the SQL querys a bit "getent passwd" and "getent groups" works
for me again. 

Patch for this is available

Regards, Torsten


-- System Information:
Debian Release: lenny/sid
  APT prefers testing
  APT policy: (500, 'testing')
Architecture: i386 (i686)

Kernel: Linux 2.6.26-1-686 (SMP w/1 CPU core)
Locale: LANG=de_DE.UTF-8, LC_CTYPE=de_DE.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/bash

Versions of packages libnss-pgsql2 depends on:
ii  libc6                         2.7-13     GNU C Library: Shared libraries
ii  libpq5                        8.3.3-1    PostgreSQL C client library

libnss-pgsql2 recommends no packages.

Versions of packages libnss-pgsql2 suggests:
ii  libpam-pgsql                  0.6.3-2    PAM module to authenticate using a
pn  nscd                          <none>     (no description available)

-- no debconf information
-------------- next part --------------
--- nss-pgsql.conf.org	2007-11-13 02:03:16.000000000 +0100
+++ nss-pgsql.conf	2008-10-02 13:14:57.000000000 +0200
 
@@ -11,9 +11,9 @@
 # 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, ARRAY(SELECT username FROM usergroups WHERE usergroups.gid = group_table.gid) AS members FROM group_table WHERE groupname = $1
+getgrnam        = SELECT groupname, passwd, gid, ARRAY(SELECT username FROM passwd_table WHERE passwd_table.gid = group_table.gid) AS members FROM group_table WHERE groupname = $1
 # Must return group_name, group_passwd, group_gid
-getgrgid        = SELECT groupname, passwd, gid, ARRAY(SELECT username FROM usergroups WHERE usergroups.gid = group_table.gid) AS members FROM group_table WHERE gid = $1
+getgrgid        = SELECT groupname, passwd, gid, ARRAY(SELECT username FROM passwd_table WHERE passwd_table.gid = group_table.gid) AS members 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, ARRAY(SELECT username FROM usergroups WHERE usergroups.gid = group_table.gid) AS members FROM group_table
+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 username FROM passwd_table pt JOIN usergroups ug ON ug.uid = pt.uid WHERE ug.gid = gt.gid ) AS members FROM group_table gt