[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