Thread: Q: pg_hba.conf separate database names file format

Hi,

according to the docs, the database specification in the pg_hba.conf file can be read from a separate file
(<https://www.postgresql.org/docs/current/auth-pg-hba-conf.html>:“A separate file containing database names can be
specifiedby preceding the file name with @.”) 

However, I could not find a specification of the format for this file…  It appears as if simply giving each database
nameon a separate line does the job.  Is this correct?  May the file contain comments (i.e. lines starting with “#”) or
emptylines?  May the file be specified including a path (e.g. “@/some/path/databases”)? 

IMHO, it might be helpful if this information would be added to the docs.

Thanks, Albrecht.

On Tue, Nov 08, 2022 at 02:16:03PM +0100, Albrecht Dreß wrote:
> However, I could not find a specification of the format for this
> file…  It appears as if simply giving each database name on a
> separate line does the job.  Is this correct?  May the file contain
> comments (i.e. lines starting with “#”) or empty lines?  May the
> file be specified including a path (e.g. “@/some/path/databases”)?

I have been playing with this code for the last couple of days, and
the answer is that you can use an absolute path or a relative path.
In the case of a relative path, the code considers the base directory
as the parent directory of the file this is included in.  For example,
/data/pg/pg_hba.conf including a @databases.conf resolves as
/data/pg/databases.conf, and a @conf/databases.conf resolves as
/data/pg/conf/databases.conf.

The parsing of these files uses the same rules as what's done for
pg_hba.conf and pg_ident.conf, so you can specify a list of
user names separated by commas or even spaces, or put one name per
line.  Comments beginning with '#' are ignored.

If you want to play with your file and see the results, I would
recommend to tweak the files, and then look at the contents generated
in the system view pg_hba_file_rules.  Querying pg_hba_file_rules
loads directly the configuration files from disk, so there is no need
to reload or restart the server to see the effects any modifications
would have.

The documentation has already some descriptions, that you've missed,
perhaps:
https://www.postgresql.org/docs/15/auth-pg-hba-conf.html
"Files included by @ constructs are read as lists of names, which can
be separated by either whitespace or commas. Comments are introduced
by #, just as in pg_hba.conf, and nested @ constructs are
allowed. Unless the file name following @ is an absolute path, it is
taken to be relative to the directory containing the referencing
file."
--
Michael

On 11/8/22 23:19, Michael Paquier wrote:

On Tue, Nov 08, 2022 at 02:16:03PM +0100, Albrecht Dreß wrote:

However, I could not find a specification of the format for this
file…  It appears as if simply giving each database name on a
separate line does the job.  Is this correct?  May the file contain
comments (i.e. lines starting with “#”) or empty lines?  May the
file be specified including a path (e.g. “@/some/path/databases”)?

I have been playing with this code for the last couple of days, and
the answer is that you can use an absolute path or a relative path.
In the case of a relative path, the code considers the base directory
as the parent directory of the file this is included in.  For example,
/data/pg/pg_hba.conf including a @databases.conf resolves as
/data/pg/databases.conf, and a @conf/databases.conf resolves as
/data/pg/conf/databases.conf.

The parsing of these files uses the same rules as what's done for
pg_hba.conf and pg_ident.conf, so you can specify a list of
user names separated by commas or even spaces, or put one name per
line.  Comments beginning with '#' are ignored.

If you want to play with your file and see the results, I would
recommend to tweak the files, and then look at the contents generated
in the system view pg_hba_file_rules.  Querying pg_hba_file_rules
loads directly the configuration files from disk, so there is no need
to reload or restart the server to see the effects any modifications
would have.

The documentation has already some descriptions, that you've missed,
perhaps:
https://www.postgresql.org/docs/15/auth-pg-hba-conf.html
"Files included by @ constructs are read as lists of names, which can
be separated by either whitespace or commas. Comments are introduced
by #, just as in pg_hba.conf, and nested @ constructs are
allowed. Unless the file name following @ is an absolute path, it is
taken to be relative to the directory containing the referencing
file."

Are these "include" files supposed to solve the problem of having a lot of databases (or users) that you want to allow access to?

On Wed, Nov 09, 2022 at 04:02:43AM -0600, Ron wrote:
> Are these "include" files supposed to solve the problem of having a *lot* of
> databases (or users) that you want to allow access to?

Yes, splitting the list of users and database eases the maintenance
and readability of pg_hba.conf as each HBA entry could get quite
long depending on the connection policy you may want.  My take would
be to use one entry per line in an @ file in this case.
--
Michael