Re: pgbouncer with ldap

Please dont top-post.

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

If you had set the same password in the postgresql server for the user and in the pgbouncer local unix user it should work.
What are the contents of your /etc/pam.d files?
How do /etc/pam.d/other /etc/pam.d/common-auth /etc/pam.d/common-account look like?
How about data/pg_hba.conf ?

Also try to do your tests by tail -f :
* the pgbouncer log
* linux auth.log or equivalent
* the pgsql log
and watch them for every enter you press.

You might have to tweak data/pg_hba.conf as well in order to look for md5 passwords for this user from the pgbouncer machine.

> 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