Proposal: new file format for hba/ident/hosts configuration?

Hello hackers,

Currently the hba/ident/hosts configuration files follow similar, but
subtly different, whitespace-separated formats. While this works,
there are several problems with it:

1. it is a PostgreSQL-specific format, without commonly available parsers.
2. extensibility is limited, which becomes clearly visible in multiple
discussions/patch proposals. Two recent examples are:
2.1. the OAuth validators and hba[1]: validators often require extra
fields, and while they can add GUCs, those are global. In a
multi-tenant application, different hba lines might require different
validator configuration. To properly support this use case, validators
have to be able to extend the options in pg_hba.
2.2. a recent discussion about adding alternative SSL keys, which
also requires an extension of pg_hosts[2], and that's tricky to do in
a readable way.
3. we can include other files in them, or we can load options from
separate files for hba, but we can't go the other direction: keeping
everything (hba, ident, hosts) together in a single file.
4. while they look the same, hba is order-sensitive, ident is
order-independent in semantics (today's parser happens to walk
top-down), and the new pg_hosts is explicitly unordered.

I'd like to improve the above points by introducing a new
configuration format, while also keeping the current format for
backward compatibility. Backward compatibility would be limited to
currently available features, as in the future, we can't guarantee
that we can fit every new feature into the old formats in an
easy-to-understand way. I would even say that we should implement new
functionality only in the new formats.

Is this a good idea in general? What does everyone think about the
current configuration style? Is it good enough, or should we try to
change it?

Moving on to more specific design questions, let's focus on the first point:

Common, non-vendor-specific configuration formats are INI, XML, JSON,
YAML, and TOML.

INI/conf is way too simple, and also not really a single standard, as
there are many different implementations. XML isn't that popular
anymore.

That leaves JSON/YAML/TOML. These all share one new requirement
compared to the current PostgreSQL config infrastructure, valid UTF-8,
but I don't think that could cause any practical problems.

YAML is complex, and has many unintuitive features. While it is quite
common, I don't think we would want to include a full YAML parser in
PostgreSQL, or try to write our own. We could try a limited YAML
format, dropping some complex/unsafe features, but that would be as
unintuitive as the current configuration formats, and could result in
compatibility issues with existing YAML tooling.

My initial choice for prototyping was JSON, and I ended up creating a
few prototypes for pg_hba with it. At first I liked it, but the more I
worked with it, the more I felt the JSON boilerplate hurt readability.
It's still a fine machine format, but I don't think it's a win for
humans editing config files by hand. Its obvious advantage is that we
already have a JSON parser in the code, and we could extend that to
handle the more human-friendly JSONC/JSON5 variants.

During pgconf.dev several people mentioned TOML when I talked about
the idea. Initially I dismissed it for mostly the same reason as
INI/conf, as I thought it was too simple. But when I decided to try
it, I actually liked it more than my JSON tests. It has a precise
specification and many libraries, so it is both easy to parse and
read.

I'd like to focus on this now, on what a specific TOML configuration
could look like. (I am not saying it has to be TOML, it is just the
best option I've found so far, but if you have a better suggestion,
please share!)

I also attached a discussion-starter patchset that implements the
described format for pg_hosts. As PostgreSQL doesn't currently include
a TOML parser, it uses a vendored tomlc17 parser[3], not necessarily
the final approach, but the easiest way to build a PoC that showcases
the concept, since tomlc17 isn't packaged for most of our supported
platforms. I also have similar patches for the other configuration
files, but I'm intentionally including only one of them for
simplicity.

1. pg_ident

Ident contains multiple maps, which map external usernames to internal
usernames, possibly with regexes. Matching works on both fields, as we
are looking for specific pairs. Currently ident parsing is ordered,
but this is an implementation detail, not a functional requirement: we
are only interested in whether a pair is present. If it is matched by
multiple lines, it doesn't matter which line we select, as it's not
user-visible, other than a log entry.

Based on that, I think we can configure it simply in the following way:

[ident]
map1name = {
"sysuser1" = "ppuser1",
'/^(.*)@mydomain\.com$' = '\1',
}
map2name = {
"sysuser1" = "ppuser1",
"sysuser2" = "ppuser2",
}

This removes the ordering, since TOML tables are unordered, but it is
simple, using the system username as a key and the PostgreSQL username
as the value.

In its current form, it is not extensible. I don't think we have to be
concerned about that too much for ident, but if we ever need to allow
additional values per mapping, we can always optionally allow the
value to be a table like:

"sysuser1" = { username = "ppuser1", other_param = "value",
foo = "bar" },

I also put everything under the "[ident]" table, which helps with the
"possibly keeping everything in a single file" point.

I also want to point out that in the above example map1name / map2name
are inline tables, and if somebody prefers, this can also be written
in the following way, which has the same meaning:

[ident.map1name]
sysuser1 = "ppuser1"
'/^(.*)@mydomain\.com$' = '\1'

[ident.map2name]
"sysuser1" = "ppuser1"
"sysuser2" = "ppuser2"

2. pg_hosts

Hosts is explicitly unordered. It contains hostnames, and every
hostname has associated fields (certificate, key, ca, passphrase
command, reload), which perfectly fits the "extensibility" version of
ident I sketched above. Compared to ident it is only a single map, so
we can store entries directly under the "[hosts]" table.

We can either write:

[hosts]
"hostname1" = { ca = "...", key = "...", passphrase_command = "..." }
"hostname2" = { ca = "...", key = "...", passphrase_command = "..." }

Or again we can write this as:

[hosts.hostname1]
ca = "..."
key = "..."
passphrase_command = "..."

[hosts.hostname2]
ca = "..."
key = "..."
passphrase_command = "..."

This format also clearly gives us extensibility. This is currently
limited to SNI support, but "hosts" is a generic name, in case we ever
want to add more properties.

One idea worth considering is a "_defaults" (or similar)
pseudo-hostname. For example, if all hostnames share the same CA, it
saves repeating that everywhere.

3. pg_hba

This is by far the trickiest format of the three, mainly because of
the options list at the end. Fortunately, we can simplify that part
with TOML, since we can treat each line as a table.

The main issue compared to the previous two is that we have to keep
pg_hba ordered, which means we have to use an array for the lines.

We also need some kind of support for defaults, similar to what I
proposed for hosts, or something even more powerful, to somewhat
replicate and improve what the hba configuration can already do with
the @include syntax.

[hba]
_defaults = { host = "localhost", ssl = "required", transport = "tcp" }
remote = { host = ["*"] }

[[hba.rules]]
based_on = "remote" # or alternatively, can be an array to include
multiple: [ "remote", "another" ]
user = "foo"
auth_method = "oauth"
issuer = "..."
# ...

[[hba.rules]]
# next rule

In the above example I already used the special TOML syntax for arrays
of tables, but of course doing this completely inline is also
possible.

The idea here is that we can specify absolute defaults using the
"_defaults" as before (using _ for consistency with hosts), and also
include other tables to provide more base settings for a specific
rule, using a special based_on property. The value(s) for based_on
reference tables inside hba, e.g. including "foo" means including
"hba.foo".

With based_on, we can also cover part of what the @include syntax does
today: for example, we can define a database array in a separate table
and reference it from a rule. Based on what I've explained so far,
though, this only works within the same file.

What's missing from the above specification is including external
files: TOML has no official support for this, and the generic
suggestion in multiple discussions about it is to "roll your own
extension". This is common for most standard configuration file
formats, which generally leave this part up to the applications.

Adding an include mechanism has the disadvantage that standard parsers
won't be able to use it. But it's an optional feature, and we could
also provide a helper script that generates a "unified" view of an
include hierarchy, which then can be used by standard TOML parsers.

As for the syntax, I think we should simply reuse the existing
include_dir / include_if_exists / include directives, as present in
postgresql.conf, with the additional requirement that we always treat
include directives as global/positional. It's a "preprocessor
directive" similar to #include in C, in that it just concatenates
files together. I think this has many advantages:

1. it keeps the syntax valid TOML, so any TOML library can parse it
(includes found in any table = treat it as global scope, also load
those files, calculate the union)
2. postgresql.conf is already close to TOML. Above I said that it
would be good if we could unify the 3 whitespace-separated
configuration files into one, but with minimal additional effort we
can do more. Since everything for ident/hosts/hba lives in additional
tables, all we have to say is that postgresql.conf only cares about
the global table and ignores everything else, and we can then store
the whole configuration in a single file.

Of course, postgresql.conf allows some syntax that isn't valid TOML,
so it isn't a drop-in subset today, but converting a postgresql.conf
to TOML wouldn't require much work. The main difference is that TOML
doesn't allow unquoted strings, so values like "replica" or "128MB"
need quotes around them.

I want to make clear that postgresql.conf is not in the scope of this
proposal. That's possible future work which I think is important to
mention, but I want to keep the focus on ident/hosts/hba.

I think the second point is especially useful for cloud/container
deployments, or for tests/examples/tutorials. I am not a DBA, so I
don't want to claim that I know all about PostgreSQL configuration,
but my understanding is that most deployments have relatively simple
configuration, where this option would help.

What do you think about this in general? About using TOML? About the
specific TOML format I suggested here?

[1]: https://www.postgresql.org/message-id/CAN4CZFM3b8u5uNNNsY6XCya257u%2BDofms3su9f11iMCxvCacag%40mail.gmail.com
[2]: https://www.postgresql.org/message-id/0cabf744-6d58-4bc4-817a-413c804cf61d%40redhat.com
[3]: https://github.com/cktan/tomlc17