Re: pgbouncer with ldap

On 23/9/19 9:10 π.μ., Ayub M wrote:
> Achilleas, I am struggling with libpam_ldap package install. Does its setup also involves sssd daemon with nss and pam? I am seeing many different ways of dealing with ldap with pam and different
> modules which I am not familiar with (nscld, sssd etc). If you have any steps documented for installing libpam_ldap kindly share with me.
>

Hello,
you have to just (for debian based) :
apt install libpam-ldap
did you do that? Did you correctly answered the conf questions? No additional packages needed IIRC. PAM has many modules, module for LDAP is one of them. Module for SSH is another. Read up a little
bit on the PAM architecture (an old but very successful SUN Microsystems technology) and things will be clearer.
Things to note :
There are two ldap conf files in your /etc :
1) /etc/ldap/ldap.conf : the classic ldap client conf file, used by e.g. ldapsearch
2) /etc/ldap.conf : the conf file of libpam-ldap

> <snip>
>
> On Wed, Sep 18, 2019 at 9:48 AM Ayub M <hiayub(at)gmail(dot)com <mailto:hiayub(at)gmail(dot)com>> wrote:
>
> Will do, thanks!
>
> On Wed, Sep 18, 2019, 4:55 AM Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)com>> wrote:
>
> Thanx I am really glad I could help! pgbouncer and LDAP is a PITA!
>
> It could be nice if you replied to the list thread just to have an update on how it goes.
>
>
> On 18/9/19 11:23 π.μ., Ayub M wrote:
>> Hi Achilleas, yes got the pam authentication working. I had to create pgbouncer file in pam directory. Now I am working on second part, which is integration of pam with ldap. Getting ldap
>> creds, will soon update you on how it goes.... thanks for your help. Without your article I would have given up on this option..
>>
>> On Wed, Sep 18, 2019, 1:53 AM Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)com>> wrote:
>>
>> Hello
>>
>> any luck?
>>
>> On 14/9/19 9:41 π.μ., Ayub M wrote:
>>> Yes I did set that, here is how pgbouncer looks like ---
>>>
>>> -rwsrwsr-x. 1 root root 2087504 Sep 13 00:45 pgbouncer
>>>
>>>
>>> On Fri, Sep 13, 2019 at 6:50 AM Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)com>> wrote:
>>>
>>> On 13/9/19 10:19 π.μ., Ayub M wrote:
>>>> Stumbled in the first step - PAM authentication via pgbouncer. After compiling pgbouncer with the pam plug-in, I am unable to login into the db - throws PAM error message. Please
>>>> help.
>>>>
>>>> User created with the same password as linux user --
>>>> localhost:~$ psql -h dbhost -p 3306 -U admin -W db1
>>>> db1=> create user testuser password 'hello123';
>>>> CREATE ROLE
>>>>
>>>> [ec2-user(at)ip-1(dot)1(dot)1(dot)1 <mailto:ec2-user(at)ip-1(dot)1(dot)1(dot)1> pam.d]$ psql -h localhost -p 5432 testdb -U testuser
>>>> Password for user testuser:
>>>> psql: ERROR:  auth failed
>>>
>>> ok, pgbouncer should be able to read /etc/pam* files.
>>> Did you miss the
>>> |# chown root:staff ~pgbouncer/pgbouncer-1.9.0/pgbouncer |
>>> |# chmod +s ~pgbouncer/pgbouncer-1.9.0/pgbouncer|
>>> part?
>>>
>>>>
>>>> Log entries - pgbouncer.log
>>>> 2019-09-13 06:51:47.180 UTC [5752] LOG C-0x1243020: testdb/testuser(at)[::1]:52408 login attempt: db=testdb user=testuser tls=no
>>>> 2019-09-13 06:51:47.180 UTC [5752] NOISE safe_send(12, 9) = 9
>>>> 2019-09-13 06:51:47.180 UTC [5752] NOISE resync(12): done=86, parse=86, recv=86
>>>> 2019-09-13 06:51:47.180 UTC [5752] NOISE resync(12): done=0, parse=0, recv=0
>>>> 2019-09-13 06:51:47.180 UTC [5752] NOISE safe_recv(12, 4096) = 14
>>>> 2019-09-13 06:51:47.180 UTC [5752] NOISE C-0x1243020: testdb/testuser(at)[::1]:52408 read pkt='p' len=14
>>>> 2019-09-13 06:51:47.180 UTC [5752] DEBUG C-0x1243020: testdb/testuser(at)[::1]:52408 pam_auth_begin(): pam_first_taken_slot=1, pam_first_free_slot=1
>>>> 2019-09-13 06:51:47.180 UTC [5752] DEBUG pam_auth_worker(): processing slot 1
>>>> 2019-09-13 06:51:47.180 UTC [5752] WARNING pam_authenticate() failed: Authentication failure
>>>> 2019-09-13 06:51:47.181 UTC [5752] DEBUG pam_auth_worker(): authorization completed, status=3
>>>> 2019-09-13 06:51:47.386 UTC [5752] LOG C-0x1243020: testdb/testuser(at)[::1]:52408 closing because: auth failed (age=0s)
>>>> 2019-09-13 06:51:47.386 UTC [5752] WARNING C-0x1243020: testdb/testuser(at)[::1]:52408 pooler error: auth failed
>>>>
>>>> Able to login as testuser
>>>> [ec2-user(at)ip-1(dot)1(dot)1(dot)1 <mailto:ec2-user(at)ip-1(dot)1(dot)1(dot)1> pam.d]$ su - testuser
>>>> Password:
>>>> Last login: Fri Sep 13 06:21:12 UTC 2019 on pts/1
>>>> [testuser(at)ip-1(dot)1(dot)1(dot)1 <mailto:testuser(at)ip-1(dot)1(dot)1(dot)1> ~]$ id
>>>> uid=1001(testuser) gid=1001(testuser) groups=1001(testuser) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
>>>>
>>>> The user was created as follows
>>>> [root(at)ip-1(dot)1(dot)1(dot)1 <mailto:root(at)ip-1(dot)1(dot)1(dot)1> ~]# adduser -p hello123 testuser
>>>> [root(at)ip-1(dot)1(dot)1(dot)1 <mailto:root(at)ip-1(dot)1(dot)1(dot)1> ~]# id testuser
>>>> uid=1001(testuser) gid=1001(testuser) groups=1001(testuser)
>>>>
>>>> Here is the pgbouncer.ini config
>>>> [ec2-user(at)ip-1(dot)1(dot)1(dot)1 <mailto:ec2-user(at)ip-1(dot)1(dot)1(dot)1> etc]$ less pgbouncer.ini | grep -v '^$' | grep -v '^;'
>>>> [databases]
>>>> testdb = host=dbhost port=3306 dbname=db1
>>>> [users]
>>>> [pgbouncer]
>>>> logfile = /var/log/pgbouncer/pgbouncer.log
>>>> pidfile = /var/run/pgbouncer/pgbouncer.pid
>>>> listen_addr = *
>>>> listen_port = 5432
>>>> auth_type = pam
>>>>
>>>> Am I missing something? Any permissions?
>>>>
>>>> On Thu, Sep 12, 2019 at 4:54 AM Ayub M <hiayub(at)gmail(dot)com <mailto:hiayub(at)gmail(dot)com>> wrote:
>>>>
>>>> Okay, thanks for the response. Unfortunately Aurora does not expose these files or I should say there is no concept of these files in AWS managed Aurora DB service. Anyway I
>>>> will give a try and let you know.
>>>>
>>>> On Thu, Sep 12, 2019 at 1:52 AM Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)com>> wrote:
>>>>
>>>> On 11/9/19 2:47 μ.μ., Ayub M wrote:
>>>>> Achilleas, for this setup to work are changes to postgresql.conf and pg_hba.conf needed? I am trying to implement this for AWS rds Aurora where these files are not
>>>>> accessible.
>>>>>
>>>> Those files are needed in any case if you work with postgresql. Unfortunately no experience with Aurora. He have been building from source for ages.
>>>>> On Mon, Sep 9, 2019, 6:46 AM Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)com>> wrote:
>>>>>
>>>>> On 9/9/19 12:41 μ.μ., Laurenz Albe wrote:
>>>>> > Christoph Moench-Tegeder wrote:
>>>>> >>> It has hba and via hba file one can specify ldap connections
>>>>> >>>
>>>>> >>> https://www.postgresql.org/docs/9.3/auth-pg-hba-conf.html
>>>>> >> https://pgbouncer.github.io/config.html#hba-file-format
>>>>> >> "Auth-method field: Only methods supported by PgBouncer’s auth_type
>>>>> >> are supported", and "ldap" is not supported.
>>>>> >> When there's no ldap support in pgbouncer, there's no ldap support
>>>>> >> in pgbouncer.
>>>>> > To throw in something less tautological:
>>>>> >
>>>>> > PgBouncer supports PAM authentication, so if you are on UNIX,
>>>>> > you could use PAM's LDAP module to do what you want.
>>>>> Right, I had written a blog about it :
>>>>> https://severalnines.com/database-blog/one-security-system-application-connection-pooling-and-postgresql-case-ldap
>>>>>
>>>>> However, I always wished (since my first endeavors with pgbouncer) it was less complicated.
>>>>> >
>>>>> > Yours,
>>>>> > Laurenz Albe
>>>>>
>>>>>
>>>>> --
>>>>> Achilleas Mantzios
>>>>> IT DEV Lead
>>>>> IT DEPT
>>>>> Dynacom Tankers Mgmt
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Achilleas Mantzios
>>>> IT DEV Lead
>>>> IT DEPT
>>>> Dynacom Tankers Mgmt
>>>>
>>>>
>>>>
>>>> --
>>>> Regards,
>>>> Ayub
>>>>
>>>>
>>>>
>>>> --
>>>> Regards,
>>>> Ayub
>>>
>>>
>>> --
>>> Achilleas Mantzios
>>> IT DEV Lead
>>> IT DEPT
>>> Dynacom Tankers Mgmt
>>>
>>>
>>>
>>> --
>>> Regards,
>>> Ayub
>>
>>
>> --
>> Achilleas Mantzios
>> IT DEV Lead
>> IT DEPT
>> Dynacom Tankers Mgmt
>>
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
>
> --
> Regards,
> Ayub

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt