pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Sample pg_hba.conf allows local users to access all databases
4+ messages / 3 participants
[nested] [flat]

* Sample pg_hba.conf allows local users to access all databases
@ 2023-08-01 17:13  William Edwards <wedwards@cyberfusion.nl>
  0 siblings, 2 replies; 4+ messages in thread

From: William Edwards @ 2023-08-01 17:13 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Sample pg_hba.conf allows local users to access all databases
@ 2023-08-01 17:34  Christophe Pettus <xof@thebuild.com>
  parent: William Edwards <wedwards@cyberfusion.nl>
  1 sibling, 0 replies; 4+ messages in thread

From: Christophe Pettus @ 2023-08-01 17:34 UTC (permalink / raw)
  To: William Edwards <wedwards@cyberfusion.nl>; +Cc: pgsql-general@lists.postgresql.org



> 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.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Sample pg_hba.conf allows local users to access all databases
@ 2023-08-01 17:35  David G. Johnston <david.g.johnston@gmail.com>
  parent: William Edwards <wedwards@cyberfusion.nl>
  1 sibling, 1 reply; 4+ messages in thread

From: David G. Johnston @ 2023-08-01 17:35 UTC (permalink / raw)
  To: William Edwards <wedwards@cyberfusion.nl>; +Cc: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Sample pg_hba.conf allows local users to access all databases
@ 2023-08-02 08:08  William Edwards <wedwards@cyberfusion.nl>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: William Edwards @ 2023-08-02 08:08 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2023-08-02 08:08 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-01 17:13 Sample pg_hba.conf allows local users to access all databases William Edwards <wedwards@cyberfusion.nl>
2023-08-01 17:34 ` Christophe Pettus <xof@thebuild.com>
2023-08-01 17:35 ` David G. Johnston <david.g.johnston@gmail.com>
2023-08-02 08:08   ` William Edwards <wedwards@cyberfusion.nl>

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