Thread: Identify system databases

Hi, ALL,

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

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

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

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

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> 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).

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

On Wed, 2025-04-16 at 17:06 +0200, Dominique Devienne wrote:
> So in a way, you guys are saying one should never REVOKE CONNECT ON
> DATABASE FROM PUBLIC?

No, not at all.

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

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