[pgpool-hackers: 3147] Re: Example for CERT authentication with Pgpool-II

Tatsuo Ishii ishii at sraoss.co.jp
Wed Nov 21 17:14:08 JST 2018


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

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

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


More information about the pgpool-hackers mailing list