Note

En vue d’une préparation d’un serveur web/mail
On part toujours d’une netinst, ici : Debian Jessie
La base de données conserve tous les mots de passe.

Installation

Installation minimal

vu que c’est une VM

=> sans lvm
=> juste un server ssh
=> 16GB sur une partition, on est large pour démarrer.

SSH :

Dans le fichier /etc/ssh/sshd_config

PermitRootLogin yes

interfaces :

allow-hotplug eth0
iface eth0 inet static
address 192.168.10.250
gateway 192.168.10.254
netmask 255.255.255.0

pre-up ip link set eth0 mtu 1400

bash completion :

apt-get install bash-completion 
nano ~/.bashrc
if [ -f /etc/profile.d/bash_completion.sh ]
then
        . /etc/profile.d/bash_completion.sh
fi

Postgres

Doc

Install de Postgres SQL : https://www.howtoforge.com/tutorial/ubuntu-postgresql-installation/

apt-get -y install postgresql
su - postgres
psql

On change le mot de passe postgres :

\password postgres

On créer une base de données unix :

CREATE DATABASE unix;

On quite

\q

On se connecte sur la nouvelle base unix :

psql unix

Schéma :

CREATE SEQUENCE group_id MINVALUE 1000 MAXVALUE 2147483647 NO CYCLE;
CREATE SEQUENCE user_id MINVALUE 1000 MAXVALUE 2147483647 NO CYCLE;

CREATE TABLE "group_table" (
       "gid" int NOT NULL DEFAULT nextval('group_id'),
       "groupname" character varying(32) NOT NULL UNIQUE,
       "descr" character varying,
       "passwd" character varying(1) default 'x',
       PRIMARY KEY ("gid")
);

CREATE TABLE "passwd_table" (
       "username" character varying(64) NOT NULL UNIQUE,
       "passwd" character varying(1) NOT NULL default 'x',
       "uid" int NOT NULL DEFAULT nextval('user_id'),
       "gid" int NOT NULL,
       "gecos" character varying(128),
       "homedir" character varying(256) NOT NULL,
       "shell" character varying DEFAULT '/bin/bash' NOT NULL,
       PRIMARY KEY ("uid")
);

CREATE TABLE "shadow_table" (
       "username" character varying(64) NOT NULL,
       "passwd" character varying(128) NOT NULL,
       "lastchange" TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
       "min" int2 NOT NULL default 0,
       "max" int4 NOT NULL default 99999,
       "warn" int2 NOT NULL default 7,
       "inact" int2 NOT NULL default -1,
       "expire" BOOLEAN NOT NULL DEFAULT False,
       "flag" int2 NOT NULL default 0,        
       "newtok" BOOLEAN NOT NULL DEFAULT False,
       PRIMARY KEY ("username","lastchange"),
       CONSTRAINT "st_username_fkey" FOREIGN KEY ("username") REFERENCES "passwd_table"("username") ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE "usergroups" (
       "gid" int NOT NULL,
       "uid" int NOT NULL,
       PRIMARY KEY ("gid", "uid"),
       CONSTRAINT "ug_gid_fkey" FOREIGN KEY ("gid") REFERENCES "group_table"("gid") ON DELETE CASCADE ON UPDATE NO ACTION,
       CONSTRAINT "ug_uid_fkey" FOREIGN KEY ("uid") REFERENCES "passwd_table"("uid") ON DELETE CASCADE ON UPDATE NO ACTION
);
-- Les droits sont à confirmer!
create user unixpam with password 'PASS';
grant insert on shadow_table to unixpam ;
grant select on shadow_table to unixpam ;

create user unixnss with password 'PASS';
grant select on passwd_table to unixnss ;
grant select on group_table to unixnss ;
grant select on usergroups to unixnss ;

create user unixnssroot with password 'PASS';
grant select on shadow_table to unixnssroot ;

INSERT INTO passwd_table(username,uid,gid,gecos,homedir) values ('webmaster',1000,1000,'webmaster,,,','/home/webmaster/');
INSERT INTO group_table(groupname,gid,passwd) values ('webmaster',1000,'x');
INSERT INTO usergroups (uid,gid)select uid,group_table.gid FROM group_table,passwd_table WHERE username='webmaster' AND (groupname='webmaster');

/etc/postgresql/9.4/main/pg_hba.conf :

On ajoute les lignes :

local unix unixpam trust
local unix unixnss trust
local unix unixnssroot trust

On relance le service

/etc/init.d/postgresql restart

PAM

apt-get install libpam-pgsql

/etc/pam_pgsql.conf :

host = 127.0.0.1
database = unix
user = unixpam
password = PASS

table = shadow_table
user_column = username
pwd_column = passwd
pw_type = md5_postgres
expired_column = expire
newtok_column = newtok
debug=1
pwd_query=INSERT INTO shadow_table(username,passwd) VALUES(%u,%p);
auth_query=SELECT passwd FROM shadow_table WHERE username=%u ORDER BY lastchange DESC LIMIT 1;
acct_query=SELECT expire, newtok, (passwd IS NULL OR passwd = '') FROM shadow_table WHERE username = %u ORDER BY lastchange DESC LIMIT 1;

/etc/pam.d/common-* :

/etc/pam.d/common-account

account	[success=2 new_authtok_reqd=done default=ignore]	pam_unix.so 
account	[success=1 new_authtok_reqd=done default=ignore]	pam_pgsql.so
account	requisite			pam_deny.so
account	required			pam_permit.so

/etc/pam.d/common-auth

auth		[success=2 default=ignore]	pam_pgsql.so
auth		[success=1 default=ignore]	pam_unix.so nullok_secure use_first_pass
auth		requisite			pam_deny.so
auth		required			pam_permit.so

/etc/pam.d/common-password

password	[success=2 default=ignore]	pam_unix.so obscure sha512
password	[success=1 default=ignore]	pam_pgsql.so
password	requisite			pam_deny.so
password	required			pam_permit.so

/etc/pam.d/common-session

session	required			pam_mkhomedir.so skel=/etc/skel/ umask=0022
session	[default=1]			pam_permit.so
session	requisite			pam_deny.so
session	[ success=ok default=ignore ]	pam_pgsql.so
session	required			pam_unix.so

/etc/pam.d/common-session

session	required			pam_mkhomedir.so skel=/etc/skel/ umask=0022
session	[default=1]			pam_permit.so
session	requisite			pam_deny.so
session	[ success=ok default=ignore ]	pam_pgsql.so
session	required			pam_unix.so

nss

apt-get install libnss-pgsql2 nscd

sans nscd, plantage au login

/etc/nsswitch.conf

passwd:         compat pgsql
group:          compat pgsql
shadow:         compat pgsql
gshadow:        files

hosts:          files dns
networks:       files

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

netgroup:       nis

/etc/nss-pgsql.conf

connectionstring        = hostaddr=127.0.0.1 dbname=unix user=unixnss password=PASS 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, ARRAY(SELECT username FROM usergroups,passwd_table WHERE usergroups.gid = group_table.gid AND passwd_table.uid = usergroups.uid) AS members FROM group_table WHERE groupname = $1
getgrgid                = SELECT groupname, passwd, gid, ARRAY(SELECT username FROM usergroups,passwd_table WHERE usergroups.gid = group_table.gid AND passwd_table.uid = usergroups.uid) AS members FROM group_table WHERE gid = $1
groups_dyn              = SELECT usergroups.gid FROM passwd_table JOIN usergroups USING (uid) where username = $1 and usergroups.gid <> $2
allgroups               = SELECT groupname, passwd, gid, ARRAY(SELECT username FROM usergroups,passwd_table WHERE usergroups.gid = group_table.gid AND passwd_table.uid = usergroups.uid) AS members FROM group_table

/etc/nss-pgsql-root.conf

shadowconnectionstring = hostaddr=127.0.0.1 dbname=unix user=unixnssroot password=PASS connect_timeout=1
shadowbyname = SELECT username, passwd, date_part('day',lastchange - '01/01/1970'), min, max, warn, inact, expire, flag FROM shadow_table WHERE username = $1 ORDER BY lastchange DESC LIMIT 1;
shadow = SELECT username, passwd, date_part('day',lastchange - '01/01/1970'), min, max, warn, inact, expire, flag FROM shadow_table WHERE (username,lastchange) IN (SELECT username, MAX(lastchange) FROM shadow_table GROUP BY username);

Les droits à fixer :

chown root:root /etc/nss-pgsql.conf /etc/nss-pgsql-root.conf
chmod 644 /etc/nss-pgsql.conf
chmod 600 /etc/nss-pgsql-root.conf
chmod 600 /etc/pam_pgsql.conf

TEST

Tout en étant root, on entre un nouveau mot de passe pour webmaster :

passwd webmaster

On peut vérifier que dans la base de donnée, un nouveau mot de passe est crée :

su postgres
psql unix
SELECT * FROM shadow_table;

Maintenant, on peux se connecter avec notre nouvel utilisateur par ssh/console/etc…
Fichier de log à surveiller :

tail -f /var/log/auth.log
tail -f /var/log/postgresql/postgresql-9.4-main.log

commande permettant de s’assurer du bon fonctionnement de PAM/Postgres

passwd -Sa
getent passwd
getent group

Sources :

http://www.ixany.org/docs/NSS-PAM_MySQL_Config_Client.html

Linux users and groups in PostgreSQL database

Jbsky

Authentification PAM/Postgres