1#!/usr/bin/env bash 2 3# Notes: 4# - uses iptables and -F with some tests, probably not very friendly to your firewall 5 6cd $(dirname $0) 7 8export PGDATA=$PWD/pgdata 9export PGHOST=127.0.0.1 10export PGPORT=6667 11export EF_ALLOW_MALLOC_0=1 12export LC_ALL=C 13export POSIXLY_CORRECT=1 14 15BOUNCER_LOG=test.log 16BOUNCER_INI=test.ini 17BOUNCER_PID=test.pid 18BOUNCER_PORT=`sed -n '/^listen_port/s/listen_port.*=[^0-9]*//p' $BOUNCER_INI` 19BOUNCER_EXE="$BOUNCER_EXE_PREFIX ../pgbouncer" 20 21BOUNCER_ADMIN_HOST=/tmp 22 23LOGDIR=log 24PG_PORT=6666 25PG_LOG=$LOGDIR/pg.log 26 27pgctl() { 28 pg_ctl -w -o "-p $PG_PORT" -D $PGDATA $@ >>$PG_LOG 2>&1 29} 30 31ulimit -c unlimited 32 33which initdb > /dev/null || { 34 echo "initdb not found, need postgres tools in PATH" 35 exit 1 36} 37 38# The tests require that psql can connect to the PgBouncer admin 39# console. On platforms that have getpeereid(), this works by 40# connecting as user pgbouncer over the Unix socket. On other 41# platforms, we have to rely on "trust" authentication, but then we 42# have to skip any tests that use authentication methods other than 43# "trust". 44case `uname` in 45 MINGW*) 46 have_getpeereid=false 47 use_unix_sockets=false 48 ;; 49 *) 50 have_getpeereid=true 51 use_unix_sockets=true 52 ;; 53esac 54 55# System configuration checks 56SED_ERE_OP='-E' 57case `uname` in 58Linux) 59 SED_ERE_OP='-r' 60 ;; 61esac 62 63case `uname` in 64MINGW*) 65 createdb() { createdb.exe "$@"; } 66 initdb() { initdb.exe "$@"; } 67 psql() { psql.exe "$@"; } 68 ;; 69esac 70 71pg_majorversion=$(initdb --version | sed -n $SED_ERE_OP 's/.* ([0-9]+).*/\1/p') 72if test $pg_majorversion -ge 10; then 73 pg_supports_scram=true 74else 75 pg_supports_scram=false 76fi 77 78if ! $use_unix_sockets; then 79 BOUNCER_ADMIN_HOST=127.0.0.1 80 81 cp test.ini test.ini.bak 82 sed -i 's/^unix_socket_dir =/#&/' test.ini 83 echo 'admin_users = pgbouncer' >> test.ini 84fi 85 86MAX_PASSWORD=$(sed -n $SED_ERE_OP 's/#define MAX_PASSWORD[[:space:]]+([0-9]+)/\1/p' ../include/bouncer.h) 87long_password=$(printf '%*s' $(($MAX_PASSWORD - 1)) | tr ' ' 'a') 88 89# System configuration checks 90if ! grep -q "^\"${USER:=$(id -un)}\"" userlist.txt; then 91 cp userlist.txt userlist.txt.bak 92 echo "\"${USER}\" \"01234\"" >> userlist.txt 93 echo "\"longpass\" \"${long_password}\"" >> userlist.txt 94fi 95 96if test -n "$USE_SUDO"; then 97 case `uname` in 98 OpenBSD) 99 sudo pfctl -a pgbouncer -F all -q 2>&1 | grep -q "pfctl:" && { 100 cat <<-EOF 101 Please enable PF and add the following rule to /etc/pf.conf 102 103 anchor "pgbouncer/*" 104 105 EOF 106 exit 1 107 } 108 ;; 109 esac 110fi 111 112stopit() { 113 local pid 114 if test -f "$1"; then 115 pid=`head -n1 "$1"` 116 kill $pid 117 while kill -0 $pid 2>/dev/null; do sleep 0.1; done 118 rm -f "$1" 119 fi 120} 121 122stopit test.pid 123stopit pgdata/postmaster.pid 124 125mkdir -p $LOGDIR 126rm -f $BOUNCER_LOG $PG_LOG 127rm -rf $PGDATA 128 129if [ ! -d $PGDATA ]; then 130 mkdir $PGDATA 131 initdb --nosync >> $PG_LOG 2>&1 132 if $use_unix_sockets; then 133 sed $SED_ERE_OP -i "/unix_socket_director/s:.*(unix_socket_director.*=).*:\\1 '/tmp':" pgdata/postgresql.conf 134 fi 135 cat >>pgdata/postgresql.conf <<-EOF 136 log_connections = on 137 EOF 138 if $pg_supports_scram; then 139 cat >pgdata/pg_hba.conf <<-EOF 140 local p6 all scram-sha-256 141 host p6 all 127.0.0.1/32 scram-sha-256 142 host p6 all ::1/128 scram-sha-256 143 EOF 144 else 145 cat >pgdata/pg_hba.conf </dev/null 146 fi 147 cat >>pgdata/pg_hba.conf <<-EOF 148 local p4 all password 149 host p4 all 127.0.0.1/32 password 150 host p4 all ::1/128 password 151 local p5 all md5 152 host p5 all 127.0.0.1/32 md5 153 host p5 all ::1/128 md5 154 local all all trust 155 host all all 127.0.0.1/32 trust 156 host all all ::1/128 trust 157 EOF 158 if ! $use_unix_sockets; then 159 sed -i 's/^local/#local/' pgdata/pg_hba.conf 160 fi 161fi 162 163pgctl start 164 165echo "Creating databases" 166psql -X -p $PG_PORT -l | grep p0 > /dev/null || { 167 psql -X -o /dev/null -p $PG_PORT -c "create user bouncer" template1 || exit 1 168 for dbname in p0 p1 p3 p4 p5 p6 p7; do 169 createdb -p $PG_PORT $dbname || exit 1 170 done 171} 172 173psql -X -p $PG_PORT -d p0 -c "select * from pg_user" | grep pswcheck > /dev/null || { 174 echo "Creating users" 175 psql -X -o /dev/null -p $PG_PORT -c "create user pswcheck with superuser createdb password 'pgbouncer-check';" p0 || exit 1 176 psql -X -o /dev/null -p $PG_PORT -c "create user someuser with password 'anypasswd';" p0 || exit 1 177 psql -X -o /dev/null -p $PG_PORT -c "create user maxedout;" p0 || exit 1 178 psql -X -o /dev/null -p $PG_PORT -c "create user longpass with password '$long_password';" p0 || exit 1 179 if $pg_supports_scram; then 180 psql -X -o /dev/null -p $PG_PORT -c "set password_encryption = 'md5'; create user muser1 password 'foo';" p0 || exit 1 181 psql -X -o /dev/null -p $PG_PORT -c "set password_encryption = 'md5'; create user muser2 password 'wrong';" p0 || exit 1 182 psql -X -o /dev/null -p $PG_PORT -c "set password_encryption = 'md5'; create user puser1 password 'foo';" p0 || exit 1 183 psql -X -o /dev/null -p $PG_PORT -c "set password_encryption = 'md5'; create user puser2 password 'wrong';" p0 || exit 1 184 # match SCRAM secret in userlist.txt 185 psql -X -o /dev/null -p $PG_PORT -c "set password_encryption = 'scram-sha-256'; create user scramuser1 password '"'SCRAM-SHA-256$4096:D76gvGUVj9Z4DNiGoabOBg==$RukL0Xo3Ql/2F9FsD7mcQ3GATG2fD3PA71qY1JagGDs=:BhKUwyyivFm7Tq2jDJVXSVRbRDgTWyBilZKgg6DDuYU='"';" p0 || exit 1 186 psql -X -o /dev/null -p $PG_PORT -c "set password_encryption = 'scram-sha-256'; create user scramuser3 password 'baz';" p0 || exit 1 187 else 188 psql -X -o /dev/null -p $PG_PORT -c "set password_encryption = on; create user muser1 password 'foo';" p0 || exit 1 189 psql -X -o /dev/null -p $PG_PORT -c "set password_encryption = on; create user muser2 password 'wrong';" p0 || exit 1 190 psql -X -o /dev/null -p $PG_PORT -c "set password_encryption = on; create user puser1 password 'foo';" p0 || exit 1 191 psql -X -o /dev/null -p $PG_PORT -c "set password_encryption = on; create user puser2 password 'wrong';" p0 || exit 1 192 fi 193} 194 195# 196# fw hacks 197# 198 199fw_enable() { 200 case `uname` in 201 Darwin) 202 fw_token=$(sudo pfctl -E 2>&1 | grep '^Token' | cut -d ' ' -f 3);; 203 esac 204} 205 206fw_disable() { 207 case `uname` in 208 Darwin) 209 sudo pfctl -X "$fw_token";; 210 esac 211} 212 213fw_drop_port() { 214 fw_enable 215 case `uname` in 216 Linux) 217 sudo iptables -A OUTPUT -p tcp --dport $1 -j DROP;; 218 Darwin) 219 echo "block drop out proto tcp from any to 127.0.0.1 port $1" \ 220 | sudo pfctl -f -;; 221 OpenBSD) 222 echo "block drop out proto tcp from any to 127.0.0.1 port $1" \ 223 | sudo pfctl -a pgbouncer -f -;; 224 *) 225 echo "Unknown OS"; exit 1;; 226 esac 227} 228fw_reject_port() { 229 fw_enable 230 case `uname` in 231 Linux) 232 sudo iptables -A OUTPUT -p tcp --dport $1 -j REJECT --reject-with tcp-reset;; 233 Darwin) 234 echo "block return-rst out proto tcp from any to 127.0.0.1 port $1" \ 235 | sudo pfctl -f -;; 236 OpenBSD) 237 echo "block return-rst out proto tcp from any to 127.0.0.1 port $1" \ 238 | sudo pfctl -a pgbouncer -f -;; 239 *) 240 echo "Unknown OS"; exit 1;; 241 esac 242} 243 244fw_reset() { 245 fw_disable 246 case `uname` in 247 Linux) 248 sudo iptables -F OUTPUT;; 249 Darwin) 250 sudo pfctl -F all;; 251 OpenBSD) 252 sudo pfctl -a pgbouncer -F all;; 253 *) 254 echo "Unknown OS"; exit 1;; 255 esac 256} 257 258# 259# util functions 260# 261 262complete() { 263 test -f $BOUNCER_PID && kill `cat $BOUNCER_PID` >/dev/null 2>&1 264 pgctl -m fast stop 265 rm -f $BOUNCER_PID 266 test -e test.ini.bak && mv test.ini.bak test.ini 267 test -e userlist.txt.bak && mv userlist.txt.bak userlist.txt 268} 269 270die() { 271 echo $@ 272 complete 273 exit 1 274} 275 276admin() { 277 psql -X -h $BOUNCER_ADMIN_HOST -U pgbouncer -d pgbouncer -c "$@;" || die "Cannot contact bouncer!" 278} 279 280runtest() { 281 local status 282 283 case `uname` in 284 MINGW*) 285 (nohup $BOUNCER_EXE $BOUNCER_INI </dev/null >/dev/null 2>&1 &) 286 ;; 287 *) 288 $BOUNCER_EXE -d $BOUNCER_INI 289 ;; 290 esac 291 until psql -X -h $BOUNCER_ADMIN_HOST -U pgbouncer -d pgbouncer -c "show version" 2>/dev/null 1>&2; do sleep 0.1; done 292 293 printf "`date` running $1 ... " 294 eval $1 >$LOGDIR/$1.out 2>&1 295 status=$? 296 297 # Detect fatal errors from PgBouncer (which are internal 298 # errors), but not those from PostgreSQL (which could be 299 # normal, such as authentication failures) 300 if grep 'FATAL @' $BOUNCER_LOG >> $LOGDIR/$1.out; then 301 status=1 302 fi 303 304 if [ $status -eq 0 ]; then 305 echo "ok" 306 elif [ $status -eq 77 ]; then 307 echo "skipped" 308 status=0 309 else 310 echo "FAILED" 311 cat $LOGDIR/$1.out | sed 's/^/# /' 312 fi 313 date >> $LOGDIR/$1.out 314 315 # allow background processing to complete 316 wait 317 318 case `uname` in 319 MINGW*) 320 psql -X -h $BOUNCER_ADMIN_HOST -U pgbouncer -d pgbouncer -c "shutdown;" 2>/dev/null 321 sleep 1 322 ;; 323 *) 324 stopit test.pid 325 ;; 326 esac 327 mv $BOUNCER_LOG $LOGDIR/$1.log 328 329 return $status 330} 331 332# show version and --version 333test_show_version() { 334 v1=$($BOUNCER_EXE --version | head -n 1) || return 1 335 v2=$(psql -X -tAq -h $BOUNCER_ADMIN_HOST -U pgbouncer -d pgbouncer -c "show version;") || return 1 336 337 echo "v1=$v1" 338 echo "v2=$v2" 339 340 test x"$v1" = x"$v2" 341} 342 343# test all the show commands 344# 345# This test right now just runs all the commands without checking the 346# output, which would be difficult. This at least ensures the 347# commands don't completely die. The output can be manually eyeballed 348# in the test log file. 349test_show() { 350 for what in clients config databases fds help lists pools servers sockets active_sockets stats stats_totals stats_averages users totals mem dns_hosts dns_zones; do 351 echo "=> show $what;" 352 psql -X -h $BOUNCER_ADMIN_HOST -U pgbouncer -d pgbouncer -c "show $what;" || return 1 353 done 354 355 psql -X -h $BOUNCER_ADMIN_HOST -U pgbouncer -d pgbouncer -c "show bogus;" && return 1 356 357 return 0 358} 359 360# server_lifetime 361test_server_lifetime() { 362 admin "set server_lifetime=2" 363 psql -X -c "select now()" p0 364 sleep 3 365 366 rc=`psql -X -p $PG_PORT -tAqc "select count(1) from pg_stat_activity where usename='bouncer' and datname='p0'" p0` 367 psql -X -c "select now()" p0 368 return $rc 369} 370 371# server_idle_timeout 372test_server_idle_timeout() { 373 admin "set server_idle_timeout=2" 374 psql -X -c "select now()" p0 375 sleep 3 376 rc=`psql -X -p $PG_PORT -tAq -c "select count(1) from pg_stat_activity where usename='bouncer' and datname='p0'" p0` 377 psql -X -c "select now()" p0 378 return $rc 379} 380 381# query_timeout 382test_query_timeout() { 383 admin "set query_timeout=3" 384 psql -X -c "select pg_sleep(5)" p0 && return 1 385 return 0 386} 387 388# idle_transaction_timeout 389test_idle_transaction_timeout() { 390 admin "set pool_mode=transaction" 391 admin "set idle_transaction_timeout=2" 392 393 psql -X --set ON_ERROR_STOP=1 p0 <<-PSQL_EOF 394 begin; 395 \! sleep 3 396 select now(); 397 PSQL_EOF 398 test $? -eq 0 && return 1 399 400 # test for GH issue #125 401 psql -X --set ON_ERROR_STOP=1 p0 <<-PSQL_EOF 402 begin; 403 select pg_sleep(2); 404 \! sleep 1 405 select now(); 406 PSQL_EOF 407 test $? -ne 0 && return 1 408 409 return 0 410} 411 412# client_idle_timeout 413test_client_idle_timeout() { 414 admin "set client_idle_timeout=2" 415 psql -X --set ON_ERROR_STOP=1 p0 <<-PSQL_EOF 416 select now(); 417 \! sleep 3 418 select now(); 419 PSQL_EOF 420 test $? -eq 0 && return 1 421 return 0 422} 423 424# server_login_retry 425test_server_login_retry() { 426 admin "set query_timeout=10" 427 admin "set server_login_retry=3" 428 429 pgctl -m fast stop 430 (sleep 1; pgctl start) & 431 psql -X -c "select now()" p0 432 rc=$? 433 wait 434 return $rc 435} 436 437# tcp_user_timeout 438test_tcp_user_timeout() { 439 test -z "$USE_SUDO" && return 77 440 test `uname` = Linux || return 77 441 # Doesn't seem to work with older kernels (Ubuntu trusty is 442 # affected), not sure what the actual cut-off is. 443 case `uname -r` in 1.*|2.*|3.*|4.*) return 77;; esac 444 445 admin "set tcp_user_timeout=1000" 446 admin "set query_timeout=5" 447 448 # make a connection is active 449 psql -X -c "select now()" p0 450 451 # block connectivity 452 fw_drop_port $PG_PORT 453 454 # try to use the connection again 455 psql -X -c "select now()" p0 456 457 fw_reset 458 459 # without tcp_user_timeout, you get a different error message 460 # about "query timeout" instead 461 grep -F 'closing because: server conn crashed?' $BOUNCER_LOG 462} 463 464# server_connect_timeout 465test_server_connect_timeout_establish() { 466 psql -X -p $PG_PORT -c "alter system set pre_auth_delay to '60s'" p0 467 pgctl reload 468 sleep 1 469 470 admin "set query_timeout=3" 471 admin "set server_connect_timeout=2" 472 psql -X -c "select now()" p0 473 # client will always see query_timeout, need to grep for connect timeout 474 grep "closing because: connect timeout" $BOUNCER_LOG 475 rc=$? 476 477 rm -f pgdata/postgresql.auto.conf 478 pgctl reload 479 sleep 1 480 481 return $rc 482} 483 484# server_connect_timeout - block with iptables 485test_server_connect_timeout_reject() { 486 test -z "$USE_SUDO" && return 77 487 admin "set query_timeout=5" 488 admin "set server_connect_timeout=3" 489 fw_drop_port $PG_PORT 490 psql -X -c "select now()" p0 491 fw_reset 492 # client will always see query_timeout, need to grep for connect timeout 493 grep "closing because: connect failed" $BOUNCER_LOG 494} 495 496# server_check_delay 497test_server_check_delay() { 498 test -z "$USE_SUDO" && return 77 499 500 admin "set server_check_delay=2" 501 admin "set server_login_retry=3" 502 admin "set query_timeout=10" 503 504 psql -X -c "select now()" p0 505 fw_reject_port $PG_PORT 506 sleep 3 507 psql -X -tAq -c "select 1" p0 >$LOGDIR/test.tmp & 508 sleep 1 509 fw_reset 510 echo `date` rules flushed 511 wait 512 echo `date` done waiting 513 514 test "`cat $LOGDIR/test.tmp`" = "1" 515} 516 517# max_client_conn 518test_max_client_conn() { 519 admin "set max_client_conn=5" 520 admin "show config" 521 522 for i in {1..4}; do 523 psql -X -c "select now() as sleeping from pg_sleep(3);" p1 & 524 done 525 526 # last conn allowed 527 psql -X -c "select now() as last_conn" p1 || return 1 528 529 # exhaust it 530 psql -X -c "select now() as sleeping from pg_sleep(3);" p1 & 531 sleep 1 532 533 # shouldn't be allowed 534 psql -X -c "select now() as exhausted" p1 && return 1 535 536 # should be ok 537 echo 'waiting for clients to complete ...' 538 wait 539 psql -X -c "select now() as ok" p1 || return 1 540 541 return 0 542} 543 544# - max pool size 545test_pool_size() { 546 # make existing connections go away 547 psql -X -p $PG_PORT -d postgres -c "select pg_terminate_backend(pid) from pg_stat_activity where usename='bouncer'" 548 until test $(psql -X -p $PG_PORT -d postgres -tAq -c "select count(1) from pg_stat_activity where usename='bouncer'") -eq 0; do sleep 0.1; done 549 550 docount() { 551 for i in {1..10}; do 552 psql -X -c "select pg_sleep(0.5)" $1 >/dev/null & 553 done 554 wait 555 cnt=`psql -X -p $PG_PORT -tAq -c "select count(1) from pg_stat_activity where usename='bouncer' and datname='$1'" postgres` 556 echo $cnt 557 } 558 559 test `docount p0` -eq 2 || return 1 560 test `docount p1` -eq 5 || return 1 561 562 # test reload (GH issue #248) 563 admin "set default_pool_size = 7" 564 test `docount p1` -eq 7 || return 1 565 566 return 0 567} 568 569test_min_pool_size() { 570 # make existing connections go away 571 psql -X -p $PG_PORT -d postgres -c "select pg_terminate_backend(pid) from pg_stat_activity where usename='bouncer'" 572 until test $(psql -X -p $PG_PORT -d postgres -tAq -c "select count(1) from pg_stat_activity where usename='bouncer'") -eq 0; do sleep 0.1; done 573 574 # default_pool_size=5 575 admin "set min_pool_size = 3" 576 577 cnt=`psql -X -p $PG_PORT -tAq -c "select count(1) from pg_stat_activity where usename='bouncer' and datname='p1'" postgres` 578 echo $cnt 579 test "$cnt" -eq 0 || return 1 580 581 # It's a bit tricky to get the timing of this test to work 582 # robustly: Full maintenance runs three times a second, so we 583 # need to wait at least 1/3 seconds for it to notice for sure 584 # that the pool is in use. When it does, it will launch one 585 # connection per round, so we need to wait at least 3 * 1/3 586 # second before all the min pool connections are launched. 587 # Also, we need to keep the query running while this is 588 # happening so that the pool doesn't become momentarily 589 # unused. 590 psql -X -c "select pg_sleep(2)" p1 & 591 sleep 2 592 593 cnt=`psql -X -p $PG_PORT -tAq -c "select count(1) from pg_stat_activity where usename='bouncer' and datname='p1'" postgres` 594 echo $cnt 595 test "$cnt" -eq 3 || return 1 596} 597 598test_reserve_pool_size() { 599 # make existing connections go away 600 psql -X -p $PG_PORT -d postgres -c "select pg_terminate_backend(pid) from pg_stat_activity where usename='bouncer'" 601 until test $(psql -X -p $PG_PORT -d postgres -tAq -c "select count(1) from pg_stat_activity where usename='bouncer'") -eq 0; do sleep 0.1; done 602 603 # default_pool_size=5 604 admin "set reserve_pool_size = 3" 605 606 for i in {1..8}; do 607 psql -X -c "select pg_sleep(8)" p1 >/dev/null & 608 done 609 sleep 1 610 cnt=`psql -X -p $PG_PORT -tAq -c "select count(1) from pg_stat_activity where usename='bouncer' and datname='p1'" postgres` 611 echo $cnt 612 test "$cnt" -eq 5 || return 1 613 614 sleep 7 # reserve_pool_timeout + wiggle room 615 616 cnt=`psql -X -p $PG_PORT -tAq -c "select count(1) from pg_stat_activity where usename='bouncer' and datname='p1'" postgres` 617 echo $cnt 618 test "$cnt" -eq 8 || return 1 619 620 grep "taking connection from reserve_pool" $BOUNCER_LOG || return 1 621} 622 623test_max_db_connections() { 624 local users 625 626 # some users, doesn't matter which ones 627 users=(muser1 muser2 puser1 puser2) 628 629 docount() { 630 for i in {1..10}; do 631 psql -X -U ${users[$(($i % 4))]} -c "select pg_sleep(0.5)" p2 >/dev/null & 632 done 633 wait 634 cnt=`psql -X -p $PG_PORT -tAq -c "select count(1) from pg_stat_activity where usename in ('muser1', 'muser2', 'puser1', 'puser2') and datname='p0'" postgres` 635 echo $cnt 636 } 637 638 test `docount` -eq 4 || return 1 639 640 return 0 641} 642 643test_max_user_connections() { 644 local databases 645 646 databases=(p7a p7b p7c) 647 648 docount() { 649 for i in {1..10}; do 650 psql -X -U maxedout -c "select pg_sleep(0.5)" ${databases[$(($i % 3))]} >/dev/null & 651 done 652 wait 653 cnt=`psql -X -p $PG_PORT -tAq -c "select count(1) from pg_stat_activity where datname = 'p7'" postgres` 654 echo $cnt 655 } 656 657 test `docount` -eq 3 || return 1 658 659 return 0 660} 661 662# test online restart while clients running 663test_online_restart() { 664# max_client_conn=10 665# default_pool_size=5 666 $have_getpeereid || return 77 667 668 for i in {1..5}; do 669 echo "`date` attempt $i" 670 671 for j in {1..5}; do 672 psql -X -c "select now() as sleeping from pg_sleep(2)" p1 & 673 done 674 675 pid1=`cat $BOUNCER_PID` 676 echo "old bouncer is $pid1" 677 $BOUNCER_EXE -d -R $BOUNCER_INI 678 sleep 2 679 pid2=`cat $BOUNCER_PID` 680 echo "new bouncer is $pid2" 681 [ $pid1 = $pid2 ] && return 1 682 done 683 return 0 684} 685 686# test pause/resume 687test_pause_resume() { 688 rm -f $LOGDIR/test.tmp 689 for i in {1..50}; do 690 psql -X -tAq -c 'select 1 from pg_sleep(0.1)' p0 >>$LOGDIR/test.tmp 691 done & 692 693 for i in {1..5}; do 694 admin "pause" 695 sleep 1 696 admin "resume" 697 sleep 1 698 done 699 700 wait 701 test `wc -l <$LOGDIR/test.tmp` -eq 50 702} 703 704# test suspend/resume 705test_suspend_resume() { 706 rm -f $LOGDIR/test.tmp 707 for i in {1..50}; do 708 psql -X -tAq -c 'select 1 from pg_sleep(0.1)' p0 >>$LOGDIR/test.tmp 709 done & 710 711 for i in {1..5}; do 712 psql -X -h $BOUNCER_ADMIN_HOST -p $BOUNCER_PORT -d pgbouncer -U pgbouncer <<-PSQL_EOF 713 suspend; 714 \! sleep 1 715 resume; 716 \! sleep 1 717 PSQL_EOF 718 done 719 720 wait 721 test `wc -l <$LOGDIR/test.tmp` -eq 50 722} 723 724# test enable/disable 725test_enable_disable() { 726 rm -f $LOGDIR/test.tmp 727 psql -X -tAq -c "select 'enabled 1'" >>$LOGDIR/test.tmp p0 2>&1 728 729 admin "disable p0" 730 psql -X -tAq -c "select 'disabled 1'" >>$LOGDIR/test.tmp p0 2>&1 731 admin "enable p0" 732 psql -X -tAq -c "select 'enabled 2'" >>$LOGDIR/test.tmp p0 2>&1 733 734 grep -q "enabled 1" $LOGDIR/test.tmp || return 1 735 grep -q "enabled 2" $LOGDIR/test.tmp || return 1 736 grep -q "disabled 1" $LOGDIR/test.tmp && return 1 737 grep -q "does not allow" $LOGDIR/test.tmp || return 1 738 return 0 739} 740 741# test pool database restart 742test_database_restart() { 743 admin "set server_login_retry=1" 744 745 psql -X -c "select now() as p0_before_restart" p0 746 pgctl -m fast restart 747 echo `date` restart 1 748 psql -X -c "select now() as p0_after_restart" p0 || return 1 749 750 751 # do with some more clients 752 for i in {1..5}; do 753 psql -X -c "select pg_sleep($i)" p0 & 754 psql -X -c "select pg_sleep($i)" p1 & 755 done 756 757 pgctl -m fast restart 758 echo `date` restart 2 759 760 wait 761 psql -X -c "select now() as p0_after_restart" p0 || return 1 762 763 # connect to clear server_login_retry state 764 psql -X -c "select now() as p1_after_restart" p1 765 766 return 0 767} 768 769# test connect string change 770test_database_change() { 771 admin "set server_lifetime=2" 772 773 db1=`psql -X -tAq -c "select current_database()" p1` 774 775 cp test.ini test.ini.bak 776 sed '/^p1 =/s/dbname=p1/dbname=p0/g' test.ini >test2.ini 777 mv test2.ini test.ini 778 779 admin "reload" 780 781 sleep 3 782 db2=`psql -X -tAq -c "select current_database()" p1` 783 784 echo "db1=$db1 db2=$db2" 785 cp test.ini.bak test.ini 786 rm test.ini.bak 787 788 admin "show databases" 789 admin "show pools" 790 791 test "$db1" = "p1" -a "$db2" = "p0" 792} 793 794# test reconnect 795test_reconnect() { 796 bp1=`psql -X -tAq -c "select pg_backend_pid()" p1` 797 admin "reconnect p1" 798 sleep 1 799 bp2=`psql -X -tAq -c "select pg_backend_pid()" p1` 800 echo "bp1=$bp1 bp2=$bp2" 801 test "$bp1" != "$bp2" 802} 803 804# test server_fast_close 805test_fast_close() { 806 ( 807 echo "select pg_backend_pid();" 808 sleep 2 809 echo "select pg_backend_pid();" 810 echo "\q" 811 ) | psql -X -tAq -f- -d p3 >$LOGDIR/testout.tmp 2>$LOGDIR/testerr.tmp & 812 sleep 1 813 admin "set server_fast_close = 1" 814 admin "reconnect p3" 815 wait 816 817 admin "show databases" 818 admin "show pools" 819 admin "show servers" 820 821 # If this worked correctly, the session will be closed between 822 # the two queries, so the second query will fail and leave an 823 # error. 824 test `wc -l <$LOGDIR/testout.tmp` -eq 1 && test `wc -l <$LOGDIR/testerr.tmp` -ge 1 825} 826 827# test wait_close 828test_wait_close() { 829 case `uname` in MINGW*) return 77;; esac # TODO 830 831 ( 832 echo "select pg_backend_pid();" 833 sleep 3 834 echo "select pg_backend_pid();" 835 echo "\q" 836 ) | psql -X -tAq -f- -d p3 & 837 psql_pid=$! 838 sleep 1 839 admin "reconnect p3" 840 admin "wait_close p3" 841 sleep 1 # give psql a moment to exit 842 843 # psql should no longer be running now. (Without the 844 # wait_close it would still be running.) 845 kill -0 $psql_pid 846 psql_running=$? 847 848 wait 849 850 admin "show databases" 851 admin "show pools" 852 admin "show servers" 853 854 test $psql_running -ne 0 855} 856 857# test auth_user 858test_auth_user() { 859 $have_getpeereid || return 77 860 861 admin "set auth_type='md5'" 862 curuser=`psql -X -d "dbname=authdb user=someuser password=anypasswd" -tAq -c "select current_user;"` 863 echo "curuser=$curuser" 864 test "$curuser" = "someuser" || return 1 865 866 curuser2=`psql -X -d "dbname=authdb user=nouser password=anypasswd" -tAq -c "select current_user;"` 867 echo "curuser2=$curuser2" 868 test "$curuser2" = "" || return 1 869 870 curuser2=`psql -X -d "dbname=authdb user=someuser password=badpasswd" -tAq -c "select current_user;"` 871 echo "curuser2=$curuser2" 872 test "$curuser2" = "" || return 1 873 874 admin "show databases" 875 admin "show pools" 876 877 return 0 878} 879 880# test plain-text password authentication from PgBouncer to PostgreSQL server 881# 882# The PostgreSQL server no longer supports storing plain-text 883# passwords, so the server-side user actually uses md5 passwords in 884# this test case, but the communication is still in plain text. 885test_password_server() { 886 admin "set auth_type='trust'" 887 888 # good password from ini 889 psql -X -c "select 1" p4 || return 1 890 # bad password from ini 891 psql -X -c "select 2" p4x && return 1 892 893 # good password from auth_file 894 psql -X -c "select 1" p4y || return 1 895 # bad password from auth_file 896 psql -X -c "select 1" p4z && return 1 897 898 # long password from auth_file 899 psql -X -c "select 1" p4l || return 1 900 901 return 0 902} 903 904# test plain-text password authentication from client to PgBouncer 905test_password_client() { 906 $have_getpeereid || return 77 907 908 admin "set auth_type='plain'" 909 910 # test with users that have a plain-text password stored 911 912 # good password 913 PGPASSWORD=foo psql -X -U puser1 -c "select 1" p1 || return 1 914 # bad password 915 PGPASSWORD=wrong psql -X -U puser2 -c "select 2" p1 && return 1 916 # long password 917 PGPASSWORD=$long_password psql -X -U longpass -c "select 3" p1 || return 1 918 # too long password 919 PGPASSWORD=X$long_password psql -X -U longpass -c "select 4" p1 && return 1 920 921 # test with users that have an md5 password stored 922 923 # good password 924 PGPASSWORD=foo psql -X -U muser1 -c "select 1" p1 || return 1 925 # bad password 926 PGPASSWORD=wrong psql -X -U muser2 -c "select 2" p1 && return 1 927 928 # test with users that have a SCRAM password stored 929 930 # good password 931 PGPASSWORD=foo psql -X -U scramuser1 -c "select 1" p1 || return 1 932 # bad password 933 PGPASSWORD=wrong psql -X -U scramuser2 -c "select 2" p1 && return 1 934 935 admin "set auth_type='trust'" 936 937 return 0 938} 939 940# test md5 authentication from PgBouncer to PostgreSQL server 941test_md5_server() { 942 admin "set auth_type='trust'" 943 944 # good password from ini 945 psql -X -c "select 1" p5 || return 1 946 # bad password from ini 947 psql -X -c "select 2" p5x && return 1 948 949 # good password from auth_file 950 psql -X -c "select 1" p5y || return 1 951 # bad password from auth_file 952 psql -X -c "select 1" p5z && return 1 953 954 return 0 955} 956 957# test md5 authentication from client to PgBouncer 958test_md5_client() { 959 $have_getpeereid || return 77 960 961 admin "set auth_type='md5'" 962 963 # test with users that have a plain-text password stored 964 965 # good password 966 PGPASSWORD=foo psql -X -U puser1 -c "select 1" p1 || return 1 967 # bad password 968 PGPASSWORD=wrong psql -X -U puser2 -c "select 2" p1 && return 1 969 970 # test with users that have an md5 password stored 971 972 # good password 973 PGPASSWORD=foo psql -X -U muser1 -c "select 1" p1 || return 1 974 # bad password 975 PGPASSWORD=wrong psql -X -U muser2 -c "select 2" p1 && return 1 976 977 admin "set auth_type='trust'" 978 979 return 0 980} 981 982# test SCRAM authentication from PgBouncer to PostgreSQL server 983test_scram_server() { 984 $pg_supports_scram || return 77 985 986 admin "set auth_type='trust'" 987 988 # good password from ini 989 psql -X -c "select 1" p6 || return 1 990 # bad password from ini 991 psql -X -c "select 2" p6x && return 1 992 993 # good password from auth_file (fails: not supported with SCRAM) 994 psql -X -c "select 1" p6y && return 1 995 # bad password from auth_file 996 psql -X -c "select 1" p6z && return 1 997 998 return 0 999} 1000 1001# test SCRAM authentication from client to PgBouncer 1002test_scram_client() { 1003 $have_getpeereid || return 77 1004 $pg_supports_scram || return 77 1005 1006 admin "set auth_type='scram-sha-256'" 1007 1008 # test with users that have a plain-text password stored 1009 1010 # good password 1011 PGPASSWORD=foo psql -X -U puser1 -c "select 1" p1 || return 1 1012 # bad password 1013 PGPASSWORD=wrong psql -X -U puser2 -c "select 2" p1 && return 1 1014 1015 # test with users that have an md5 password stored (all fail) 1016 1017 # good password 1018 PGPASSWORD=foo psql -X -U muser1 -c "select 1" p1 && return 1 1019 # bad password 1020 PGPASSWORD=wrong psql -X -U muser2 -c "select 2" p1 && return 1 1021 1022 # test with users that have a SCRAM password stored 1023 1024 # good password 1025 PGPASSWORD=foo psql -X -U scramuser1 -c "select 1" p1 || return 1 1026 # bad password 1027 PGPASSWORD=wrong psql -X -U scramuser2 -c "select 2" p1 && return 1 1028 1029 # SCRAM should also work when auth_type is "md5" 1030 admin "set auth_type='md5'" 1031 1032 # good password 1033 PGPASSWORD=foo psql -X -U scramuser1 -c "select 1" p1 || return 1 1034 # bad password 1035 PGPASSWORD=wrong psql -X -U scramuser2 -c "select 2" p1 && return 1 1036 1037 admin "set auth_type='trust'" 1038 1039 return 0 1040} 1041 1042# test SCRAM authentication from client to PgBouncer and on to server 1043test_scram_both() { 1044 $have_getpeereid || return 77 1045 $pg_supports_scram || return 77 1046 1047 admin "set auth_type='scram-sha-256'" 1048 1049 # plain-text password in userlist.txt 1050 PGPASSWORD=baz psql -X -U scramuser3 -c "select 1" p61 || return 1 1051 1052 # SCRAM password in userlist.txt 1053 PGPASSWORD=foo psql -X -U scramuser1 -c "select 1" p62 || return 1 1054 1055 return 0 1056} 1057 1058# test that SCRAM authentication pass-through is preserved by online 1059# restart 1060# 1061# Note: coproc requires bash >=4 1062test_scram_takeover() { 1063 $have_getpeereid || return 77 1064 $pg_supports_scram || return 77 1065 1066 admin "set auth_type='scram-sha-256'" 1067 admin "set pool_mode=transaction" 1068 admin "set server_lifetime=3" 1069 1070 { coproc { PGPASSWORD=foo psql -X -U scramuser1 -f - -d p62; } >&3; } 3>&1 1071 1072 echo "select 1;" >&"${COPROC[1]}" 1073 sleep 4 # wait for server_lifetime 1074 1075 $BOUNCER_EXE -d -R $BOUNCER_INI 1076 sleep 1 1077 1078 echo "select 2;" >&"${COPROC[1]}" 1079 echo "\q" >&"${COPROC[1]}" 1080 1081 wait $COPROC_PID 1082 1083 test $? -eq 0 1084} 1085 1086# Several tests that check the behavior when connecting with a 1087# nonexistent user under various authentication types. Database p1 1088# has a forced user, p2 does not; these exercise slightly different 1089# code paths. 1090 1091test_no_user_trust() { 1092 admin "set auth_type='trust'" 1093 1094 psql -X -U nosuchuser1 -c "select 1" p2 && return 1 1095 grep -F "closing because: \"trust\" authentication failed" $BOUNCER_LOG || return 1 1096 1097 return 0 1098} 1099 1100test_no_user_trust_forced_user() { 1101 admin "set auth_type='trust'" 1102 1103 psql -X -U nosuchuser1 -c "select 1" p1 && return 1 1104 grep -F "closing because: \"trust\" authentication failed" $BOUNCER_LOG || return 1 1105 1106 return 0 1107} 1108 1109test_no_user_password() { 1110 $have_getpeereid || return 77 1111 1112 admin "set auth_type='plain'" 1113 1114 PGPASSWORD=whatever psql -X -U nosuchuser1 -c "select 1" p2 && return 1 1115 grep -F "no such user: nosuchuser1" $BOUNCER_LOG || return 1 1116 grep -F "closing because: password authentication failed" $BOUNCER_LOG || return 1 1117 1118 return 0 1119} 1120 1121test_no_user_password_forced_user() { 1122 $have_getpeereid || return 77 1123 1124 admin "set auth_type='plain'" 1125 1126 PGPASSWORD=whatever psql -X -U nosuchuser1 -c "select 1" p1 && return 1 1127 grep -F "no such user: nosuchuser1" $BOUNCER_LOG || return 1 1128 grep -F "closing because: password authentication failed" $BOUNCER_LOG || return 1 1129 1130 return 0 1131} 1132 1133test_no_user_md5() { 1134 $have_getpeereid || return 77 1135 1136 admin "set auth_type='md5'" 1137 1138 PGPASSWORD=whatever psql -X -U nosuchuser1 -c "select 1" p2 && return 1 1139 grep -F "no such user: nosuchuser1" $BOUNCER_LOG || return 1 1140 grep -F "closing because: password authentication failed" $BOUNCER_LOG || return 1 1141 1142 return 0 1143} 1144 1145test_no_user_md5_forced_user() { 1146 $have_getpeereid || return 77 1147 1148 admin "set auth_type='md5'" 1149 1150 PGPASSWORD=whatever psql -X -U nosuchuser1 -c "select 1" p1 && return 1 1151 grep -F "no such user: nosuchuser1" $BOUNCER_LOG || return 1 1152 grep -F "closing because: password authentication failed" $BOUNCER_LOG || return 1 1153 1154 return 0 1155} 1156 1157test_no_user_scram() { 1158 $have_getpeereid || return 77 1159 $pg_supports_scram || return 77 1160 1161 admin "set auth_type='scram-sha-256'" 1162 1163 PGPASSWORD=whatever psql -X -U nosuchuser1 -c "select 1" p2 && return 1 1164 grep -F "no such user: nosuchuser1" $BOUNCER_LOG || return 1 1165 grep -F "closing because: SASL authentication failed" $BOUNCER_LOG || return 1 1166 1167 return 0 1168} 1169 1170test_no_user_scram_forced_user() { 1171 $have_getpeereid || return 77 1172 $pg_supports_scram || return 77 1173 1174 admin "set auth_type='scram-sha-256'" 1175 1176 PGPASSWORD=whatever psql -X -U nosuchuser1 -c "select 1" p1 && return 1 1177 grep -F "no such user: nosuchuser1" $BOUNCER_LOG || return 1 1178 grep -F "closing because: SASL authentication failed" $BOUNCER_LOG || return 1 1179 1180 return 0 1181} 1182 1183test_no_user_auth_user() { 1184 $have_getpeereid || return 77 1185 1186 admin "set auth_type='md5'" 1187 1188 PGPASSWORD=whatever psql -X -U nosuchuser1 -c "select 1" authdb && return 1 1189 # Currently no mock authentication when using 1190 # auth_query/auth_user. See TODO in 1191 # handle_auth_query_response(). 1192 grep -F "closing because: no such user (age" $BOUNCER_LOG || return 1 1193 1194 return 0 1195} 1196 1197test_auto_database() { 1198 psql -X -d p7 -c "select current_database()" || return 1 1199 grep -F "registered new auto-database" $BOUNCER_LOG || return 1 1200 1201 return 0 1202} 1203 1204test_cancel() { 1205 case `uname` in MINGW*) return 77;; esac 1206 1207 psql -X -d p3 -c "select pg_sleep(20)" & 1208 psql_pid=$! 1209 sleep 1 1210 kill -INT $psql_pid 1211 wait $psql_pid 1212 test $? -ne 0 || return 1 1213 grep -F "canceling statement due to user request" $PG_LOG || return 1 1214 1215 return 0 1216} 1217 1218# Test for waiting connections handling for cancel requests. 1219# 1220# The bug fixed by GH PR #542 was: When the connection pool is full, 1221# cancel requests cannot get through (that is normal), but then when 1222# unused connections close and pool slots are available, those are not 1223# used for waiting cancel requests. 1224test_cancel_wait() { 1225 case `uname` in MINGW*) return 77;; esac 1226 1227 # default_pool_size=5 1228 admin "set server_idle_timeout=2" 1229 1230 psql -X -d p3 -c "select pg_sleep(20)" & 1231 psql_pid=$! 1232 psql -X -d p3 -c "select pg_sleep(2)" & 1233 psql -X -d p3 -c "select pg_sleep(2)" & 1234 psql -X -d p3 -c "select pg_sleep(2)" & 1235 psql -X -d p3 -c "select pg_sleep(2)" & 1236 sleep 1 1237 1238 # This cancel must wait for a pool slot to become free. 1239 kill -INT $psql_pid 1240 1241 wait $psql_pid 1242 1243 # Prior to the bug fix, the cancel would never get through and 1244 # the first psql would simply run the full sleep and exit 1245 # successfully. 1246 test $? -ne 0 || return 1 1247 grep -F "canceling statement due to user request" $PG_LOG || return 1 1248 1249 return 0 1250} 1251 1252# Test that cancel requests can exceed the pool size 1253# 1254# Cancel request connections can use twice the pool size. See also GH 1255# PR #543. 1256test_cancel_pool_size() { 1257 case `uname` in MINGW*) return 77;; esac 1258 1259 # default_pool_size=5 1260 admin "set server_idle_timeout=2" 1261 1262 psql -X -d p3 -c "select pg_sleep(20)" & 1263 psql1_pid=$! 1264 psql -X -d p3 -c "select pg_sleep(20)" & 1265 psql2_pid=$! 1266 psql -X -d p3 -c "select pg_sleep(20)" & 1267 psql3_pid=$! 1268 psql -X -d p3 -c "select pg_sleep(20)" & 1269 psql4_pid=$! 1270 psql -X -d p3 -c "select pg_sleep(20)" & 1271 psql5_pid=$! 1272 sleep 1 1273 1274 # These cancels requires more connections than the 1275 # default_pool_size=5. 1276 kill -INT $psql1_pid $psql2_pid $psql3_pid $psql4_pid $psql5_pid 1277 1278 wait $psql1_pid 1279 1280 # Prior to the change fix, the cancels would never get through 1281 # and the psql processes would simply run the full sleep and 1282 # exit successfully. 1283 test $? -ne 0 || return 1 1284 grep -F "canceling statement due to user request" $PG_LOG || return 1 1285 1286 return 0 1287} 1288 1289testlist=" 1290test_show_version 1291test_show 1292test_server_login_retry 1293test_auth_user 1294test_client_idle_timeout 1295test_server_lifetime 1296test_server_idle_timeout 1297test_query_timeout 1298test_idle_transaction_timeout 1299test_server_connect_timeout_establish 1300test_server_connect_timeout_reject 1301test_server_check_delay 1302test_tcp_user_timeout 1303test_max_client_conn 1304test_pool_size 1305test_min_pool_size 1306test_reserve_pool_size 1307test_max_db_connections 1308test_max_user_connections 1309test_online_restart 1310test_pause_resume 1311test_suspend_resume 1312test_enable_disable 1313test_database_restart 1314test_database_change 1315test_reconnect 1316test_fast_close 1317test_wait_close 1318test_password_server 1319test_password_client 1320test_md5_server 1321test_md5_client 1322test_scram_server 1323test_scram_client 1324test_scram_both 1325test_scram_takeover 1326test_no_user_trust 1327test_no_user_trust_forced_user 1328test_no_user_password 1329test_no_user_password_forced_user 1330test_no_user_md5 1331test_no_user_md5_forced_user 1332test_no_user_scram 1333test_no_user_scram_forced_user 1334test_no_user_auth_user 1335test_auto_database 1336test_cancel 1337test_cancel_wait 1338test_cancel_pool_size 1339" 1340 1341if [ $# -gt 0 ]; then 1342 testlist=$@ 1343fi 1344 1345total_status=0 1346for test in $testlist 1347do 1348 runtest $test 1349 status=$? 1350 if [ $status -ne 0 ]; then 1351 total_status=1 1352 fi 1353done 1354 1355complete 1356 1357exit $total_status 1358 1359# vim: sts=0 sw=8 noet nosmarttab: 1360