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@ip-1.1.1.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@[::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 <hia...@gmail.com
<mailto:hia...@gmail.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 <ach...@matrix.gatewaynet.com
<mailto:ach...@matrix.gatewaynet.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 <ach...@matrix.gatewaynet.com
<mailto:ach...@matrix.gatewaynet.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
