From: William Edwards <wedwards@cyberfusion.nl>
To: pgsql-general@lists.postgresql.org
Subject: Sample pg_hba.conf allows local users to access all databases
Date: Tue, 01 Aug 2023 19:13:00 +0200
Message-ID: <07a0ac4f2b98152f355c64e967763924@cyberfusion.nl> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: wedwards@cyberfusion.nl
Subject: Re: Sample pg_hba.conf allows local users to access all databases
In-Reply-To: <07a0ac4f2b98152f355c64e967763924@cyberfusion.nl>
References: <07a0ac4f2b98152f355c64e967763924@cyberfusion.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox