pgbouncer with ldap

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

pgbouncer with ldap

Ayub M
Hello, I am trying to setup pgbouncer with ldap to connect with rds postgres. Downloaded latest version of pbbouncer (1.11) and using hba auth_type. Getting below error when starting pgbouncer daemon. Am I missing something?

[[hidden email] etc]$ pgbouncer -V
PgBouncer version 1.11.0

[[hidden email] etc]$ cat pg_hba.conf 
host all user1 0.0.0.0/0 md5
host all all 0.0.0.0/0 ldap ldapserver=ldap-server.com ldapprefix="cn=" ldapsuffix=", dc=group, dc=com"

[[hidden email] etc]$ cat pgbouncer.ini | grep -v '^$' | grep -v '^;' 
[databases]
db1 = host=xxx.rds.amazonaws.com port=5439 dbname=db1
[users]
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 5439
auth_type = hba
auth_file = /home/centos/pgbouncer-1.11.0/etc/userlist.txt
auth_hba_file = /home/centos/pgbouncer-1.11.0/etc/pg_hba.conf

[[hidden email] etc]$  pgbouncer -d pgbouncer.ini
2019-09-09 06:57:54.447 UTC [9465] WARNING hba line 2: unsupported method: buf=ldap
2019-09-09 06:57:54.448 UTC [9465] WARNING could not parse hba config line 2

Regards,
Ayub
Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

Christoph Moench-Tegeder
## Ayub M ([hidden email]):

> Hello, I am trying to setup pgbouncer with ldap to connect with rds
> postgres. Downloaded latest version of pbbouncer (1.11) and using hba
> auth_type. Getting below error when starting pgbouncer daemon. Am I
> missing something?

There's no "ldap" mentioned anywhere in the docs:
 https://pgbouncer.github.io/config.html
That could be a hint that ldap isn't supported...

Regards,
Christoph

--
Spare Space


Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

Ayub M
It has hba and via hba file one can specify ldap connections


On Mon, Sep 9, 2019, 3:40 AM Christoph Moench-Tegeder <[hidden email]> wrote:
## Ayub M ([hidden email]):

> Hello, I am trying to setup pgbouncer with ldap to connect with rds
> postgres. Downloaded latest version of pbbouncer (1.11) and using hba
> auth_type. Getting below error when starting pgbouncer daemon. Am I
> missing something?

There's no "ldap" mentioned anywhere in the docs:
 https://pgbouncer.github.io/config.html
That could be a hint that ldap isn't supported...

Regards,
Christoph

--
Spare Space
Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

Christoph Moench-Tegeder
## Ayub M ([hidden email]):

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

Regards,
Christoph

--
Spare Space


Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

Laurenz Albe
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.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

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



Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

Ayub M
Thank you Achilleas and Laurenz for the points, will try this out. 

On Mon, Sep 9, 2019, 6:46 AM Achilleas Mantzios <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

Ayub M
In reply to this post by Achilleas Mantzios
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. 

On Mon, Sep 9, 2019, 6:46 AM Achilleas Mantzios <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

Achilleas Mantzios
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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

Ayub M
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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

Ayub M
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@ip-1.1.1.1 pam.d]$ psql -h localhost -p 5432 testdb -U testuser
Password for user testuser: 
psql: ERROR:  auth failed

Log entries - pgbouncer.log
2019-09-13 06:51:47.180 UTC [5752] LOG C-0x1243020: testdb/testuser@[::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@[::1]:52408 read pkt='p' len=14
2019-09-13 06:51:47.180 UTC [5752] DEBUG C-0x1243020: testdb/testuser@[::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@[::1]:52408 closing because: auth failed (age=0s)
2019-09-13 06:51:47.386 UTC [5752] WARNING C-0x1243020: testdb/testuser@[::1]:52408 pooler error: auth failed

Able to login as testuser 
[ec2-user@ip-1.1.1.1 pam.d]$ su - testuser 
Password: 
Last login: Fri Sep 13 06:21:12 UTC 2019 on pts/1
[testuser@ip-1.1.1.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@ip-1.1.1.1 ~]# adduser -p hello123 testuser
[root@ip-1.1.1.1 ~]# id testuser
uid=1001(testuser) gid=1001(testuser) groups=1001(testuser)

Here is the pgbouncer.ini config
[ec2-user@ip-1.1.1.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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

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

[[hidden email] 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@[::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@[::1]:52408 read pkt='p' len=14
2019-09-13 06:51:47.180 UTC [5752] DEBUG C-0x1243020: testdb/testuser@[::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@[::1]:52408 closing because: auth failed (age=0s)
2019-09-13 06:51:47.386 UTC [5752] WARNING C-0x1243020: testdb/testuser@[::1]:52408 pooler error: auth failed

Able to login as testuser 
[[hidden email] pam.d]$ su - testuser 
Password: 
Last login: Fri Sep 13 06:21:12 UTC 2019 on pts/1
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
[[hidden email] ~]# adduser -p hello123 testuser
[[hidden email] ~]# id testuser
uid=1001(testuser) gid=1001(testuser) groups=1001(testuser)

Here is the pgbouncer.ini config
[[hidden email] 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

Ayub M
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 <[hidden email]> 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

[[hidden email] 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@[::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@[::1]:52408 read pkt='p' len=14
2019-09-13 06:51:47.180 UTC [5752] DEBUG C-0x1243020: testdb/testuser@[::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@[::1]:52408 closing because: auth failed (age=0s)
2019-09-13 06:51:47.386 UTC [5752] WARNING C-0x1243020: testdb/testuser@[::1]:52408 pooler error: auth failed

Able to login as testuser 
[[hidden email] pam.d]$ su - testuser 
Password: 
Last login: Fri Sep 13 06:21:12 UTC 2019 on pts/1
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
[[hidden email] ~]# adduser -p hello123 testuser
[[hidden email] ~]# id testuser
uid=1001(testuser) gid=1001(testuser) groups=1001(testuser)

Here is the pgbouncer.ini config
[[hidden email] 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: pgbouncer with ldap

Achilleas Mantzios
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 <[hidden email]> 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

[[hidden email] 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@[::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@[::1]:52408 read pkt='p' len=14
2019-09-13 06:51:47.180 UTC [5752] DEBUG C-0x1243020: testdb/testuser@[::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@[::1]:52408 closing because: auth failed (age=0s)
2019-09-13 06:51:47.386 UTC [5752] WARNING C-0x1243020: testdb/testuser@[::1]:52408 pooler error: auth failed

Able to login as testuser 
[[hidden email] pam.d]$ su - testuser 
Password: 
Last login: Fri Sep 13 06:21:12 UTC 2019 on pts/1
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
[[hidden email] ~]# adduser -p hello123 testuser
[[hidden email] ~]# id testuser
uid=1001(testuser) gid=1001(testuser) groups=1001(testuser)

Here is the pgbouncer.ini config
[[hidden email] 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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