Re: passwords and 7.3

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Nicolas Kowalski <Nicolas.Kowalski@imag.fr> writes:
>> And so on. Regular Unix users have their passwords set from the NIS
>> passwd database (standard crypt method), and PostgreSQL-specific users
>> have their passwords defined in pg_shadow (no encryption there). This
>> last use prevents us from using PAM-style authentication I presume.
>
> I don't see why.  You could write a PAM plugin to do whatever you want
> (that's the whole point of PAM, isn't it)?

Hm, you mean PostgreSQL asking PAM asking PostgreSQL for some non-UNIX
users passwords ? This looks like a loop, but yes, this is perhaps a
solution.

[Later...]

You are right, this works well. :-)


For those who are interested, I did the following on a Debian 3.0
GNU/Linux server running PostgreSQL 7.2.1 (any comment appreciated):

1) install pam_pgsql module,

2) create a view for extracting usefull PAM information from pg_shadow:

intranet=# \d pam_auth
           View "pam_auth"
    Column     |  Type   | Modifiers
---------------+---------+-----------
 user_name     | name    |
 user_password | text    |
 acc_expired   | boolean |
 acc_new_pwreq | boolean |
View definition: SELECT pg_shadow.usename AS user_name, pg_shadow.passwd AS user_password, 'f'::bool AS acc_expired,
'f'::boolAS acc_new_pwreq FROM pg_shadow; 


3) create a config file for pam_pgsql (/etc/pam_pgsql.conf):

database = intranet
user = postgres
table = pam_auth
user_column = user_name
pwd_column = user_password
expired_column = acc_expired
newtok_column = acc_new_pwreq
debug


4) create a postgresql service config file for pam (/etc/pam.d/postgresql):

auth        sufficient    pam_pgsql.so
auth        required      pam_unix.so
account     sufficient    pam_pgsql.so
account     required      pam_unix.so
password    sufficient    pam_pgsql.so
password    required      pam_unix.so



4) change the appropriate line in pg_hba.conf:

host         all         129.88.43.0   255.255.255.0       pam


5) set the server to do no encryption
(/etc/postgresql/postgresql.conf):

password_encryption = false


6) Enjoy.


--
Nicolas