[Pkg-postgresql-public] libpam-pgsql and libnss-pgsql2 do not work as expected...

PMC OS pmcos-support at freenet.de
Tue Aug 24 23:36:53 UTC 2010


Hello experts,

I am new to Linux, PostgreSQL and Courier and have setup a small netwok with 6 Computers runing Debian Lenny:

1) 2 Workstations
2) 2 Intranet Servers
3) PostgreSQL Server
4) Router

The two intranet servers are now working with NFSv4/TCP, apache2 with suphp and php5, courier-imap/mta/mlm

My router is working to and use IPT, bind9 and apache2 with mod_proxy forwarding traffic to the two intranet servers.

Also the workstations are working fine.

First I had a problem connection to the PostgreSQL server over the network, but no it is working.

OK, I have  "system" and imported a modified the scheme

----( system_scheme.1.sql )----
-- 

 "system" WITH TEMPLATE = template0 ENCODING = 'UTF8';

 "system" OWNER TO 'system';

-- Connect to database

\connect "system";

-- Create two sequences for gid and uid

CREATE SEQUENCE g_id
        START WITH 10001
        INCREMENT BY 1
        MINVALUE 1000
        MAXVALUE 2147483647
        NO CYCLE;

CREATE SEQUENCE u_id
        START WITH 10001
        INCREMENT BY 1
        MINVALUE 10000
        MAXVALUE 2147483647
        NO CYCLE;

-- s

 "system_group" (
        "gid"           integer NOT NULL DEFAULT nextval('g_id'::regclass),
        "groupname"     character varying(16) NOT NULL,
        "descr"         character varying,
        "passwd"        character varying(64),
        PRIMARY KEY ("gid")
);
 "system_group" OWNER TO 'system';

 "system_passwd" (
        "username"      character varying(32) NOT NULL,
        "passwd"        character varying(64) NOT NULL,
        "uid"           integer NOT NULL DEFAULT nextval('uid'::regclass),
        "gid"           integer NOT NULL,
        "gecos"         character varying(128),
        "homedir"       character varying(128) NOT NULL,
        "shell"         character varying DEFAULT '/bin/bash' NOT NULL,
        PRIMARY KEY ("uid")
);
 "system_passwd" OWNER TO 'system';

 "system_groups" (
        "gid"           integer NOT NULL,
        "uid"           integer NOT NULL,
        PRIMARY KEY ("gid", "uid"),
        CONSTRAINT "ug_gid_fkey" FOREIGN KEY ("gid") REFERENCES "groups"("gid"),
        CONSTRAINT "ug_uid_fkey" FOREIGN KEY ("uid") REFERENCES "accounts"("uid")
);
 "system_groups" OWNER TO 'system';

 "system_shadow" (
        "username"      character varying(32) NOT NULL,
        "passwd"        character varying(64) NOT NULL,
        "lastchange"    integer NOT NULL,
        "min"           integer NOT NULL,
        "max"           integer NOT NULL,
        "warn"          integer NOT NULL,
        "inact"         integer NOT NULL,
        "expire"        integer NOT NULL,
        "flag"          integer NOT NULL,
        PRIMARY KEY ("username")
);
 "system_shadow" OWNER TO 'system';

 "system_server" (
        "uid"           integer NOT NULL,
        "server"        character varying(64) DEFAULT 'intranet.example.com' NOT NULL,
        PRIMARY KEY ("uid")
);
 "system_server" OWNER TO 'system';

 "system_quota" (
        "uid"           integer NOT NULL,
        "quota"         integer DEFAULT '131072' NOT NULL,
        PRIMARY KEY ("uid")
);
 "system_quota" OWNER TO 'system';

 "system_auxoptions_courier" (
        "uid"           integer NOT NULL,
        "auxoptions"    character varying(255),
        PRIMARY KEY ("uid")
);
 "system_auxoptions_courier" OWNER TO 'system';
-------------------------------

and created following files:

----( /etc/nss-pgsql.conf )----
connectionstring        = hostaddr=pgsql.example.com dbname=system user=system password=whoops connect_timeout=1

# you can use anything postgres accepts as table expression

# Must return "usernames", 1 column, list
getgroupmembersbygid    =  system_passwd WHERE gid = $1

# Must return passwd_name, passwd_passwd, passwd_gecos, passwd_dir, passwd_shell, passwd_uid, passwd_gid
getpwnam                =  system_passwd WHERE username = $1

# Must return passwd_name, passwd_passwd, passwd_gecos, passwd_dir, passwd_shell, passwd_uid, passwd_gid
getpwuid                =  system_passwd WHERE uid = $1

# All users
allusers                =  system_passwd

# Must return group_name, group_passwd, group_gid
getgrnam                =  system_group WHERE groupname = $1

# Must return group_name, group_passwd, group_gid
getgrgid                =  system_group WHERE gid = $1

# Must return gid.  %s MUST appear first for username match in where clause
groups_dyn              =  system_passwd JOIN system_groups USING (uid) where username = $1 and ug.gid <> $2
allgroups               =  system_group
------------

----( /etc/nss-pgsql-root.conf )----
# example configfile for PostgreSQL NSS module
# this file must be readable for root only

connectionstring        = hostaddr=pgsql.example.com dbname=system user=system
connect_timeout         = 1

#Query in the following format
#shadow_name, shadow_passwd, shadow_lstchg, shadow_min, shadow_max, shadow_warn, shadow_inact, shadow_expire, shadow_flag
shadowbyname            =  system_shadow WHERE username = $1
shadow                  =  system_shadow
------------

and modified the

----( /etc/nsswitch.conf )----
# /etc/nsswitch.conf
#
# Example configuration of GNU Name Service Switch functionality.
# If you have the `glibc-doc' and `info' packages installed, try:
# `info libc "Name Service Switch"' for information about this file.
#
# extended with example entries using the nss-pgsql module.

passwd:         files [SUCCESS=continue] pgsql compat
group:          files [SUCCESS=continue] pgsql compat
shadow:         files [SUCCESS=continue] pgsql compat

hosts:          files dns
networks:       files

protocols:      db files
services:       db files
ethers:         db files
rpc:            db files

netgroup:       nis
------------

----( /etc/pam_pgsql.conf )----
host            = pgsql.example.com
port            = 5032
database        = system
user            = system
password        = whoops
table           = system
user_column     = username
pwd_column      = passwd
expired_column  = expired
newtok_column   = new_pwreq
pw_type         = md5
timeout         = 3
debug

# connect = hostaddre=pgsql.example.com dbname=system user=system password=whoops connect_timeout=3
# auth_query =  system_passwd WHERE username = %u
# acct_query = SELECT (expired = 'y' OR expired = '1'), (new_pwreq = 'y' OR new_pwreq = '1'), (password IS
# pwd_query =  passwd = %p WHERE username = %u

# There are also additional flags you can use:
#     authtok           - see "use_first_pass"
#     use_first_pass    - require authtok from previous entry in PAM stack
#                         (useful for "password   required  pam_pgsql.so authtok")
#                         after "password required pam_cracklib.so ..."
#     try_first_pass    - same as previous, but doesn't fail if previous
#                         module failed to provide us with password
#     echo_pass                 - displays password while being typed
------------

and of course

----( /etc/pam.d/common-auth )----
auth        required    pam_pgsql.so
------------

----( /etc/pam.d/common-account )----
account     required    pam_pgsql.so
------------

----( /etc/pam.d/common-password )----
password    required    pam_pgsql.so
------------

leafe a "root terminal" ope if something goes wrong, restarted services...

. ..and kicked my self in the ass!

Can someone of you experts tell me what I have done wrong?

Oh and for the two things:
    expired_column  = expired
    newtok_column   = new_pwreq

How can I transform this into a query which requests its data from the table system_shadow?

Thanks
PMCOS




Exklusiv: Neue E-Mail-Adresse @iPhone.de jetzt verfügbar!
Sichern Sie sich jetzt ihre persönliche http://www.iphone.de/iphonemail/index.html?pid=10111947021



More information about the Pkg-postgresql-public mailing list