pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (4+ messages)  latest in thread

Message-ID: <07a0ac4f2b98152f355c64e967763924@cyberfusion.nl>
Permalink:  ../07a0ac4f2b98152f355c64e967763924@cyberfusion.nl/
Also on:    postgresql.org/message-id/07a0ac4f2b98152f355c64e967763924@cyberfusion.nl

 · 

reply

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