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

Tatsuo Ishii ishii at sraoss.co.jp
Wed Nov 21 10:53:34 JST 2018


Usama,

One thing I noticed was, cert files generated here are not correct.
For example,

openssl x509 -in root.crt -noout -text
:
:
        Validity
            Not Before: Nov 20 01:06:17 2018 GMT
            Not After : Nov 17 01:06:17 2028 GMT
:
:

So root.crt cannot be used. I have tried to fix the issue by applying
attached patch but still the test does not succeed. Maybe there are
some other issues remaining in cert files.

> 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
>>>>>
> _______________________________________________
> pgpool-hackers mailing list
> pgpool-hackers at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
-------------- next part --------------
A non-text attachment was scrubbed...
Name: cert_auth.diff
Type: text/x-patch
Size: 1423 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20181121/e4be516f/attachment-0001.bin>


More information about the pgpool-hackers mailing list