Date: Sun, 9 Oct 2022 14:13:48 -0700
From: Nathan Bossart <nathandbossart@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Joe Conway <mail@joeconway.com>,
	Joshua Brindle <joshua.brindle@crunchydata.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: use has_privs_of_role() for pg_hba.conf
Message-ID: <20221009211348.GB900071@nathanxps13>
References: <Yz/Ii6dr1CSu4Bnv@paquier.xyz>
 <20221007194430.GA550749@nathanxps13>
 <3347532.1665173939@sss.pgh.pa.us>
 <20221007215836.GB814385@nathanxps13>
 <87994809-5b2c-08f0-0ab3-3c76c49afada@joeconway.com>
 <3574712.1665242046@sss.pgh.pa.us>
 <CA+TgmoYDUpotJ3R5cPWqKZpi7TmTRq2ud4OYsvWnvPYK0KmaAQ@mail.gmail.com>
 <CAKFQuwbbR==cUhGpv0+G=z1mgKS+8ysdtMqSgt4mMvGwAyYYQg@mail.gmail.com>
 <20221008171222.GB825354@nathanxps13>
 <Y0Iht953YoGpCXeb@paquier.xyz>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="liOOAslEiF7prFVr"
Content-Disposition: inline
In-Reply-To: <Y0Iht953YoGpCXeb@paquier.xyz>
Archived-At: 
 <https://www.postgresql.org/message-id/20221009211348.GB900071%40nathanxps13>
Precedence: bulk


--liOOAslEiF7prFVr
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Sun, Oct 09, 2022 at 10:19:51AM +0900, Michael Paquier wrote:
> Even if the patch is at the end rejected, I think that the test is
> still useful once you switch its logic to use membership and not
> inherited privileges for the roles created, and there is zero coverage
> for "samplegroup" and its kind currently.

Here you go.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

--liOOAslEiF7prFVr
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="hba_test.patch"

diff --git a/src/test/authentication/t/001_password.pl b/src/test/authentication/t/001_password.pl
index 93df77aa4e..5d4b30e5da 100644
--- a/src/test/authentication/t/001_password.pl
+++ b/src/test/authentication/t/001_password.pl
@@ -200,4 +200,52 @@ append_to_file(
 
 test_conn($node, 'user=md5_role', 'password from pgpass', 0);
 
+unlink($pgpassfile);
+delete $ENV{"PGPASSFILE"};
+
+# Create database and roles for inheritance tests
+reset_pg_hba($node, 'all', 'all', 'trust');
+$node->safe_psql('postgres', "CREATE DATABASE regress_regression_group;");
+$node->safe_psql('postgres', "CREATE ROLE regress_regression_group LOGIN PASSWORD 'pass';");
+$node->safe_psql('postgres', "CREATE ROLE regress_member LOGIN SUPERUSER IN ROLE regress_regression_group PASSWORD 'pass';");
+$node->safe_psql('postgres', "CREATE ROLE regress_not_member LOGIN SUPERUSER PASSWORD 'pass';");
+
+# Test role membership is respected for +
+$ENV{"PGPASSWORD"} = 'pass';
+reset_pg_hba($node, 'all', '+regress_regression_group', 'scram-sha-256');
+test_conn($node, 'user=regress_regression_group', 'scram-sha-256', 0,
+	log_like =>
+	  [qr/connection authenticated: identity="regress_regression_group" method=scram-sha-256/]);
+test_conn($node, 'user=regress_member', 'scram-sha-256', 0,
+	log_like =>
+	  [qr/connection authenticated: identity="regress_member" method=scram-sha-256/]);
+test_conn($node, 'user=regress_not_member', 'scram-sha-256', 2,
+	log_unlike =>
+	  [qr/connection authenticated: identity="regress_not_member" method=scram-sha-256/]);
+
+# Test role membership is respected for samerole
+$ENV{"PGDATABASE"} = 'regress_regression_group';
+reset_pg_hba($node, 'samerole', 'all', 'scram-sha-256');
+test_conn($node, 'user=regress_regression_group', 'scram-sha-256', 0,
+	log_like =>
+	  [qr/connection authenticated: identity="regress_regression_group" method=scram-sha-256/]);
+test_conn($node, 'user=regress_member', 'scram-sha-256', 0,
+	log_like =>
+	  [qr/connection authenticated: identity="regress_member" method=scram-sha-256/]);
+test_conn($node, 'user=regress_not_member', 'scram-sha-256', 2,
+	log_unlike =>
+	  [qr/connection authenticated: identity="regress_not_member" method=scram-sha-256/]);
+
+# Test role membership is respected for samegroup
+reset_pg_hba($node, 'samegroup', 'all', 'scram-sha-256');
+test_conn($node, 'user=regress_regression_group', 'scram-sha-256', 0,
+	log_like =>
+	  [qr/connection authenticated: identity="regress_regression_group" method=scram-sha-256/]);
+test_conn($node, 'user=regress_member', 'scram-sha-256', 0,
+	log_like =>
+	  [qr/connection authenticated: identity="regress_member" method=scram-sha-256/]);
+test_conn($node, 'user=regress_not_member', 'scram-sha-256', 2,
+	log_unlike =>
+	  [qr/connection authenticated: identity="regress_not_member" method=scram-sha-256/]);
+
 done_testing();

--liOOAslEiF7prFVr--