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 member of 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 either granted 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 access requires specific intervention on the part of someone with a superuser account.
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. 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.
David J.
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