From: | William Edwards <wedwards(at)cyberfusion(dot)nl> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Sample pg_hba.conf allows local users to access all databases |
Date: | 2023-08-01 17:13:00 |
Message-ID: | 07a0ac4f2b98152f355c64e967763924@cyberfusion.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)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(at)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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2023-08-01 17:14:08 | Re: How to build a new grammer for pg? |
Previous Message | Ron | 2023-08-01 16:22:31 | Re: PostgreSQL listens on localhost? |