Обсуждение: Sample pg_hba.conf allows local users to access all databases

Hi,

The sample pg_hba.conf in master 
(https://github.com/postgres/postgres/blob/master/src/backend/libpq/pg_hba.conf.sample) 
contains the following lines:

```
# IPv4 local connections:
host    all             all             127.0.0.1/32            
@authmethodhost@
# IPv6 local connections:
host    all             all             ::1/128                 
@authmethodhost@
```

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. This was tested with PostgreSQL 14.x on Debian 11 using its 
default pg_hba.conf that also contains the lines above.

I can imagine that this is not desirable on machines to which 
unprivileged users have access. It seems likely that a PostgreSQL 
administrator would expect users to be able to access only the databases 
of which they are a member, unless configured otherwise manually.

Why are these lines shipped by default, and/or am I overestimating the 
impact in real-world scenarios?

Proof:

```
root@sandbox:~# sudo -u postgres psql
postgres=# create database john;
CREATE DATABASE
postgres=# create database jack;
CREATE DATABASE
postgres=# CREATE USER john;
CREATE ROLE
postgres=# CREATE USER jack;
CREATE ROLE
postgres=# ALTER USER john WITH PASSWORD 'password';
ALTER ROLE
postgres=# ALTER USER jack WITH PASSWORD 'password';
ALTER ROLE
postgres=# grant all privileges on database john to john;
GRANT
postgres=# grant all privileges on database jack to jack;
GRANT
postgres=> \l
                                   List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access 
privileges
-----------+----------+----------+-------------+-------------+-----------------------
...
  jack      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
=Tc/postgres         +
            |          |          |             |             | 
postgres=CTc/postgres+
            |          |          |             |             | 
jack=CTc/postgres
  john      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
=Tc/postgres         +
            |          |          |             |             | 
postgres=CTc/postgres+
            |          |          |             |             | 
john=CTc/postgres

root@sandbox:~# psql john john -h 127.0.0.1 -W
Password:
psql (14.8 (Debian 14.8-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 
256, compression: off)
Type "help" for help.

john=> \c jack
Password:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 
256, compression: off)
You are now connected to database "jack" as user "john".
```

With kind regards,

William Edwards

> On Aug 1, 2023, at 10:13, William Edwards <wedwards@cyberfusion.nl> wrote:
> This allows all local users connecting over TCP to access all databases, not only the databases that the user is a
memberof as one might expect. 

There's really no notion of a user being "a member of" a database in PostgreSQL.  Users are global resources, which are
eithergranted access to a particular database, or aren't. 

In your example, you explicitly grant access to the databases to the users you are creating.

Since a default installation of PostgreSQL contains only one superuser role, and the `postgres` database, any other
accessrequires specific intervention on the part of someone with a superuser account. 

Hi David,

David G. Johnston schreef op 2023-08-01 19:35:
> On Tue, Aug 1, 2023 at 10:13 AM William Edwards
> <wedwards@cyberfusion.nl> 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