Skip to content

Commit 8fea868

Browse files
michaelpqmichaelpq
committed
Add support for regexps on database and user entries in pg_hba.conf
As of this commit, any database or user entry beginning with a slash (/) is considered as a regular expression. This is particularly useful for users, as now there is no clean way to match pattern on multiple HBA lines. For example, a user name mapping with a regular expression needs first to match with a HBA line, and we would skip the follow-up HBA entries if the ident regexp does *not* match with what has matched in the HBA line. pg_hba.conf is able to handle multiple databases and roles with a comma-separated list of these, hence individual regular expressions that include commas need to be double-quoted. At authentication time, user and database names are now checked in the following order: - Arbitrary keywords (like "all", the ones beginning by '+' for membership check), that we know will never have a regexp. A fancy case is for physical WAL senders, we *have* to only match "replication" for the database. - Regular expression matching. - Exact match. The previous logic did the same, but without the regexp step. We have discussed as well the possibility to support regexp pattern matching for host names, but these happen to lead to tricky issues based on what I understand, particularly with host entries that have CIDRs. This commit relies heavily on the refactoring done in a903971 and fc579e1, so as the amount of code required to compile and execute regular expressions is now minimal. When parsing pg_hba.conf, all the computed regexps needs to explicitely free()'d, same as pg_ident.conf. Documentation and TAP tests are added to cover this feature, including cases where the regexps use commas (for clarity in the docs, coverage for the parsing logic in the tests). Note that this introduces a breakage with older versions, where a database or user name beginning with a slash are treated as something to check for an equal match. Per discussion, we have discarded this as being much of an issue in practice as it would require a cluster to have database and/or role names that begin with a slash, as well as HBA entries using these. Hence, the consistency gained with regexps in pg_ident.conf is more appealing in the long term. **This compatibility change should be mentioned in the release notes.** Author: Bertrand Drouvot Reviewed-by: Jacob Champion, Tom Lane, Michael Paquier Discussion: https://postgr.es/m/fff0d7c1-8ad4-76a1-9db3-0ab6ec338bf7@amazon.com
1 parent 5035c93 commit 8fea868

File tree

3 files changed

+163
-21
lines changed

3 files changed

+163
-21
lines changed

‎doc/src/sgml/client-auth.sgml

+42-14
Original file line numberDiff line numberDiff line change
@@ -233,11 +233,20 @@ hostnogssenc <replaceable>database</replaceable> <replaceable>user</replaceabl
233233
doesn't match with logical replication connections. Note that physical
234234
replication connections do not specify any particular database whereas
235235
logical replication connections do specify it.
236-
Otherwise, this is the name of
237-
a specific <productname>PostgreSQL</productname> database.
238-
Multiple database names can be supplied by separating them with
239-
commas. A separate file containing database names can be specified by
240-
preceding the file name with <literal>@</literal>.
236+
Otherwise, this is the name of a specific
237+
<productname>PostgreSQL</productname> database or a regular expression.
238+
Multiple database names and/or regular expressions can be supplied by
239+
separating them with commas.
240+
</para>
241+
<para>
242+
If the database name starts with a slash (<literal>/</literal>), the
243+
remainder of the name is treated as a regular expression.
244+
(See <xref linkend="posix-syntax-details"/> for details of
245+
<productname>PostgreSQL</productname>'s regular expression syntax.)
246+
</para>
247+
<para>
248+
A separate file containing database names and/or regular expressions
249+
can be specified by preceding the file name with <literal>@</literal>.
241250
</para>
242251
</listitem>
243252
</varlistentry>
@@ -249,7 +258,8 @@ hostnogssenc <replaceable>database</replaceable> <replaceable>user</replaceabl
249258
Specifies which database user name(s) this record
250259
matches. The value <literal>all</literal> specifies that it
251260
matches all users. Otherwise, this is either the name of a specific
252-
database user, or a group name preceded by <literal>+</literal>.
261+
database user, a regular expression (when starting with a slash
262+
(<literal>/</literal>), or a group name preceded by <literal>+</literal>.
253263
(Recall that there is no real distinction between users and groups
254264
in <productname>PostgreSQL</productname>; a <literal>+</literal> mark really means
255265
<quote>match any of the roles that are directly or indirectly members
@@ -258,9 +268,18 @@ hostnogssenc <replaceable>database</replaceable> <replaceable>user</replaceabl
258268
considered to be a member of a role if they are explicitly a member
259269
of the role, directly or indirectly, and not just by virtue of
260270
being a superuser.
261-
Multiple user names can be supplied by separating them with commas.
262-
A separate file containing user names can be specified by preceding the
263-
file name with <literal>@</literal>.
271+
Multiple user names and/or regular expressions can be supplied by
272+
separating them with commas.
273+
</para>
274+
<para>
275+
If the user name starts with a slash (<literal>/</literal>), the
276+
remainder of the name is treated as a regular expression.
277+
(See <xref linkend="posix-syntax-details"/> for details of
278+
<productname>PostgreSQL</productname>'s regular expression syntax.)
279+
</para>
280+
<para>
281+
A separate file containing user names and/or regular expressions can
282+
be specified by preceding the file name with <literal>@</literal>.
264283
</para>
265284
</listitem>
266285
</varlistentry>
@@ -739,6 +758,14 @@ host all all ::1/128 trust
739758
# TYPE DATABASE USER ADDRESS METHOD
740759
host all all localhost trust
741760

761+
# The same using a regular expression for DATABASE, that allows connection
762+
# to the database db1, db2 and any databases with a name beginning by "db"
763+
# and finishing with a number using two to four digits (like "db1234" or
764+
# "db12").
765+
#
766+
# TYPE DATABASE USER ADDRESS METHOD
767+
local db1,"/^db\d{2,4}$",db2 all localhost trust
768+
742769
# Allow any user from any host with IP address 192.168.93.x to connect
743770
# to database "postgres" as the same user name that ident reports for
744771
# the connection (typically the operating system user name).
@@ -785,15 +812,16 @@ host all all 192.168.12.10/32 gss
785812
# TYPE DATABASE USER ADDRESS METHOD
786813
host all all 192.168.0.0/16 ident map=omicron
787814

788-
# If these are the only three lines for local connections, they will
815+
# If these are the only four lines for local connections, they will
789816
# allow local users to connect only to their own databases (databases
790-
# with the same name as their database user name) except for administrators
791-
# and members of role "support", who can connect to all databases. The file
792-
# $PGDATA/admins contains a list of names of administrators. Passwords
793-
# are required in all cases.
817+
# with the same name as their database user name) except for users whose
818+
# name end with "helpdesk", administrators and members of role "support",
819+
# who can connect to all databases. The file $PGDATA/admins contains a
820+
# list of names of administrators. Passwords are required in all cases.
794821
#
795822
# TYPE DATABASE USER ADDRESS METHOD
796823
local sameuser all md5
824+
local all /^.*helpdesk$ md5
797825
local all @admins md5
798826
local all +support md5
799827

‎src/backend/libpq/hba.c

+79-7
Original file line numberDiff line numberDiff line change
@@ -293,6 +293,30 @@ free_auth_token(AuthToken *token)
293293
pg_regfree(token->regex);
294294
}
295295

296+
/*
297+
* Free a HbaLine. Its list of AuthTokens for databases and roles may include
298+
* regular expressions that need to be cleaned up explicitly.
299+
*/
300+
static void
301+
free_hba_line(HbaLine *line)
302+
{
303+
ListCell *cell;
304+
305+
foreach(cell, line->roles)
306+
{
307+
AuthToken *tok = lfirst(cell);
308+
309+
free_auth_token(tok);
310+
}
311+
312+
foreach(cell, line->databases)
313+
{
314+
AuthToken *tok = lfirst(cell);
315+
316+
free_auth_token(tok);
317+
}
318+
}
319+
296320
/*
297321
* Copy a AuthToken struct into freshly palloc'd memory.
298322
*/
@@ -661,6 +685,10 @@ is_member(Oid userid, const char *role)
661685

662686
/*
663687
* Check AuthToken list for a match to role, allowing group names.
688+
*
689+
* Each AuthToken listed is checked one-by-one. Keywords are processed
690+
* first (these cannot have regular expressions), followed by regular
691+
* expressions (if any) and the exact match.
664692
*/
665693
static bool
666694
check_role(const char *role, Oid roleid, List *tokens)
@@ -676,15 +704,25 @@ check_role(const char *role, Oid roleid, List *tokens)
676704
if (is_member(roleid, tok->string + 1))
677705
return true;
678706
}
679-
else if (token_matches(tok, role) ||
680-
token_is_keyword(tok, "all"))
707+
else if (token_is_keyword(tok, "all"))
708+
return true;
709+
else if (token_has_regexp(tok))
710+
{
711+
if (regexec_auth_token(role, tok, 0, NULL) == REG_OKAY)
712+
return true;
713+
}
714+
else if (token_matches(tok, role))
681715
return true;
682716
}
683717
return false;
684718
}
685719

686720
/*
687721
* Check to see if db/role combination matches AuthToken list.
722+
*
723+
* Each AuthToken listed is checked one-by-one. Keywords are checked
724+
* first (these cannot have regular expressions), followed by regular
725+
* expressions (if any) and the exact match.
688726
*/
689727
static bool
690728
check_db(const char *dbname, const char *role, Oid roleid, List *tokens)
@@ -719,6 +757,11 @@ check_db(const char *dbname, const char *role, Oid roleid, List *tokens)
719757
}
720758
else if (token_is_keyword(tok, "replication"))
721759
continue; /* never match this if not walsender */
760+
else if (token_has_regexp(tok))
761+
{
762+
if (regexec_auth_token(dbname, tok, 0, NULL) == REG_OKAY)
763+
return true;
764+
}
722765
else if (token_matches(tok, dbname))
723766
return true;
724767
}
@@ -1138,8 +1181,13 @@ parse_hba_line(TokenizedAuthLine *tok_line, int elevel)
11381181
tokens = lfirst(field);
11391182
foreach(tokencell, tokens)
11401183
{
1141-
parsedline->databases = lappend(parsedline->databases,
1142-
copy_auth_token(lfirst(tokencell)));
1184+
AuthToken *tok = copy_auth_token(lfirst(tokencell));
1185+
1186+
/* Compile a regexp for the database token, if necessary */
1187+
if (regcomp_auth_token(tok, HbaFileName, line_num, err_msg, elevel))
1188+
return NULL;
1189+
1190+
parsedline->databases = lappend(parsedline->databases, tok);
11431191
}
11441192

11451193
/* Get the roles. */
@@ -1158,8 +1206,13 @@ parse_hba_line(TokenizedAuthLine *tok_line, int elevel)
11581206
tokens = lfirst(field);
11591207
foreach(tokencell, tokens)
11601208
{
1161-
parsedline->roles = lappend(parsedline->roles,
1162-
copy_auth_token(lfirst(tokencell)));
1209+
AuthToken *tok = copy_auth_token(lfirst(tokencell));
1210+
1211+
/* Compile a regexp from the role token, if necessary */
1212+
if (regcomp_auth_token(tok, HbaFileName, line_num, err_msg, elevel))
1213+
return NULL;
1214+
1215+
parsedline->roles = lappend(parsedline->roles, tok);
11631216
}
11641217

11651218
if (parsedline->conntype != ctLocal)
@@ -2355,12 +2408,31 @@ load_hba(void)
23552408

23562409
if (!ok)
23572410
{
2358-
/* File contained one or more errors, so bail out */
2411+
/*
2412+
* File contained one or more errors, so bail out, first being careful
2413+
* to clean up whatever we allocated. Most stuff will go away via
2414+
* MemoryContextDelete, but we have to clean up regexes explicitly.
2415+
*/
2416+
foreach(line, new_parsed_lines)
2417+
{
2418+
HbaLine *newline = (HbaLine *) lfirst(line);
2419+
2420+
free_hba_line(newline);
2421+
}
23592422
MemoryContextDelete(hbacxt);
23602423
return false;
23612424
}
23622425

23632426
/* Loaded new file successfully, replace the one we use */
2427+
if (parsed_hba_lines != NIL)
2428+
{
2429+
foreach(line, parsed_hba_lines)
2430+
{
2431+
HbaLine *newline = (HbaLine *) lfirst(line);
2432+
2433+
free_hba_line(newline);
2434+
}
2435+
}
23642436
if (parsed_hba_context != NULL)
23652437
MemoryContextDelete(parsed_hba_context);
23662438
parsed_hba_context = hbacxt;

‎src/test/authentication/t/001_password.pl

+42
Original file line numberDiff line numberDiff line change
@@ -81,6 +81,14 @@ sub test_conn
8181
GRANT ALL ON sysuser_data TO md5_role;");
8282
$ENV{"PGPASSWORD"} = 'pass';
8383

84+
# Create a role that contains a comma to stress the parsing.
85+
$node->safe_psql('postgres',
86+
q{SET password_encryption='md5'; CREATE ROLE "md5,role" LOGIN PASSWORD 'pass';}
87+
);
88+
89+
# Create a database to test regular expression.
90+
$node->safe_psql('postgres', "CREATE database regex_testdb;");
91+
8492
# For "trust" method, all users should be able to connect. These users are not
8593
# considered to be authenticated.
8694
reset_pg_hba($node, 'all', 'all', 'trust');
@@ -200,6 +208,40 @@ sub test_conn
200208

201209
test_conn($node, 'user=md5_role', 'password from pgpass', 0);
202210

211+
# Testing with regular expression for username. The third regexp matches.
212+
reset_pg_hba($node, 'all', '/^.*nomatch.*$, baduser, /^md.*$', 'password');
213+
test_conn($node, 'user=md5_role', 'password, matching regexp for username',
214+
0);
215+
216+
# The third regex does not match anymore.
217+
reset_pg_hba($node, 'all', '/^.*nomatch.*$, baduser, /^m_d.*$', 'password');
218+
test_conn($node, 'user=md5_role',
219+
'password, non matching regexp for username',
220+
2, log_unlike => [qr/connection authenticated:/]);
221+
222+
# Test with a comma in the regular expression. In this case, the use of
223+
# double quotes is mandatory so as this is not considered as two elements
224+
# of the user name list when parsing pg_hba.conf.
225+
reset_pg_hba($node, 'all', '"/^.*5,.*e$"', 'password');
226+
test_conn($node, 'user=md5,role', 'password', 'matching regexp for username',
227+
0);
228+
229+
# Testing with regular expression for dbname. The third regex matches.
230+
reset_pg_hba($node, '/^.*nomatch.*$, baddb, /^regex_t.*b$', 'all',
231+
'password');
232+
test_conn(
233+
$node, 'user=md5_role dbname=regex_testdb', 'password,
234+
matching regexp for dbname', 0);
235+
236+
# The third regexp does not match anymore.
237+
reset_pg_hba($node, '/^.*nomatch.*$, baddb, /^regex_t.*ba$',
238+
'all', 'password');
239+
test_conn(
240+
$node,
241+
'user=md5_role dbname=regex_testdb',
242+
'password, non matching regexp for dbname',
243+
2, log_unlike => [qr/connection authenticated:/]);
244+
203245
unlink($pgpassfile);
204246
delete $ENV{"PGPASSFILE"};
205247

0 commit comments

Comments
 (0)