supabase-postgres.git / summary / log / commit / refs

commit    1ffe2e3fb47e7f3eb7b214c7ee6a3ea117ea83c3
Author:   Kang Ming <kang.ming1996@gmail.com>
Commit:   GitHub <noreply@github.com>
Date:     Wed Apr 23 23:37:12 2025 +0000

    chore: add test for pgbouncer.get_auth (#1567)


 ansible/vars.yml                 |  6 +++---
 nix/tests/expected/pgbouncer.out | 21 ++++++++++++++++++++-
 nix/tests/sql/pgbouncer.sql      | 15 ++++++++++++++-
 3 files changed, 37 insertions(+), 5 deletions(-)

diff --git a/ansible/vars.yml b/ansible/vars.yml
index 019f1927..735e0892 100644
--- a/ansible/vars.yml
+++ b/ansible/vars.yml
@@ -9,9 +9,9 @@ postgres_major:
 
 # Full version strings for each major version
 postgres_release:
-  postgresorioledb-17: "17.0.1.068-orioledb"
-  postgres17: "17.4.1.018"
-  postgres15: "15.8.1.075"
+  postgresorioledb-17: "17.0.1.069-orioledb"
+  postgres17: "17.4.1.019"
+  postgres15: "15.8.1.076"
 
 # Non Postgres Extensions
 pgbouncer_release: "1.19.0"
diff --git a/nix/tests/expected/pgbouncer.out b/nix/tests/expected/pgbouncer.out
index 17f2c629..83845cb5 100644
--- a/nix/tests/expected/pgbouncer.out
+++ b/nix/tests/expected/pgbouncer.out
@@ -58,7 +58,7 @@ SELECT
 FROM pg_proc p 
 JOIN schema_obj s ON s.oid = p.pronamespace 
 CROSS JOIN LATERAL aclexplode(p.proacl) AS acl 
-ORDER BY object_name, grantee, privilege_type; 
+ORDER BY object_name, grantee, privilege_type;
   schema   | object_name |    grantee     | privilege_type 
 -----------+-------------+----------------+----------------
  pgbouncer | get_auth    | pgbouncer      | EXECUTE
@@ -66,3 +66,22 @@ ORDER BY object_name, grantee, privilege_type;
  pgbouncer | get_auth    | supabase_admin | EXECUTE
 (3 rows)
 
+-- Ensure that pgbouncer.get_auth() function does not return an expired password
+create role test_expired_user_password with login password 'expired_password' valid until '2000-01-01 00:00:00+00';
+create role test_valid_user_password with login password 'valid_password' valid until '2100-01-01 00:00:00+00';
+-- Update the pg_authid catalog directly to replace with a known SCRAM hash
+update pg_authid set rolpassword = 'SCRAM-SHA-256$4096:testsaltbase64$storedkeybase64$serverkeybase64' where rolname = 'test_valid_user_password';
+select pgbouncer.get_auth('test_expired_user_password');
+           get_auth            
+-------------------------------
+ (test_expired_user_password,)
+(1 row)
+
+select pgbouncer.get_auth('test_valid_user_password');
+                                           get_auth                                           
+----------------------------------------------------------------------------------------------
+ (test_valid_user_password,SCRAM-SHA-256$4096:testsaltbase64$storedkeybase64$serverkeybase64)
+(1 row)
+
+drop role test_expired_user_password;
+drop role test_valid_user_password;
diff --git a/nix/tests/sql/pgbouncer.sql b/nix/tests/sql/pgbouncer.sql
index 4ddac10e..e6b56cb6 100644
--- a/nix/tests/sql/pgbouncer.sql
+++ b/nix/tests/sql/pgbouncer.sql
@@ -50,4 +50,17 @@ SELECT
 FROM pg_proc p 
 JOIN schema_obj s ON s.oid = p.pronamespace 
 CROSS JOIN LATERAL aclexplode(p.proacl) AS acl 
-ORDER BY object_name, grantee, privilege_type; 
+ORDER BY object_name, grantee, privilege_type;
+
+-- Ensure that pgbouncer.get_auth() function does not return an expired password
+create role test_expired_user_password with login password 'expired_password' valid until '2000-01-01 00:00:00+00';
+create role test_valid_user_password with login password 'valid_password' valid until '2100-01-01 00:00:00+00';
+-- Update the pg_authid catalog directly to replace with a known SCRAM hash
+update pg_authid set rolpassword = 'SCRAM-SHA-256$4096:testsaltbase64$storedkeybase64$serverkeybase64' where rolname = 'test_valid_user_password';
+
+select pgbouncer.get_auth('test_expired_user_password');
+
+select pgbouncer.get_auth('test_valid_user_password');
+
+drop role test_expired_user_password;
+drop role test_valid_user_password;


[parent: 8d32169739d2]