<div dir="ltr"><br><br><div class="gmail_quote"><div dir="ltr">On Wed, Nov 21, 2018 at 1:14 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi Usama,<br>
<br>
<br>
> Hi Ishii-San<br>
> <br>
> Thank you very much for testing and providing the log files. But apparently<br>
> all the configuration<br>
> files are as they should be and found no issues in those.<br>
> <br>
> There was only one thing in the example which was host dependent and that<br>
> was creation of SSL<br>
> certificates, So I have updated the example and moved the certificates<br>
> creation part to inside the<br>
> containers. Hopefully this should solve the problem.<br>
> <br>
> So can you please give it one more try with the latest version when ever<br>
> you get the free time.<br>
> Also you will notice that I have removed the build_all.sh script which is<br>
> no more required. and now<br>
> 'docker-compose build' and 'docker-compose run'<br>
> are enough to execute the example<br>
<br>
Now everything works great. Thanks!<br>
(BTW, not "docker-compose run", but "docker-compose up").<br>
<br></blockquote><div><br></div><div>Great, and thanks for helping out in this and clarifying the mistake.</div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Lessons learned here is, openssl command is very environment<br>
dependent:-)<br></blockquote><div><br></div><div>yes lesson learnt the hard way, :-)</div><div><br></div><div>Kind Regards</div><div>Muhammad Usama</div><div><br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
> <br>
> Thanks<br>
> Best regards<br>
> Muhammad Usama<br>
> <br>
> <br>
> <br>
> On Tue, Nov 20, 2018 at 6:16 AM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
> <br>
>> Usama,<br>
>><br>
>> F.Y.I. This is the output of "docker-compose up" by using your update<br>
>> to the git repository.<br>
>><br>
>> $ docker-compose up<br>
>> Creating network "pgpool_cert_auth_default" with the default driver<br>
>> Creating network "pgpool_cert_auth_app_net" with driver "bridge"<br>
>> Creating pgsql-pgpool ... done<br>
>> Creating pgmaster ... done<br>
>> Creating pgslave ... done<br>
>> Creating pgpoolnode ... done<br>
>> Creating clientnode ... done<br>
>> Attaching to pgsql-pgpool, pgmaster, pgslave, pgpoolnode, clientnode<br>
>> pgsql-pgpool | exiting<br>
>> pgslave | + MASTER_IP=172.22.0.50<br>
>> pgslave | + ROLE=standby<br>
>> pgslave | + echo setting up server in standby role.<br>
>> pgslave | + test -z standby<br>
>> pgpoolnode | + IP=172.22.0.51<br>
>> pgpoolnode | + PORT=5432<br>
>> pgpoolnode | + echo checking for postgresql server at<br>
>> <a href="http://172.22.0.51:5432" rel="noreferrer" target="_blank">172.22.0.51:5432</a>.<br>
>> pgpoolnode | + test -z 172.22.0.51<br>
>> pgslave | setting up server in standby role.<br>
>> pgsql-pgpool exited with code 0<br>
>> pgslave | + '[' standby = standby ']'<br>
>> pgslave | + psql -h 172.22.0.50 -U postgres -c '\q'<br>
>> pgpoolnode | + test -z 5432<br>
>> pgmaster | + MASTER_IP=172.22.0.50<br>
>> pgmaster | + ROLE=master<br>
>> clientnode | + PGPOOL_IP=172.22.0.52<br>
>> clientnode | + PGPOOL_PORT=9999<br>
>> clientnode | + psql -h 172.22.0.52 -p 9999 -U postgres -c '\q'<br>
>> pgpoolnode | checking for postgresql server at <a href="http://172.22.0.51:5432" rel="noreferrer" target="_blank">172.22.0.51:5432</a>.<br>
>> pgslave | + echo 'mastar Postgres is up - executing basebackup<br>
>> command'<br>
>> pgslave | + rm -rf /var/lib/pgsql/10/data<br>
>> pgpoolnode | + psql -h 172.22.0.51 -p 5432 -U postgres -c '\q'<br>
>> pgslave | mastar Postgres is up - executing basebackup command<br>
>> pgslave | + sudo -u postgres pg_basebackup -RP -p 5432 -h<br>
>> 172.22.0.50 -D /var/lib/pgsql/10/data<br>
>> pgmaster | + echo setting up server in master role.<br>
>> clientnode | Pgpool-II is up and running<br>
>> clientnode | + echo 'Pgpool-II is up and running'<br>
>> clientnode | + sleep 5<br>
>> pgpoolnode | + echo 'Postgres at <a href="http://172.22.0.51:5432" rel="noreferrer" target="_blank">172.22.0.51:5432</a> is up and running'<br>
>> pgmaster | + test -z master<br>
>> pgpoolnode | Postgres at <a href="http://172.22.0.51:5432" rel="noreferrer" target="_blank">172.22.0.51:5432</a> is up and running<br>
>> pgmaster | setting up server in master role.<br>
>> pgmaster | + '[' master = standby ']'<br>
>> 23215/23215 kB (100%), 1/1 tablespaceoint<br>
>> pgmaster | Starting postgresql-10 service: [ OK ]<br>
>> pgmaster | Success. You can now start the database server using:<br>
>> pgmaster |<br>
>> pgmaster | /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data<br>
>> -l logfile start<br>
>> pgmaster |<br>
>> pgmaster | 2018-11-20 01:09:44.662 UTC [40] LOG: listening on<br>
>> IPv4 address "0.0.0.0", port 5432<br>
>> pgmaster | 2018-11-20 01:09:44.662 UTC [40] LOG: listening on<br>
>> IPv6 address "::", port 5432<br>
>> pgmaster | 2018-11-20 01:09:44.669 UTC [40] LOG: listening on<br>
>> Unix socket "/var/run/postgresql/.s.PGSQL.5432"<br>
>> pgmaster | 2018-11-20 01:09:44.677 UTC [40] LOG: listening on<br>
>> Unix socket "/tmp/.s.PGSQL.5432"<br>
>> pgmaster | 2018-11-20 01:09:44.695 UTC [40] LOG: redirecting log<br>
>> output to logging collector process<br>
>> pgmaster | 2018-11-20 01:09:44.695 UTC [40] HINT: Future log<br>
>> output will appear in directory "log".<br>
>> pgmaster | tail: unrecognized file system type 0x794c7630 for<br>
>> `/var/lib/pgsql/10/pgstartup.log'. Reverting to polling.<br>
>> pgslave | Starting postgresql-10 service: [ OK ]<br>
>> pgslave | tail: unrecognized file system type 0x794c7630 for<br>
>> `/var/lib/pgsql/10/pgstartup.log'. Reverting to polling.<br>
>> pgslave | Success. You can now start the database server using:<br>
>> pgslave |<br>
>> pgslave | /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data<br>
>> -l logfile start<br>
>> pgslave |<br>
>> pgslave | 2018-11-20 01:09:46.328 UTC [44] LOG: listening on<br>
>> IPv4 address "0.0.0.0", port 5432<br>
>> pgslave | 2018-11-20 01:09:46.329 UTC [44] LOG: listening on<br>
>> IPv6 address "::", port 5432<br>
>> pgslave | 2018-11-20 01:09:46.336 UTC [44] LOG: listening on<br>
>> Unix socket "/var/run/postgresql/.s.PGSQL.5432"<br>
>> pgslave | 2018-11-20 01:09:46.343 UTC [44] LOG: listening on<br>
>> Unix socket "/tmp/.s.PGSQL.5432"<br>
>> pgslave | 2018-11-20 01:09:46.354 UTC [44] LOG: redirecting log<br>
>> output to logging collector process<br>
>> pgslave | 2018-11-20 01:09:46.354 UTC [44] HINT: Future log<br>
>> output will appear in directory "log".<br>
>> pgpoolnode | Starting pgpool service: [ OK ]<br>
>> pgpoolnode | tail: unrecognized file system type 0x794c7630 for<br>
>> `/var/log/pgpool.log'. Reverting to polling.<br>
>> pgpoolnode | 2018-11-20 01:09:46: pid 44: WARNING: pool key file<br>
>> "/home/postgres/.pgpoolkey" has group or world access; permissions should<br>
>> be u=rw (0600) or less<br>
>> pgpoolnode |<br>
>> pgpoolnode | 2018-11-20 01:09:46: pid 44: LOG: Backend status file<br>
>> /var/log/pgpool/pgpool_status does not exist<br>
>> pgpoolnode | 2018-11-20 01:09:46: pid 44: LOG: Setting up socket<br>
>> for <a href="http://0.0.0.0:9999" rel="noreferrer" target="_blank">0.0.0.0:9999</a><br>
>> pgpoolnode | 2018-11-20 01:09:46: pid 44: LOG: Setting up socket<br>
>> for :::9999<br>
>> pgpoolnode | 2018-11-20 01:09:46: pid 44: WARNING: failed to open<br>
>> status file at: "/var/log/pgpool/pgpool_status"<br>
>> pgpoolnode | 2018-11-20 01:09:46: pid 44: DETAIL: "No such file or<br>
>> directory"<br>
>> pgpoolnode | 2018-11-20 01:09:46: pid 44: LOG: pgpool-II<br>
>> successfully started. version 4.0.1 (torokiboshi)<br>
>> pgpoolnode | 2018-11-20 01:09:47: pid 75: WARNING: failed to open<br>
>> status file at: "/var/log/pgpool/pgpool_status"<br>
>> pgpoolnode | 2018-11-20 01:09:47: pid 75: DETAIL: "No such file or<br>
>> directory"<br>
>> clientnode | + psql -h 172.22.0.52 -p 9999 -U postgres -c 'SET<br>
>> password_encryption = '\''scram-sha-256'\''; CREATE ROLE scramuser PASSWORD<br>
>> '\''scram_password'\''; ALTER ROLE scramuser WITH LOGIN;' postgres<br>
>> clientnode | ALTER ROLE<br>
>> clientnode | + psql -h 172.22.0.52 -p 9999 -U postgres -c 'SET<br>
>> password_encryption = '\''scram-sha-256'\''; CREATE ROLE certuser PASSWORD<br>
>> '\''cert_password'\''; ALTER ROLE certuser WITH LOGIN;' postgres<br>
>> pgpoolnode | 2018-11-20 01:09:52: pid 76: WARNING: failed to open<br>
>> status file at: "/var/log/pgpool/pgpool_status"<br>
>> pgpoolnode | 2018-11-20 01:09:52: pid 76: DETAIL: "No such file or<br>
>> directory"<br>
>> clientnode | ALTER ROLE<br>
>> clientnode | + echo 'testing if ssl connection without proper client<br>
>> certificate is rejected'<br>
>> clientnode | + sudo -u postgres psql 'sslmode=require port=9999<br>
>> host=172.22.0.52 dbname=postgres user=scramuser'<br>
>> clientnode | testing if ssl connection without proper client<br>
>> certificate is rejected<br>
>> clientnode | psql: server does not support SSL, but SSL was required<br>
>> clientnode | + echo 'testing if ssl connection with proper client<br>
>> certificate works'<br>
>> clientnode | + sudo -u postgres psql 'sslmode=require port=9999<br>
>> host=172.22.0.52 dbname=postgres user=certuser'<br>
>> clientnode | testing if ssl connection with proper client<br>
>> certificate works<br>
>> clientnode | psql: server does not support SSL, but SSL was required<br>
>> clientnode | + tail -f /dev/null<br>
>> pgpoolnode | 2018-11-20 01:09:52: pid 75: WARNING: failed to open<br>
>> status file at: "/var/log/pgpool/pgpool_status"<br>
>> pgpoolnode | 2018-11-20 01:09:52: pid 75: DETAIL: "No such file or<br>
>> directory"<br>
>><br>
>><br>
>><br>
>> > Sorry, 2.txt was empty. Attached again.<br>
>> ><br>
>> >>>> Usama,<br>
>> >>>><br>
>> >>>> > Hi<br>
>> >>>> ><br>
>> >>>> > I have created a simple docker based example of using CERT<br>
>> authentication<br>
>> >>>> > with Pgpool-II frontend connections for the reference.<br>
>> >>>> ><br>
>> >>>> > Please have a look and let me know what you think<br>
>> >>>> ><br>
>> >>>> > <a href="https://github.com/codeforall/pgpool_cert_auth" rel="noreferrer" target="_blank">https://github.com/codeforall/pgpool_cert_auth</a><br>
>> >>>><br>
>> >>>> Unfortunately it does not work for me.<br>
>> >>>><br>
>> >>>> docker exec -it clientnode sudo -u postgres psql "sslmode=require<br>
>> >>>> port=9999 host=172.22.0.52 dbname=postgres user=certuser" -c "show<br>
>> >>>> pool_nodes"<br>
>> >>>> psql: server does not support SSL, but SSL was required<br>
>> >>>><br>
>> >>>><br>
>> >>> This is very strange, I have rebuild the dockers by pulling the fresh<br>
>> code<br>
>> >>> from repo and can run the test successfully.<br>
>> >>> Seems like setting of ssl configuration is failing.<br>
>> >>><br>
>> >>> can you please help me identify the issue by sending the log of<br>
>> >>> "docker-compose up " and of the output of following commands<br>
>> >><br>
>> >> Sure. Log attached.<br>
>> >><br>
>> >>> docker exec -it pgmaster /bin/bash -c 'cat $PGDATA/postgresql.conf'<br>
>> >><br>
>> >> Attached (1.txt).<br>
>> >><br>
>> >>> docker exec -it pgmaster /bin/bash -c 'cd $PGDATA/log && cat "$(ls<br>
>> -1rt |<br>
>> >>> tail -n1)"'<br>
>> >><br>
>> >> Attached (2.txt).<br>
>> >><br>
>> >>> docker exec -it pgslave /bin/bash -c 'cat $PGDATA/postgresql.conf'<br>
>> >><br>
>> >> Attached (3.txt).<br>
>> >><br>
>> >>><br>
>> >>> docker exec -it pgslave /bin/bash -c 'cd $PGDATA/log && cat "$(ls<br>
>> -1rt |<br>
>> >>> tail -n1)"'<br>
>> >><br>
>> >> Attached (4.txt).<br>
>> >><br>
>> >>> docker exec -it pgpoolnode /bin/bash -c 'cat<br>
>> ${PGPOOLCONF}/pgpool.conf'<br>
>> >><br>
>> >> Attached (5.txt).<br>
>> >><br>
>> >>>> Also I noticed you do not use Pgpool-II RPMs provided by Pgpool-II<br>
>> >>>> community:<br>
>> >>>> <a href="https://pgpool.net/mediawiki/index.php/Yum_Repository" rel="noreferrer" target="_blank">https://pgpool.net/mediawiki/index.php/Yum_Repository</a><br>
>> >>>><br>
>> >>>> Is there any reason for this?<br>
>> >>>><br>
>> >>>> No reason as such, I just installed the Pgpool rpms from same repo<br>
>> from<br>
>> >>> where I was getting the PG server.<br>
>> >>> I have update the docker files to use the pgpool community rpms<br>
>> instead.<br>
>> >>><br>
>> >>><br>
>> <a href="https://github.com/codeforall/pgpool_cert_auth/commit/218f7536330677597552330199d0fd637f88d5b0" rel="noreferrer" target="_blank">https://github.com/codeforall/pgpool_cert_auth/commit/218f7536330677597552330199d0fd637f88d5b0</a><br>
>> >>><br>
>> >>> Thanks<br>
>> >>> Best Regards<br>
>> >>> Muhammad Usama<br>
>> >>><br>
>> >>><br>
>> >>><br>
>> >>>> Best regards,<br>
>> >>>> --<br>
>> >>>> Tatsuo Ishii<br>
>> >>>> SRA OSS, Inc. Japan<br>
>> >>>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >>>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> >>>><br>
>><br>
</blockquote></div></div>