[pgpool-hackers: 3142] Re: Example for CERT authentication with Pgpool-II
Tatsuo Ishii
ishii at sraoss.co.jp
Tue Nov 20 10:15:28 JST 2018
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