[Drouvot, Bertrand <bdrouvot@amazon.com>]

Hi hackers,

Attached is a patch proposal to allow the use of regular expressions for 
the username in pg_hba.conf.

Using regular expressions for the username in the pg_hba.conf file is 
convenient in situations where an organization has a large number of 
users and needs an expressive way to map them.

For example, if an organization wants to allow gss connections only for 
users having their principal, e.g. @BDTFOREST.LOCAL, they could make use 
of an entry in pg_hba.conf such as:

host all /^.*@BDTFOREST.LOCAL$ 0.0.0.0/0 gss

Without this patch, I can think of three alternatives with existing 
functionality, which all of tradeoffs. This includes:

1) Create an entry per user: this is challenging for organizations 
managing large numbers of users (e.g. 1000s). This is also not dynamic, 
i.e. the HBA file would need to be updated when users are added or removed.

2) Use a mapping in pg_ident.conf, for example:

Here is an entry in pg_hba.conf that uses a map:

host all all 0.0.0.0/0 gss map=mygssmap

and by defining this mapping in pg_ident.conf:

mygssmap /^(.*)@BDTFOREST\.LOCAL$ \1@BDTFOREST.LOCAL

That works for filtering the username.

LOG:  connection authenticated: identity="bertrand@BDTFOREST.LOCAL" 
method=gss (/pg_installed/data/pg_hba.conf:95)
$ grep -n mygssmap /pg_installed/data/pg_hba.conf
95:host    all             all  0.0.0.0/0  gss map=mygssmap

However, the behavior is not the same for the ones that don’t match the 
mapping in pg_ident.conf: indeed the connection attempt stop here and 
the next HBA line won’t be evaluated.

FATAL:  GSSAPI authentication failed for user "bdt"
DETAIL:  Connection matched pg_hba.conf line 95: "host all             
all  0.0.0.0/0  gss map=mygssmap"

3) Make use of a role in pg_hba.conf, e.g. “+BDTONLY”. That would work 
too, and also allow the evaluation of the next HBA line for the ones 
that are not part of the role.

However:

     - That’s not as dynamic as the regular expression, as new users 
would need to be granted the role and some users who are moving in the 
company may need to have the role revoked.
     - Looking at the regular expression in the HBA file makes it clear 
what filtering needs to be done. This is not obvious when looking at the 
role, even if it has a meaningful name. This can generate “incorrect 
filtering” should one user be granted the role by mistake, or make it 
more difficult to debug why a user is not being matched to a particular 
line in the HBA file.

This is why I think username filtering with regular expressions would 
provide its own advantages.

Thoughts? Looking forward to your feedback,

Regards,

-- 
Bertrand Drouvot
Amazon Web Services: https://aws.amazon.com

Attachments:

v1-0001-hba_with_regexp.patch (text/plain, 7.4K) download
diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml
index 433759928b..f3d0be260c 100644
--- a/doc/src/sgml/client-auth.sgml
+++ b/doc/src/sgml/client-auth.sgml
@@ -249,7 +249,8 @@ hostnogssenc  <replaceable>database</replaceable>  <replaceable>user</replaceabl
        Specifies which database user name(s) this record
        matches. The value <literal>all</literal> specifies that it
        matches all users.  Otherwise, this is either the name of a specific
-       database user, or a group name preceded by <literal>+</literal>.
+       database user, a regular expression preceded by <literal>/</literal>
+       or a group name preceded by <literal>+</literal>.
        (Recall that there is no real distinction between users and groups
        in <productname>PostgreSQL</productname>; a <literal>+</literal> mark really means
        <quote>match any of the roles that are directly or indirectly members
@@ -787,16 +788,18 @@ host    all             all             192.168.12.10/32        gss
 # TYPE  DATABASE        USER            ADDRESS                 METHOD
 host    all             all             192.168.0.0/16          ident map=omicron
 
-# If these are the only three lines for local connections, they will
+# If these are the only four lines for local connections, they will
 # allow local users to connect only to their own databases (databases
-# with the same name as their database user name) except for administrators
-# and members of role "support", who can connect to all databases.  The file
-# $PGDATA/admins contains a list of names of administrators.  Passwords
+# with the same name as their database user name) except for administrators,
+# users finishing with "helpdesk" and members of role "support",
+# who can connect to all databases.
+# The file$PGDATA/admins contains a list of names of administrators.  Passwords
 # are required in all cases.
 #
 # TYPE  DATABASE        USER            ADDRESS                 METHOD
 local   sameuser        all                                     md5
 local   all             @admins                                 md5
+local   all             /^.*helpdesk$                           md5
 local   all             +support                                md5
 
 # The last two lines above can be combined into a single line:
diff --git a/src/backend/libpq/hba.c b/src/backend/libpq/hba.c
index 1447588c4a..e5b95eebf6 100644
--- a/src/backend/libpq/hba.c
+++ b/src/backend/libpq/hba.c
@@ -574,7 +574,8 @@ is_member(Oid userid, const char *role)
 }
 
 /*
- * Check AuthToken list for a match to role, allowing group names.
+ * Check AuthToken list for a match to role.
+ * We are allowing group names and regular expressions.
  */
 static bool
 check_role(const char *role, Oid roleid, List *tokens)
@@ -590,6 +591,56 @@ check_role(const char *role, Oid roleid, List *tokens)
 			if (is_member(roleid, tok->string + 1))
 				return true;
 		}
+		else if (!tok->quoted && tok->string[0] == '/')
+		{
+			/*
+			 * When tok->string starts with a slash, treat it as a regular
+			 * expression.
+			 */
+			int			r;
+			pg_wchar   *wstr;
+			int			wlen;
+			regex_t		re;
+
+			wstr = palloc((strlen(tok->string + 1) + 1) * sizeof(pg_wchar));
+			wlen = pg_mb2wchar_with_len(tok->string + 1,
+										wstr, strlen(tok->string + 1));
+
+			r = pg_regcomp(&re, wstr, wlen, REG_ADVANCED, C_COLLATION_OID);
+			if (r)
+			{
+				char		errstr[100];
+
+				pg_regerror(r, &re, errstr, sizeof(errstr));
+				ereport(LOG,
+						(errcode(ERRCODE_INVALID_REGULAR_EXPRESSION),
+						 errmsg("invalid regular expression \"%s\": %s",
+								tok->string + 1, errstr)));
+
+				pfree(wstr);
+				return false;
+			}
+			else
+			{
+				pg_wchar   *wrolestr;
+				int			wrolelen;
+
+				pfree(wstr);
+				wrolestr = palloc((strlen(role) + 1) * sizeof(pg_wchar));
+				wrolelen = pg_mb2wchar_with_len(role, wrolestr, strlen(role));
+
+				if (pg_regexec(&re, wrolestr, wrolelen, 0, NULL, 0, NULL, 0) == REG_OKAY)
+				{
+					pfree(wrolestr);
+					return true;
+				}
+				else
+				{
+					pfree(wrolestr);
+					return false;
+				}
+			}
+		}
 		else if (token_matches(tok, role) ||
 				 token_is_keyword(tok, "all"))
 			return true;
diff --git a/src/test/authentication/t/001_password.pl b/src/test/authentication/t/001_password.pl
index 3e3079c824..09439fe8e4 100644
--- a/src/test/authentication/t/001_password.pl
+++ b/src/test/authentication/t/001_password.pl
@@ -23,12 +23,13 @@ if (!$use_unix_sockets)
 # and then execute a reload to refresh it.
 sub reset_pg_hba
 {
-	my $node       = shift;
-	my $hba_method = shift;
+	my $node       		= shift;
+	my $hba_user_name 	= shift;
+	my $hba_method 		= shift;
 
 	unlink($node->data_dir . '/pg_hba.conf');
 	# just for testing purposes, use a continuation line
-	$node->append_conf('pg_hba.conf', "local all all\\\n $hba_method");
+	$node->append_conf('pg_hba.conf', "local all $hba_user_name\\\n $hba_method");
 	$node->reload;
 	return;
 }
@@ -76,14 +77,14 @@ $ENV{"PGPASSWORD"} = 'pass';
 
 # For "trust" method, all users should be able to connect. These users are not
 # considered to be authenticated.
-reset_pg_hba($node, 'trust');
+reset_pg_hba($node, 'all', 'trust');
 test_role($node, 'scram_role', 'trust', 0,
 	log_unlike => [qr/connection authenticated:/]);
 test_role($node, 'md5_role', 'trust', 0,
 	log_unlike => [qr/connection authenticated:/]);
 
 # For plain "password" method, all users should also be able to connect.
-reset_pg_hba($node, 'password');
+reset_pg_hba($node, 'all', 'password');
 test_role($node, 'scram_role', 'password', 0,
 	log_like =>
 	  [qr/connection authenticated: identity="scram_role" method=password/]);
@@ -92,7 +93,7 @@ test_role($node, 'md5_role', 'password', 0,
 	  [qr/connection authenticated: identity="md5_role" method=password/]);
 
 # For "scram-sha-256" method, user "scram_role" should be able to connect.
-reset_pg_hba($node, 'scram-sha-256');
+reset_pg_hba($node, 'all', 'scram-sha-256');
 test_role(
 	$node,
 	'scram_role',
@@ -112,7 +113,7 @@ $ENV{"PGPASSWORD"} = 'pass';
 
 # For "md5" method, all users should be able to connect (SCRAM
 # authentication will be performed for the user with a SCRAM secret.)
-reset_pg_hba($node, 'md5');
+reset_pg_hba($node, 'all', 'md5');
 test_role($node, 'scram_role', 'md5', 0,
 	log_like =>
 	  [qr/connection authenticated: identity="scram_role" method=md5/]);
@@ -122,11 +123,11 @@ test_role($node, 'md5_role', 'md5', 0,
 
 # Tests for channel binding without SSL.
 # Using the password authentication method; channel binding can't work
-reset_pg_hba($node, 'password');
+reset_pg_hba($node, 'all', 'password');
 $ENV{"PGCHANNELBINDING"} = 'require';
 test_role($node, 'scram_role', 'scram-sha-256', 2);
 # SSL not in use; channel binding still can't work
-reset_pg_hba($node, 'scram-sha-256');
+reset_pg_hba($node, 'all', 'scram-sha-256');
 $ENV{"PGCHANNELBINDING"} = 'require';
 test_role($node, 'scram_role', 'scram-sha-256', 2);
 
@@ -145,7 +146,7 @@ append_to_file(
 !);
 chmod 0600, $pgpassfile or die;
 
-reset_pg_hba($node, 'password');
+reset_pg_hba($node, 'all', 'password');
 test_role($node, 'scram_role', 'password from pgpass', 0);
 test_role($node, 'md5_role',   'password from pgpass', 2);
 
@@ -156,4 +157,8 @@ append_to_file(
 
 test_role($node, 'md5_role', 'password from pgpass', 0);
 
+# Testing with regular expression for username
+reset_pg_hba($node, '/^.*md.*$', 'password');
+test_role($node, 'md5_role', 'password from pgpass and regular expression for username', 0);
+
 done_testing();