pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedSample 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>
2023-08-01 17:34 ` Re: Sample pg_hba.conf allows local users to access all databases Christophe Pettus <xof@thebuild.com>
2023-08-01 17:35 ` Re: Sample pg_hba.conf allows local users to access all databases David G. Johnston <david.g.johnston@gmail.com>
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: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>
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:13 Sample pg_hba.conf allows local users to access all databases William Edwards <wedwards@cyberfusion.nl>
@ 2023-08-01 17:35 ` David G. Johnston <david.g.johnston@gmail.com>
2023-08-02 08:08 ` Re: Sample pg_hba.conf allows local users to access all databases 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-01 17:13 Sample pg_hba.conf allows local users to access all databases William Edwards <wedwards@cyberfusion.nl>
2023-08-01 17:35 ` Re: Sample pg_hba.conf allows local users to access all databases David G. Johnston <david.g.johnston@gmail.com>
@ 2023-08-02 08:08 ` William Edwards <wedwards@cyberfusion.nl>
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