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 < ach...@matrix.gatewaynet.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@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> 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> 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> 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