From e262be10517d15a43bbb882e737c11505beb0ce8 Mon Sep 17 00:00:00 2001 From: Tatsuo Ishii Date: Sat, 15 Mar 2025 14:00:36 +0900 Subject: [PATCH v2] Test: add new regression test for client authentication. This commit adds 040.client_auth new regression test. The test script (test.sh) reads csv files: client_auth_1node.csv and client_auth_2node.csv that describe the test cases. client_auth_1node.csv is for 1 PostgreSQL configuration test. client_auth_2node.csv is for 2 PostgreSQL configuration test. The reason we have two files here are, the test results in slightly different between them. u12 and u20 are ok in the 1 node configuration, while with the 2 node configuration, they fail. The csv files have 7 fields: ---- username: the username used for the test case pool_hba.conf: takes "scram", "md5" or "off". If "scram" or "md5", the user will have an entry in pool_hba.conf accordingly. If "off", enable_pool_hba.conf will be off. allow_clear_text_frontend_auth: takes "on" or "off". pool_passwd: takes "AES", "md5" or "off". If "AES" or "md5" the user's password will be stored in pool_passwd using ASE256 or md5 encryption method accordingly. If "off" is specified, no entry will be created. pg_hba.conf: almost same as pool_hba.conf except this is for pg_hba.conf. expected: takes "ok" or "fail". If ok, the authentication is expected to be succeeded. If failed, the test is regarded as failed. "fail" is opposite. The authentication is expected to be failed. If succeeds, the test regarded as failed. comment: arbitrary comment --- By changing these fields, we can easily modify or add test cases. Currently 20 test cases are in the table. The merit of this method is possible higher test coverage. For human, it is easier to find uncovered test cases in a table than in a program code. --- .../040.client_auth/client_auth_1node.csv | 21 ++ .../040.client_auth/client_auth_2node.csv | 21 ++ .../regression/tests/040.client_auth/test.sh | 296 ++++++++++++++++++ 3 files changed, 338 insertions(+) create mode 100644 src/test/regression/tests/040.client_auth/client_auth_1node.csv create mode 100644 src/test/regression/tests/040.client_auth/client_auth_2node.csv create mode 100755 src/test/regression/tests/040.client_auth/test.sh diff --git a/src/test/regression/tests/040.client_auth/client_auth_1node.csv b/src/test/regression/tests/040.client_auth/client_auth_1node.csv new file mode 100644 index 000000000..4e95f9772 --- /dev/null +++ b/src/test/regression/tests/040.client_auth/client_auth_1node.csv @@ -0,0 +1,21 @@ +username,pool_hba.conf,allow_clear_text_frontend_auth,pool_passwd,pg_hba.conf,expected,comment +u1,scram,off,AES,scram,ok, +u2,scram,off,AES,md5,ok, +u3,scram,off,md5,scram,fail, +u4,scram,off,md5,md5,fail, +u5,scram,off,off,scram,fail, +u6,scram,off,off,md5,fail, +u7,md5,off,AES,scram,ok, +u8,md5,off,AES,md5,ok, +u9,md5,off,md5,scram,fail, +u10,md5,off,md5,md5,ok, +u11,md5,off,off,scram,fail, +u12,md5,off,off,md5,ok,only ok in 1 node case +u13,off,on,off,scram,ok, +u14,off,on,off,md5,ok, +u15,off,off,AES,scram,ok, +u16,off,off,AES,md5,ok, +u17,off,off,md5,scram,fail, +u18,off,off,md5,md5,ok, +u19,off,off,off,scram,fail, +u20,off,off,off,md5,ok,only ok in 1 node case diff --git a/src/test/regression/tests/040.client_auth/client_auth_2node.csv b/src/test/regression/tests/040.client_auth/client_auth_2node.csv new file mode 100644 index 000000000..5cb8830fc --- /dev/null +++ b/src/test/regression/tests/040.client_auth/client_auth_2node.csv @@ -0,0 +1,21 @@ +username,pool_hba.conf,allow_clear_text_frontend_auth,pool_passwd,pg_hba.conf,expected,comment +u1,scram,off,AES,scram,ok, +u2,scram,off,AES,md5,ok, +u3,scram,off,md5,scram,fail, +u4,scram,off,md5,md5,fail, +u5,scram,off,off,scram,fail, +u6,scram,off,off,md5,fail, +u7,md5,off,AES,scram,ok, +u8,md5,off,AES,md5,ok, +u9,md5,off,md5,scram,fail, +u10,md5,off,md5,md5,ok, +u11,md5,off,off,scram,fail, +u12,md5,off,off,md5,fail,only ok in 1 node case +u13,off,on,off,scram,ok, +u14,off,on,off,md5,ok, +u15,off,off,AES,scram,ok, +u16,off,off,AES,md5,ok, +u17,off,off,md5,scram,fail, +u18,off,off,md5,md5,ok, +u19,off,off,off,scram,fail, +u20,off,off,off,md5,fail,only ok in 1 node case diff --git a/src/test/regression/tests/040.client_auth/test.sh b/src/test/regression/tests/040.client_auth/test.sh new file mode 100755 index 000000000..955227275 --- /dev/null +++ b/src/test/regression/tests/040.client_auth/test.sh @@ -0,0 +1,296 @@ +#!/usr/bin/bash +#------------------------------------------------------------------- +# test script for client authentication +# + +# This test is only valid with PostgreSQL 10 or later. +if [ $PGVERSION -le 9 ];then + exit 0 +fi + +#---------------------------------------- +# Function declarations + +# create user +# $1: username +# $2: "scram" or "md5" +function add_user() +{ + createuser $1 + if [ $2 = "scram" ];then + psql -c "SET password_encryption = 'scram-sha-256'; ALTER USER $1 WITH ENCRYPTED PASSWORD '$1'" + elif [ $2 = "md5" ];then + psql -c "SET password_encryption = 'md5'; ALTER USER $1 WITH ENCRYPTED PASSWORD '$1'" + else + echo "wrong auth method \"$2\" for add_user $1" + exit 1 + fi +} + +# create pool_hba.conf entry +# $1: username +# $2: auth method for pool_hba.conf +# "scram" or "md5" +function add_pool_hba() +{ + if [ $2 = "scram" ];then + echo "host all $1 127.0.0.1/32 scram-sha-256" >> $POOL_HBA + echo "host all $1 ::1/128 scram-sha-256" >> $POOL_HBA + elif [ $2 = "md5" ];then + echo "host all $1 127.0.0.1/32 md5" >> $POOL_HBA + echo "host all $1 ::1/128 md5" >> $POOL_HBA + else + echo "wrong auth method \"$2\" for add_pool_hba $1" + exit 1 + fi +} + +# create pool_passwd entry +# $1: username (also password) +# $2: encryption method (AES or md5) +function add_pool_passwd() +{ + if [ $2 = "AES" ];then + echo $1|$PG_ENC -m -f $PGPOOL_CONF -u $1 $1 + elif [ $2 = "md5" ];then + echo $1|$PG_MD5 -m -f $PGPOOL_CONF -u $1 $1 + else + echo "invalid encryption method \"$2\" for pool_passwd" + exit 1 + fi +} + +# create pg_hba.conf entry +# $1: username +# $2: "scram" or "md5" +function add_pg_hba() +{ + for i in data0 data1 + do + if [ ! -d $i ];then + continue + fi + + PG_HBA=$i/pg_hba.conf + if [ $2 = "scram" ];then + echo "host all $1 127.0.0.1/32 scram-sha-256" >> $PG_HBA + echo "host all $1 ::1/128 scram-sha-256" >> $PG_HBA + elif [ $2 = "md5" ];then + echo "host all $1 127.0.0.1/32 md5" >> $PG_HBA + echo "host all $1 ::1/128 md5" >> $PG_HBA + else + echo "wrong auth method \"$2\" for add_pg_hba $1" + exit 1 + fi + done +} + +# create pgpass entry +# $1: username +function add_pgpass() +{ + echo "127.0.0.1:$PGPORT:$PGDATABASE:$1:$1" >> pgpass + echo "127.0.0.1:$PGPORT:$PGDATABASE:$1:$1foo" >> pgpasswrong +} + +# Perform actual tests +# $1: test spec csv file +# $2: number of PostgreSQL nodes +function do_auth +{ + rm -fr $TESTDIR + mkdir $TESTDIR + cd $TESTDIR + + cp /dev/null $failed_usernames + + # create test environment + echo -n "creating test environment..." + $PGPOOL_SETUP -m s -n $2 || exit 1 + #$PGPOOL_SETUP -m s -n 1 || exit 1 + echo "done." + + source ./bashrc.ports + export PGPORT=$PGPOOL_PORT + export PGDATABASE=test + + # Set max_init_children to 1 to make sure we reuse the + # connection to test wrong password rejection + echo "num_init_children = 1" >> etc/pgpool.conf + + PGPOOL_CONF=etc/pgpool.conf + POOL_HBA=etc/pool_hba.conf + + # + # replace trust auth for all users with superuser in pg_hba.conf + for i in data0 data1 + do + if [ ! -d $i ];then + continue + fi + + sed -i "s/local all all trust/local all $superuser trust/" $i/pg_hba.conf + sed -i "s/host all all all trust/host all $superuser all trust/" $i/pg_hba.conf + done + + # + # replace trust auth for all users with superuser in pool_hba.conf + sed -i "s@host all all 127.0.0.1/32 trust@host all $superuser all trust@" $POOL_HBA + sed -i "s@host all all ::1/128 trust@host all $superuser ::1/128 trust@" $POOL_HBA + + # set up pgpass + cp /dev/null pgpass + chmod 0600 pgpass + cp /dev/null pgpasswrong + chmod 0600 pgpasswrong + + # pgpool.conf opt + cp /dev/null $PGPOOL_CONF_OPT + echo "include pgpool.conf.opt" >> etc/pgpool.conf + + # start pgpool + ./startall + wait_for_pgpool_startup + + IFS="," + + # + # setup each user + # + cat $spec|while read line + do + set $line + if [ $1 = 'username' ];then + echo "skip tile row" + else + username=$1 + echo "==== $username ====" + pool_hba=$2 + allow_clear=$3 + pool_passwd=$4 + pg_hba=$5 + expected=$6 + + add_user $username $pg_hba + + if [ $pool_hba = "scram" -o $pool_hba = "md5" ]; then + add_pool_hba $username $pool_hba + fi + + if [ $pool_passwd = "AES" -o $pool_passwd = "md5" ]; then + add_pool_passwd $username $pool_passwd + fi + add_pg_hba $username $pg_hba + add_pgpass $username + fi + done + ./shutdownall + + # + # do auth tests + # + cat $spec|while read line + do + set $line + if [ $1 = 'username' ];then + echo "skip tile row" + else + username=$1 + echo "==== $username ====" + pool_hba=$2 + allow_clear=$3 + pool_passwd=$4 + pg_hba=$5 + expected=$6 + + cp $PGPOOL_CONF_OPT $PGPOOL_CONF_OPT.old + if [ $pool_hba = "scram" -o $pool_hba = "md5" ];then + echo "enable_pool_hba = on" > $PGPOOL_CONF_OPT + else + if [ $allow_clear = "off" ];then + cp /dev/null $PGPOOL_CONF_OPT + else + echo "allow_clear_text_frontend_auth = on" > $PGPOOL_CONF_OPT + fi + fi + + # if pgpool.conf.opt was changed, restart pgpool + cmp $PGPOOL_CONF_OPT $PGPOOL_CONF_OPT.old + if [ $? != 0 ];then + ./shutdownall + ./startall + wait_for_pgpool_startup + fi + + ok="" + result="" + PGPASSFILE=pgpass $PSQL -h 127.0.0.1 -U $username -c "SELECT user" >/dev/null 2>&1 + rtn=$? + if [ $expected = "ok" ];then + echo "checking $username auth expecting success..." + if [ $rtn = 0 ];then + echo "$username: password verification succeeded." + echo "$username: try with wrong password expecting rejected..." + PGPASSFILE=pgpasswrong $PSQL -h 127.0.0.1 -U $username -c "SELECT user">/dev/null 2>&1 + if [ $? = 0 ];then + echo "$username: wrong password verification failed." + else + echo "$username: wrong password rejected as expected" + result=ok + fi + else + echo "$username: password verification failed." + echo -n " $username" >> $failed_usernames + fi + else # expecting fail + echo "checking $username auth expecting failure..." + if [ $rtn != 0 ];then + echo "$username: password verification failed as expected" + result=ok + else + echo "$username: unexpected password verification success" + fi + fi + + if [ "$result" != "ok" ];then + echo -n " $username" >> $failed_usernames + fi + fi + done + + ./shutdownall + cd .. + + if [ -s $failed_usernames ];then + echo "failed tests: `cat $failed_usernames`" + exit 1 + fi +} + +# +#---------------------------------------- +# Misc preparations + +failed_usernames=/tmp/failed_usernames +trap "rm $failed_usernames" EXIT +cp /dev/null $failed_usernames + +source $TESTLIBS +TESTDIR=testdir +PSQL=$PGBIN/psql +PG_ENC=$PGPOOL_INSTALL_DIR/bin/pg_enc +PG_MD5=$PGPOOL_INSTALL_DIR/bin/pg_md5 +export CREATEUSER=$PGBIN/createuser +superuser=`whoami` +PGPOOL_CONF_OPT=etc/pgpool.conf.opt + +# +#---------------------------------------- +# Test execution starts here + +spec=../client_auth_1node.csv +do_auth $spec 1 +spec=../client_auth_2node.csv +#do_auth $spec 2 + +exit 0 -- 2.25.1