Subject: Re: Fwd: Identify system databases

From: Laurenz Albe <[email-masked]>
Date: Wed, 16 Apr 2025 14:30:27 +0200

On Wed, 2025-04-16 at 10:09 +0200, Dominique Devienne wrote:
> On Wed, Apr 16, 2025 at 9:32 AM Laurenz Albe <[email-masked]> wrote:
> > On Tue, 2025-04-15 at 17:24 -0700, Adrian Klaver wrote:
> > But then you fortunately cannot drop all databases, because you cannot
> > drop the database you are connected to.
> > 
> > Still, a cluster that is missing "postgres" will give beginners trouble
> 
> Right. Given shared "cluster-wide" objects like roles, databases, etc...
> I'm always found it weird that one must know "a priori" the name of one
> database to connect to, to do anything with PostgreSQL.

It is documented, and it is the default database if you are connecting
with the default database user "postgres", so it is not that bad.

Also, tools like "createdb" connect to that database by default.

> Not being able to drop the DB one's connected to is also a pita.
> If one have the right to do it, then it should just be done, and the
> connection closed.

No, it is a life saver, at least in my opinion.

> Authentication is cluster-wide, not DB specific, so I'd welcome a way to connect
> to the cluster, not a specific DB, and introspect shared-objects,
> including databases
> I'm allowed to connect to, which could be an empty list.

It is deep in the DNA of PostgreSQL that you always have to connect to
a database, unless you establish a replication connection.
I am surprised that you perceive that as a problem or limitation.

> FWIW. And a little OT. And implicit cluster-level mini-DB given access
> just to a subset of catalogs, or at least some limited queries like
> my databases and my roles, nothing else, would be very welcome IMHO. --DD

Well, that's the "postgres" database.

Yours,
Laurenz Albe

---

From: Tom Lane <[email-masked]>
Date: Wed, 16 Apr 2025 10:39:14 -0400

> I am surprised that you perceive that as a problem or limitation.

That isn't going to change, and here's why not: a lot of the critical
catalogs are per-database not shared.  You aren't going to get
anywhere "introspecting shared objects" when you don't have a copy of
pg_class with which to find the shared catalogs, nor a copy of pg_proc
with which to look up index access method support procedures, etc etc.

You could imagine making up some mini-database that is somehow
forbidden from gaining any user-defined objects and then using
that, but I fail to see why that's a better idea than the
approach we use now.  Not being able to use any user-defined
functions or views seems like a pretty huge handicap.  And this
hypothetical new mini-database *would* be a special snowflake
in a way that none of the existing ones are, in that the system
would have to prevent actions that are perfectly okay in any
other one.  I don't perceive that as a good thing.

(You can, of course, speculate about some major rearchitecting
of the system catalogs that would make this situation different.
I doubt that's going to happen at this point, though.  There's
too much stuff that's dependent on how things are now.)

			regards, tom lane

---

From: Dominique Devienne <[email-masked]>
Date: Wed, 16 Apr 2025 17:06:57 +0200

> > On Wed, 2025-04-16 at 10:09 +0200, Dominique Devienne wrote:

So in a way, you guys are saying one should never REVOKE CONNECT ON
DATABASE FROM PUBLIC?

All my DBs are not PUBLIC-accessible.
And inside my DBs, I try to revoke everything from PUBLIC
(USAGE ON TYPES, EXECUTE ON ROUTINES).
Nor do I use the public schema.
And I never use the "built-in" postgres database.
Basically I want all GRANTs to be explicit.

Given the above, I'd want to not provide access to the postgres DB too.
Yet have a way to discover which DBs I can connect to, from the "cluster only".
Naively.

Sounds like you are saying use the "postgres" DB for that, and move on. --DD

D:\>ppg -c acme -d postgres database_ --acls
Connected OK (postgresql://ddevienne@acme/postgres); with SSL
|----------|----------|-----------|-----------|
| Grantor  | Grantee  | Privilege | Grantable |
|----------|----------|-----------|-----------|
| postgres | PUBLIC   | TEMPORARY |    NO     |
| postgres | PUBLIC   | CONNECT   |    NO     |
| postgres | postgres | CREATE    |    NO     |
| postgres | postgres | TEMPORARY |    NO     |
| postgres | postgres | CONNECT   |    NO     |
|----------|----------|-----------|-----------|
5 ACLs to 2 Grantees from 1 Grantor

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 16 Apr 2025 08:25:12 -0700

> Yet have a way to discover which DBs I can connect to, from the "cluster

Kinda surprised you don't consider this a feature...give all of your
databases UUID names and ensure that non-superusers must be told the
databases they are allowed to connect to.

But feel free to work out a design and add it to the ToDo list for the v4
protocol.  The use case seems reasonable and doable (on the basis of the
replication protocol works).

https://wiki.postgresql.org/wiki/Todo#Wire_Protocol_Changes_.2F_v4_Protocol

David J.

---

From: Tom Lane <[email-masked]>
Date: Wed, 16 Apr 2025 12:08:53 -0400

> replication protocol works).

No, the replication protocol isn't a precedent.  Physical replication
needn't connect to a particular database because it does no catalog
accesses (and hence can't run SQL).  All it's able to do is suck out
the WAL stream.  Logical replication can do SQL --- but it has to
connect to a specific database.

			regards, tom lane

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 16 Apr 2025 22:04:10 +0200

> So in a way, you guys are saying one should never REVOKE CONNECT ON

No, not at all.

> Sounds like you are saying use the "postgres" DB for that, and move on.

It is just fine to restrict access to databases as much as you want.

You just need access to a database if you want to run SQL statements,
be that SELECT or CREATE DATABASE.  It's as simple as that.

Restricting access is not wrong per se, but if you randomly restrict
access to everything, that's not so much a security measure as a way
to make the database unusable.

I see no value in restricting USAGE on the data type "text" or EXECUTE
on the function "upper()".  Modifying system objects is not recommended
and can lead to trouble.

Yours,
Laurenz Albe