Identify system databases

On Tuesday, April 15, 2025, Igor Korot <ikorot01@gmail.com> wrote:

Hi, ALL,
Is there a field in the pg_databases table which indicates that particular
DB is a system one?

What is a system database?

David J.

On 4/15/25 07:48, Igor Korot wrote:

Hi, ALL,
Is there a field in the pg_databases table which indicates that
particular DB is a system one?

No there is not. As a practical matter they are no different then any
user created database, other then template0 is created with datallowconn
set to false.

Or it can be determined ony by name?

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

---------- Forwarded message ---------
From: Igor Korot <ikorot01@gmail.com>
Date: Tue, Apr 15, 2025 at 11:15 AM
Subject: Re: Identify system databases
To: David G. Johnston <david.g.johnston@gmail.com>

Hi, David,

On Tue, Apr 15, 2025 at 9:56 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tuesday, April 15, 2025, Igor Korot <ikorot01@gmail.com> wrote:

Hi, ALL,
Is there a field in the pg_databases table which indicates that
particular DB is a system one?

What is a system database?

I consider system database a database that is created by default when the
server is run for the first time.

Kind of a predefined set of databases…

As an example - there is a database called Postgres. It is created when the
server runs for the first time, whether I as a user like it or not.

Thank you.

---------- Forwarded message ---------
From: Igor Korot <ikorot01@gmail.com>
Date: Tue, Apr 15, 2025 at 11:16 AM
Subject: Re: Identify system databases
To: Adrian Klaver <adrian.klaver@aklaver.com>

Hi, Adrian,

On Tue, Apr 15, 2025 at 10:07 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 4/15/25 07:48, Igor Korot wrote:

Hi, ALL,
Is there a field in the pg_databases table which indicates that
particular DB is a system one?

No there is not. As a practical matter they are no different then any
user created database, other then template0 is created with datallowconn
set to false.

Understood. Will filter by name…

Thank you.

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 4/15/25 07:48, Igor Korot wrote:

Is there a field in the pg_databases table which indicates that
particular DB is a system one?

No there is not. As a practical matter they are no different then any
user created database, other then template0 is created with datallowconn
set to false.

Yeah; in a very real sense there isn't such a thing as a "system
database" in Postgres. There are pre-created databases that have
specific uses (such as being the default template for CREATE
DATABASE), but those uses are identified by name not by OID.
You can drop template1, and the only thing that will be unhappy
is CREATE DATABASE, and if you make a new DB that is named
template1 then CREATE DATABASE will be happy again.

regards, tom lane

On 4/15/25 09:21, Igor Korot wrote:

Hi, David,

On Tue, Apr 15, 2025 at 9:56 AM David G. Johnston
<david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:

On Tuesday, April 15, 2025, Igor Korot <ikorot01@gmail.com
<mailto:ikorot01@gmail.com>> wrote:

Hi, ALL,
Is there a field in the pg_databases table which indicates that
particular DB is a system one?

What is a system database?

I consider system database a database that is created by default when
the server is run for the first time.

Agreed.

The fact that initdb creates the template0, template1 and postgres
databases and you can't change that makes them system not user databases.

Further from here:

https://www.postgresql.org/docs/devel/app-initdb.html

"The postgres database is a default database meant for use by users,
utilities and third party applications"

indicates that postgres is expected to be in a cluster(system).

Lastly the CREATE DATABASE depends on template1 to be there to create at
least the first user database. Also template0 is a "clean" template
database that allows for using a new encoding in new database. It is
also used by pg_dump/restore:

CREATE DATABASE test WITH TEMPLATE = template0 ...

Kind of a predefined set of databases…

As an example - there is a database called Postgres. It is created when
the server runs for the first time, whether I as a user like it or not.

Thank you.

David J.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Apr 15, 2025 at 9:31 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 4/15/25 09:21, Igor Korot wrote:

Hi, David,

On Tue, Apr 15, 2025 at 9:56 AM David G. Johnston
<david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:

On Tuesday, April 15, 2025, Igor Korot <ikorot01@gmail.com
<mailto:ikorot01@gmail.com>> wrote:

Hi, ALL,
Is there a field in the pg_databases table which indicates that
particular DB is a system one?

What is a system database?

I consider system database a database that is created by default when
the server is run for the first time.

Agreed.

The fact that initdb creates the template0, template1 and postgres
databases and you can't change that makes them system not user databases.

Based on that definition there is a boundary in the system where OIDs are
considered bootstrap/system OIDs versus user OIDs. Key off of that.
Though since the names never change, and there are always/only three, it
seems pointless to use the OID aspect of initdb as a basis.

My definition of a "system database" would be a database that, if it didn't
exist, would cause the system to break. i.e., is a database whose presence
is integral to the operations of the system. None of these qualify under
that definition. Which is why there is no column in pg_database
identifying system databases - there are none.

The system will continue to operate if you do:

initdb
createdb newdb
psql -c 'alter database template0 is_template false;'
dropdb template0
psql -c 'alter database template1 is_template false;'
dropdb template1
dropdb --maintenance-db newdb postgres

An operational definition worth considering, though, is that any database
owned by the bootstrap superuser is a system database. After all, the
system owner created/owns them? If you want non-system databases for your
application, assign their ownership to a non-system role.

David J.

On 4/15/25 09:48, David G. Johnston wrote:

On Tue, Apr 15, 2025 at 9:31 AM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

Agreed.

The fact that initdb creates the template0, template1 and postgres
databases and you can't change that makes them system not user
databases.

Based on that definition there is a boundary in the system where OIDs
are considered bootstrap/system OIDs versus user OIDs.  Key off of
that.  Though since the names never change, and there are always/only
three, it seems pointless to use the OID aspect of initdb as a basis.

My definition of a "system database" would be a database that, if it
didn't exist, would cause the system to break.  i.e., is a database
whose presence is integral to the operations of the system.  None of
these qualify under that definition.  Which is why there is no column in
pg_database identifying system databases - there are none.

1) Try connecting(not using single user mode) to a cluster without them.

2) Trying creating a database or databases to replace them without them
existing in the first place.

If what you say is true why does initdb lack an option to not create
them on creating a cluster?

David J.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Apr 15, 2025 at 11:20 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 4/15/25 09:48, David G. Johnston wrote:

On Tue, Apr 15, 2025 at 9:31 AM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

Agreed.

The fact that initdb creates the template0, template1 and postgres
databases and you can't change that makes them system not user
databases.

Based on that definition there is a boundary in the system where OIDs
are considered bootstrap/system OIDs versus user OIDs. Key off of
that. Though since the names never change, and there are always/only
three, it seems pointless to use the OID aspect of initdb as a basis.

My definition of a "system database" would be a database that, if it
didn't exist, would cause the system to break. i.e., is a database
whose presence is integral to the operations of the system. None of
these qualify under that definition. Which is why there is no column in
pg_database identifying system databases - there are none.

1) Try connecting(not using single user mode) to a cluster without them.

I did. Here, the full test.
-- freshly initdb cluster exists and is running

❯ cat ~/Downloads/test-drops.bash
createdb mydb
psql -c 'alter database template0 is_template false;' mydb
dropdb template0
psql -c 'alter database template1 is_template false;' mydb
dropdb template1
dropdb --maintenance-db mydb postgres
psql -c '\l' mydb

❯ bash ~/Downloads/test-drops.bash
ALTER DATABASE
ALTER DATABASE
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype |
Locale | ICU Rules | Access privileges
------+--------+----------+-----------------+------------+------------+--------+-----------+-------------------
mydb | davidj | UTF8 | libc | en_US.utf8 | en_US.utf8 |
| |
(1 row)

2) Trying creating a database or databases to replace them without them
existing in the first place.

Why?

If what you say is true why does initdb lack an option to not create
them on creating a cluster?

Well, it has to create one database because it is not possible to connect
to the cluster without naming a specific database.

initdb could allow the user to specify the initial database name and only
create that single database. It would work.

By creating the initial three databases the system is more usable due to
having established conventions. They are conventional databases, not
system ones.

Plus, it leaves the cluster in a state that is generally much more
user-friendly. And, as shown, people who dislike the convention can
rearrange things into whatever form they desire.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, Apr 15, 2025 at 11:20 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

If what you say is true why does initdb lack an option to not create
them on creating a cluster?

By creating the initial three databases the system is more usable due to
having established conventions. They are conventional databases, not
system ones.

Precisely. For example, the only reason for the "postgres" database
to exist is so that there is a reasonable default database for clients
to connect to. If we didn't have it we'd need some other convention.
(Indeed, we used to not have it, and back then the default client
behavior was usually to connect to template1. That led to people
creating random junk in template1 and then being surprised when
CREATE DATABASE copied it into new databases.)

In the end this discussion is about terminology. These three
databases are part of the ecosystem and clients generally expect them
to be there. But the server doesn't depend on them to function.
Does that make them "system" databases? All depends on what you
think that means.

regards, tom lane

On Tue, Apr 15, 2025 at 3:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tue, Apr 15, 2025 at 11:20 AM Adrian Klaver <

adrian.klaver@aklaver.com>

wrote:

If what you say is true why does initdb lack an option to not create
them on creating a cluster?

By creating the initial three databases the system is more usable due to
having established conventions. They are conventional databases, not
system ones.

Precisely. For example, the only reason for the "postgres" database
to exist is so that there is a reasonable default database for clients
to connect to. If we didn't have it we'd need some other convention.
(Indeed, we used to not have it, and back then the default client
behavior was usually to connect to template1. That led to people
creating random junk in template1 and then being surprised when
CREATE DATABASE copied it into new databases.)

Interesting history. I've always interpreted the "postgres" database as
being the system database, but now I know it's not.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

On 4/15/25 11:35, David G. Johnston wrote:

On Tue, Apr 15, 2025 at 11:20 AM Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

1) Try connecting(not using single user mode) to a cluster without them.

First I have had this discussion before and was not successful and I
expect the same outcome this time, but I am half Dutch so that is not a
concern.

I did.  Here, the full test.
-- freshly initdb cluster exists and is running

❯ cat ~/Downloads/test-drops.bash
createdb mydb

From Postgres log:

CREATE DATABASE mydb;

From here:

https://www.postgresql.org/docs/current/sql-createdatabase.html

"template

The name of the template from which to create the new database, or
DEFAULT to use the default template (template1).
"

I know I can use --template=template with createdb, but that still means
there has to be a database existing in the initial cluster to use as a
template. You can't get around that fact.

2) Trying creating a database or databases to replace them without them
existing in the first place.

Why?

See above. It boils down to, there has to be 'seed'(system) databases
extant in the cluster created by initdb for a user to move forward.

If what you say is true why does initdb lack an option to not create
them on creating a cluster?

Well, it has to create one database because it is not possible to
connect to the cluster without naming a specific database.

Exactly, the cluster needs a database created by the system to function.

initdb could allow the user to specify the initial database name and
only create that single database.  It would work.

From an earlier post of mine in this thread:

"Further from here:

https://www.postgresql.org/docs/devel/app-initdb.html

"The postgres database is a default database meant for use by users,
utilities and third party applications"

indicates that postgres is expected to be in a cluster(system).

Lastly the CREATE DATABASE depends on template1 to be there to create at
least the first user database. Also template0 is a "clean" template
database that allows for using a new encoding in new database. It is
also used by pg_dump/restore:

CREATE DATABASE test WITH TEMPLATE = template0 ...
"

It would be possible to do as you say, but then you would need to make
the system nature of the process explicit by updating pg_database to
include information on what the default template database, the 'clean'
template and the 'default connection database' are. As it it stands now
it is implicit in the naming and the use of those names by the server
code and outside code.

By creating the initial three databases the system is more usable due to
having established conventions.  They are conventional databases, not
system ones.

Then remove them or rename them and see how long it takes the mail
server to start glowing with posts to --general and --bugs.

The bottom line is the Postgres project has built an infrastructure
around those names that the community is plugged into.

Plus, it leaves the cluster in a state that is generally much more
user-friendly.  And, as shown, people who dislike the convention can
rearrange things into whatever form they desire.

David J.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Apr 15, 2025 at 5:24 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

By creating the initial three databases the system is more usable due to
having established conventions. They are conventional databases, not
system ones.

The bottom line is the Postgres project has built an infrastructure
around those names that the community is plugged into.

So, the answer is no, there is no dedicated pg_database column to identify
the system/conventional databases created by initdb. They are identified
by name. You must know from documentation or experience that they are
"postgres", "template1" and "template0".

Pick whichever of "system" or "convention" or some other word makes you
happy to categorize those three databases. Though I suggest "the initdb"
databases since neither of the other terms is used anywhere in the
documentation.

David J.

On Tue, 2025-04-15 at 17:24 -0700, Adrian Klaver wrote:

I know I can use --template=template with createdb, but that still means
there has to be a database existing in the initial cluster to use as a
template. You can't get around that fact.

Right.

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,
a cluster that is missing "template1" is annoying, and a cluster without
"template0" is possibly in serious trouble.

Yours,
Laurenz Albe

On Wed, Apr 16, 2025 at 9:32 AM Laurenz Albe <laurenz.albe@cybertec.at> 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.

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.

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.

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

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

On Wed, Apr 16, 2025 at 9:32 AM Laurenz Albe <laurenz.albe@cybertec.at> 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

Laurenz Albe <laurenz.albe@cybertec.at> writes:

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

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.

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

On Wed, Apr 16, 2025 at 4:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

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

On Wed, Apr 16, 2025 at 8:07 AM Dominique Devienne <ddevienne@gmail.com>
wrote:

On Wed, Apr 16, 2025 at 4:39 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Laurenz Albe <laurenz.albe@cybertec.at> writes:

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

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.