[pgpool-hackers: 3145] Re: Example for CERT authentication with Pgpool-II
Muhammad Usama
m.usama at gmail.com
Tue Nov 20 23:57:18 JST 2018
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
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-hackers/attachments/20181120/e5de056b/attachment.html>
More information about the pgpool-hackers
mailing list