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

[Stephen Gran]

severity 500890 normal
thanks
This one time, at band camp, Torsten Irländer said:
> 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:

> --- 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

You appear to be missing a table.  You need a total of three tables,
minimally, for this to work.  You need one table with user information 
(the passwd_table), one table with group information (the group_table),
and a map table (the usergroups table) to map user to group membership.

The queries are correct for that.  If the default sql does not create
the 3 tables, that's a problem that should be addressed.

Cheers,
-- 
 -----------------------------------------------------------------
|   ,''`.                                            Stephen Gran |
|  : :' :                                        sgran at debian.org |
|  `. `'                        Debian user, admin, and developer |
|    `-                                     http://www.debian.org |
 -----------------------------------------------------------------
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://lists.alioth.debian.org/pipermail/nsspampgsql-devel/attachments/20081002/5056f4b7/attachment.pgp