[pgpool-general: 6307] Re: [pgpool-hackers: 3140] Re: Example for CERT authentication with Pgpool-II

Muhammad Usama m.usama at gmail.com
Thu Nov 22 19:09:14 JST 2018


On Wed, Nov 21, 2018 at 1:14 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> Hi Usama,
>
>
> > Hi Ishii-San
> >
> > Thank you very much for testing and providing the log files. But
> apparently
> > all the configuration
> > files are as they should be and found no issues in those.
> >
> > There was only one thing in the example which was host dependent and that
> > was creation of SSL
> > certificates, So I have updated the example and moved the certificates
> > creation part to inside the
> > containers. Hopefully this should solve the problem.
> >
> > So can you please give it one more try with the latest version when ever
> > you get the free time.
> > Also you will notice that I have removed the build_all.sh script which is
> > no more required. and now
> > 'docker-compose build' and 'docker-compose run'
> > are enough to execute the example
>
> Now everything works great. Thanks!
> (BTW, not "docker-compose run", but "docker-compose up").
>
>
Great, and thanks for helping out in this and clarifying the mistake.



> Lessons learned here is, openssl command is very environment
> dependent:-)
>

yes lesson learnt the hard way, :-)

Kind Regards
Muhammad Usama


> >
> > Thanks
> > Best regards
> > Muhammad Usama
> >
> >
> >
> > On Tue, Nov 20, 2018 at 6:16 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> >
> >> Usama,
> >>
> >> F.Y.I. This is the output of "docker-compose up" by using your update
> >> to the git repository.
> >>
> >> $ docker-compose up
> >> Creating network "pgpool_cert_auth_default" with the default driver
> >> Creating network "pgpool_cert_auth_app_net" with driver "bridge"
> >> Creating pgsql-pgpool ... done
> >> Creating pgmaster     ... done
> >> Creating pgslave      ... done
> >> Creating pgpoolnode   ... done
> >> Creating clientnode   ... done
> >> Attaching to pgsql-pgpool, pgmaster, pgslave, pgpoolnode, clientnode
> >> pgsql-pgpool     | exiting
> >> pgslave          | + MASTER_IP=172.22.0.50
> >> pgslave          | + ROLE=standby
> >> pgslave          | + echo setting up server in standby role.
> >> pgslave          | + test -z standby
> >> pgpoolnode       | + IP=172.22.0.51
> >> pgpoolnode       | + PORT=5432
> >> pgpoolnode       | + echo checking for postgresql server at
> >> 172.22.0.51:5432.
> >> pgpoolnode       | + test -z 172.22.0.51
> >> pgslave          | setting up server in standby role.
> >> pgsql-pgpool exited with code 0
> >> pgslave          | + '[' standby = standby ']'
> >> pgslave          | + psql -h 172.22.0.50 -U postgres -c '\q'
> >> pgpoolnode       | + test -z 5432
> >> pgmaster         | + MASTER_IP=172.22.0.50
> >> pgmaster         | + ROLE=master
> >> clientnode       | + PGPOOL_IP=172.22.0.52
> >> clientnode       | + PGPOOL_PORT=9999
> >> clientnode       | + psql -h 172.22.0.52 -p 9999 -U postgres -c '\q'
> >> pgpoolnode       | checking for postgresql server at 172.22.0.51:5432.
> >> pgslave          | + echo 'mastar Postgres is up - executing basebackup
> >> command'
> >> pgslave          | + rm -rf /var/lib/pgsql/10/data
> >> pgpoolnode       | + psql -h 172.22.0.51 -p 5432 -U postgres -c '\q'
> >> pgslave          | mastar Postgres is up - executing basebackup command
> >> pgslave          | + sudo -u postgres pg_basebackup -RP -p 5432 -h
> >> 172.22.0.50 -D /var/lib/pgsql/10/data
> >> pgmaster         | + echo setting up server in master role.
> >> clientnode       | Pgpool-II is up and running
> >> clientnode       | + echo 'Pgpool-II is up and running'
> >> clientnode       | + sleep 5
> >> pgpoolnode       | + echo 'Postgres at 172.22.0.51:5432 is up and
> running'
> >> pgmaster         | + test -z master
> >> pgpoolnode       | Postgres at 172.22.0.51:5432 is up and running
> >> pgmaster         | setting up server in master role.
> >> pgmaster         | + '[' master = standby ']'
> >> 23215/23215 kB (100%), 1/1 tablespaceoint
> >> pgmaster         | Starting postgresql-10 service: [  OK  ]
> >> pgmaster         | Success. You can now start the database server using:
> >> pgmaster         |
> >> pgmaster         |     /usr/pgsql-10/bin/pg_ctl -D
> /var/lib/pgsql/10/data
> >> -l logfile start
> >> pgmaster         |
> >> pgmaster         | 2018-11-20 01:09:44.662 UTC [40] LOG:  listening on
> >> IPv4 address "0.0.0.0", port 5432
> >> pgmaster         | 2018-11-20 01:09:44.662 UTC [40] LOG:  listening on
> >> IPv6 address "::", port 5432
> >> pgmaster         | 2018-11-20 01:09:44.669 UTC [40] LOG:  listening on
> >> Unix socket "/var/run/postgresql/.s.PGSQL.5432"
> >> pgmaster         | 2018-11-20 01:09:44.677 UTC [40] LOG:  listening on
> >> Unix socket "/tmp/.s.PGSQL.5432"
> >> pgmaster         | 2018-11-20 01:09:44.695 UTC [40] LOG:  redirecting
> log
> >> output to logging collector process
> >> pgmaster         | 2018-11-20 01:09:44.695 UTC [40] HINT:  Future log
> >> output will appear in directory "log".
> >> pgmaster         | tail: unrecognized file system type 0x794c7630 for
> >> `/var/lib/pgsql/10/pgstartup.log'. Reverting to polling.
> >> pgslave          | Starting postgresql-10 service: [  OK  ]
> >> pgslave          | tail: unrecognized file system type 0x794c7630 for
> >> `/var/lib/pgsql/10/pgstartup.log'. Reverting to polling.
> >> pgslave          | Success. You can now start the database server using:
> >> pgslave          |
> >> pgslave          |     /usr/pgsql-10/bin/pg_ctl -D
> /var/lib/pgsql/10/data
> >> -l logfile start
> >> pgslave          |
> >> pgslave          | 2018-11-20 01:09:46.328 UTC [44] LOG:  listening on
> >> IPv4 address "0.0.0.0", port 5432
> >> pgslave          | 2018-11-20 01:09:46.329 UTC [44] LOG:  listening on
> >> IPv6 address "::", port 5432
> >> pgslave          | 2018-11-20 01:09:46.336 UTC [44] LOG:  listening on
> >> Unix socket "/var/run/postgresql/.s.PGSQL.5432"
> >> pgslave          | 2018-11-20 01:09:46.343 UTC [44] LOG:  listening on
> >> Unix socket "/tmp/.s.PGSQL.5432"
> >> pgslave          | 2018-11-20 01:09:46.354 UTC [44] LOG:  redirecting
> log
> >> output to logging collector process
> >> pgslave          | 2018-11-20 01:09:46.354 UTC [44] HINT:  Future log
> >> output will appear in directory "log".
> >> pgpoolnode       | Starting pgpool service: [  OK  ]
> >> pgpoolnode       | tail: unrecognized file system type 0x794c7630 for
> >> `/var/log/pgpool.log'. Reverting to polling.
> >> pgpoolnode       | 2018-11-20 01:09:46: pid 44: WARNING:  pool key file
> >> "/home/postgres/.pgpoolkey" has group or world access; permissions
> should
> >> be u=rw (0600) or less
> >> pgpoolnode       |
> >> pgpoolnode       | 2018-11-20 01:09:46: pid 44: LOG:  Backend status
> file
> >> /var/log/pgpool/pgpool_status does not exist
> >> pgpoolnode       | 2018-11-20 01:09:46: pid 44: LOG:  Setting up socket
> >> for 0.0.0.0:9999
> >> pgpoolnode       | 2018-11-20 01:09:46: pid 44: LOG:  Setting up socket
> >> for :::9999
> >> pgpoolnode       | 2018-11-20 01:09:46: pid 44: WARNING:  failed to open
> >> status file at: "/var/log/pgpool/pgpool_status"
> >> pgpoolnode       | 2018-11-20 01:09:46: pid 44: DETAIL:  "No such file
> or
> >> directory"
> >> pgpoolnode       | 2018-11-20 01:09:46: pid 44: LOG:  pgpool-II
> >> successfully started. version 4.0.1 (torokiboshi)
> >> pgpoolnode       | 2018-11-20 01:09:47: pid 75: WARNING:  failed to open
> >> status file at: "/var/log/pgpool/pgpool_status"
> >> pgpoolnode       | 2018-11-20 01:09:47: pid 75: DETAIL:  "No such file
> or
> >> directory"
> >> clientnode       | + psql -h 172.22.0.52 -p 9999 -U postgres -c 'SET
> >> password_encryption = '\''scram-sha-256'\''; CREATE ROLE scramuser
> PASSWORD
> >> '\''scram_password'\''; ALTER ROLE scramuser WITH LOGIN;' postgres
> >> clientnode       | ALTER ROLE
> >> clientnode       | + psql -h 172.22.0.52 -p 9999 -U postgres -c 'SET
> >> password_encryption = '\''scram-sha-256'\''; CREATE ROLE certuser
> PASSWORD
> >> '\''cert_password'\''; ALTER ROLE certuser WITH LOGIN;' postgres
> >> pgpoolnode       | 2018-11-20 01:09:52: pid 76: WARNING:  failed to open
> >> status file at: "/var/log/pgpool/pgpool_status"
> >> pgpoolnode       | 2018-11-20 01:09:52: pid 76: DETAIL:  "No such file
> or
> >> directory"
> >> clientnode       | ALTER ROLE
> >> clientnode       | + echo 'testing if ssl connection without proper
> client
> >> certificate is rejected'
> >> clientnode       | + sudo -u postgres psql 'sslmode=require port=9999
> >> host=172.22.0.52 dbname=postgres user=scramuser'
> >> clientnode       | testing if ssl connection without proper client
> >> certificate is rejected
> >> clientnode       | psql: server does not support SSL, but SSL was
> required
> >> clientnode       | + echo 'testing if ssl connection with proper client
> >> certificate works'
> >> clientnode       | + sudo -u postgres psql 'sslmode=require port=9999
> >> host=172.22.0.52 dbname=postgres user=certuser'
> >> clientnode       | testing if ssl connection with proper client
> >> certificate works
> >> clientnode       | psql: server does not support SSL, but SSL was
> required
> >> clientnode       | + tail -f /dev/null
> >> pgpoolnode       | 2018-11-20 01:09:52: pid 75: WARNING:  failed to open
> >> status file at: "/var/log/pgpool/pgpool_status"
> >> pgpoolnode       | 2018-11-20 01:09:52: pid 75: DETAIL:  "No such file
> or
> >> directory"
> >>
> >>
> >>
> >> > Sorry, 2.txt was empty. Attached again.
> >> >
> >> >>>> Usama,
> >> >>>>
> >> >>>> > Hi
> >> >>>> >
> >> >>>> > I have created a simple docker based example of using CERT
> >> authentication
> >> >>>> > with Pgpool-II frontend connections  for the reference.
> >> >>>> >
> >> >>>> > Please have a look and let me know what you think
> >> >>>> >
> >> >>>> > https://github.com/codeforall/pgpool_cert_auth
> >> >>>>
> >> >>>> Unfortunately it does not work for me.
> >> >>>>
> >> >>>> docker exec -it clientnode sudo -u postgres psql "sslmode=require
> >> >>>> port=9999 host=172.22.0.52 dbname=postgres user=certuser" -c "show
> >> >>>> pool_nodes"
> >> >>>> psql: server does not support SSL, but SSL was required
> >> >>>>
> >> >>>>
> >> >>> This is very strange, I have rebuild the dockers by pulling the
> fresh
> >> code
> >> >>> from repo and can run the test successfully.
> >> >>> Seems like setting of ssl configuration is failing.
> >> >>>
> >> >>> can you please help me identify the issue by sending the log of
> >> >>> "docker-compose up " and of the output of following commands
> >> >>
> >> >> Sure. Log attached.
> >> >>
> >> >>> docker exec -it pgmaster  /bin/bash -c 'cat $PGDATA/postgresql.conf'
> >> >>
> >> >> Attached (1.txt).
> >> >>
> >> >>> docker exec -it pgmaster  /bin/bash -c 'cd $PGDATA/log && cat "$(ls
> >> -1rt  |
> >> >>> tail -n1)"'
> >> >>
> >> >> Attached (2.txt).
> >> >>
> >> >>> docker exec -it pgslave  /bin/bash -c 'cat $PGDATA/postgresql.conf'
> >> >>
> >> >> Attached (3.txt).
> >> >>
> >> >>>
> >> >>> docker exec -it pgslave  /bin/bash -c 'cd $PGDATA/log && cat "$(ls
> >> -1rt  |
> >> >>> tail -n1)"'
> >> >>
> >> >> Attached (4.txt).
> >> >>
> >> >>> docker exec -it pgpoolnode  /bin/bash -c 'cat
> >> ${PGPOOLCONF}/pgpool.conf'
> >> >>
> >> >> Attached (5.txt).
> >> >>
> >> >>>> Also I noticed you do not use Pgpool-II RPMs provided by Pgpool-II
> >> >>>> community:
> >> >>>> https://pgpool.net/mediawiki/index.php/Yum_Repository
> >> >>>>
> >> >>>> Is there any reason for this?
> >> >>>>
> >> >>>> No reason as such, I just installed the Pgpool rpms from same repo
> >> from
> >> >>> where I was getting the PG server.
> >> >>> I have update the docker files to use the pgpool community rpms
> >> instead.
> >> >>>
> >> >>>
> >>
> https://github.com/codeforall/pgpool_cert_auth/commit/218f7536330677597552330199d0fd637f88d5b0
> >> >>>
> >> >>> Thanks
> >> >>> Best Regards
> >> >>> Muhammad Usama
> >> >>>
> >> >>>
> >> >>>
> >> >>>> Best regards,
> >> >>>> --
> >> >>>> Tatsuo Ishii
> >> >>>> SRA OSS, Inc. Japan
> >> >>>> English: http://www.sraoss.co.jp/index_en.php
> >> >>>> Japanese:http://www.sraoss.co.jp
> >> >>>>
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20181122/494008c6/attachment-0001.html>


More information about the pgpool-general mailing list