Hi David,
David G. Johnston schreef op 2023-08-01 19:35:
On Tue, Aug 1, 2023 at 10:13 AM William Edwards
<wedwards@xxxxxxxxxxxxxx> wrote:
This allows all local users connecting over TCP to access all
databases,
not only the databases that the user is a member of as one might
expect.
Proof that user is able to access database that it is not a member
of is
below.
Roles do not gain membership in databases.
I mixed up \du and \l output (the latter has a 'Member of' column)
because I used identical names for some roles and databases. Sorry for
the confusion.
Roles can be granted
permissions on databases (mainly CONNECT). And all roles, via PUBLIC,
get connect privileges on all databases by default. So the
pg_hba.conf entry is not causing something to happen against the
wishes of the privileges system.
https://www.postgresql.org/docs/current/ddl-priv.html
And yes, this is a usability vs secure-by-default that hasn't seen
enough complaint to take on changing the default.
Understood - records in pg_hba.conf limit access preemptively during
client authentication and do not control privileges.
For completeness' sake: from what I understand, with default privileges,
this does allow users to manipulate and read objects in any 'public'
schema pre PostgreSQL 15.x
(https://www.postgresql.org/docs/15/release-15.html E.4.2).
David J.
Met vriendelijke groeten,
William Edwards