Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u4jTa-006YWL-Rg for pgsql-general@arkaria.postgresql.org; Tue, 15 Apr 2025 16:49:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u4jTY-00Dde9-Hk for pgsql-general@arkaria.postgresql.org; Tue, 15 Apr 2025 16:49:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u4jTX-00Dde0-VY for pgsql-general@lists.postgresql.org; Tue, 15 Apr 2025 16:49:32 +0000 Received: from mail-ot1-x32b.google.com ([2607:f8b0:4864:20::32b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u4jTV-000Dxl-35 for pgsql-general@lists.postgresql.org; Tue, 15 Apr 2025 16:49:31 +0000 Received: by mail-ot1-x32b.google.com with SMTP id 46e09a7af769-72c27166ab3so3089638a34.1 for ; Tue, 15 Apr 2025 09:49:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744735769; x=1745340569; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=SL8A3++ZnOhA8PInFeweheLX49tt+j8Omv2nmGFsBEE=; b=l9K7k2gHmLFrHG6xgpYFjYL3cL5e3ozWfP8I+SH393eC+BiO1U+RQrZlbjqNRDpwbG XPj6LyxbMCjNqaft0rbEKbP+VTccdYDJaBc/hTfaaR5DDvLvDo47xPBN8a0d1RcekD22 bk2C1DPaAAIENnDAqddjcHMQt8Ar2J8vdAxjhXQ1jkIk0Vvlr4xUfvAaVXYAfCy3GZ77 /+u2KMe6LRVALF0V8PVAl9UdDU5Rs81eNH5ucXvTO6pYCdkkWYEe1TpWqJwcr6UiP0ht afHGL8NeYfYiBSInhHAU+0oMe4rRoq/fO5ipKju60LGD0WqdoOR0NMQaUZw7VdMTV0s1 k63g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744735769; x=1745340569; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=SL8A3++ZnOhA8PInFeweheLX49tt+j8Omv2nmGFsBEE=; b=k3qiRTjS3lWxSXQXpeRRbwHXBrwnv0oBODjpYOpIgAbb4pse0vUZRbGHL1q4xycB8/ UVq9W7nhhngHtASlxuR+OfCJI04jfQongqeb3AeZH4wQANhaNKF2TvSsXC0QaApFJAHf DhsNK3V84Fd0a1Vn3/Ifc4YuNSteYKWqf1ZpE5zU7HYqPYaRgpaYAdHh8bjYLF0U7sXc DtbaSUztVmxre6WOGrOF88ZghZBJ2nA0rHGq2w0Ff4Mjbbg4gBkmaoLvzPmYMfntu5bx kzf23y/R2Exh25ERQ/ti2toGjWXBnRn0hjptUVaCtNrUAFuiFh35ys1e+vKfAVI02Hjr 4jbQ== X-Forwarded-Encrypted: i=1; AJvYcCV4U4Yv8Apacf/IdMEkVCnME/CpUoDYJQwu9cg5NK5xTsuVTnQZSk7IrMHmxODVXGhbiyFHMJtNNlNURDcT@lists.postgresql.org X-Gm-Message-State: AOJu0YydFl39Wp2JEQ/vuHRRADf+qHih915fTYTBe3pNXGKtn1Kmj2LP HaTpEmu35GqYpaTGb0TqyikKo3VDAZr6BDAZDQA8xQjEifrTKCizSGpvYjTmUUPcZoFekoEf3ef GeLLHlp9sghc0xNw4XXECUw5nPnJYdMYO X-Gm-Gg: ASbGncvDxyBRnjf4ZNErsrJJmUDwVrO/GYO2OoK3Ps9bppK4BF211wUtHrWcRUEn23B 31dyZ1jYxVYasbI1NLrjflP3JCEB6F7ezB0eEwAg9+OqGRp3iBdElxSFY8PBSmpA6yjPd+ILUqS 43jJfoBjhfhVhvU/4IvPBj X-Google-Smtp-Source: AGHT+IHoIEH4gvzn4EC22uO4W7OekTUwV05kj6ba6hpOVeXBLOHoZ9PYLTxiDzFT6ik/UYuY9RQWrWE5DEv40GABqG8= X-Received: by 2002:a05:6830:3698:b0:72b:8326:f0c7 with SMTP id 46e09a7af769-72e863c15a6mr10952460a34.28.1744735769332; Tue, 15 Apr 2025 09:49:29 -0700 (PDT) MIME-Version: 1.0 References: <817abeac-d628-4279-bfef-9e1e3aa1884b@aklaver.com> In-Reply-To: <817abeac-d628-4279-bfef-9e1e3aa1884b@aklaver.com> From: "David G. Johnston" Date: Tue, 15 Apr 2025 09:48:49 -0700 X-Gm-Features: ATxdqUECYgS_scL6NrYiD7Mz8FryRLON9Qf-AXwAlQgqFiF1P8JwmodhBngcduU Message-ID: Subject: Re: Fwd: Identify system databases To: Adrian Klaver Cc: Igor Korot , "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000c215af0632d3f39a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c215af0632d3f39a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Apr 15, 2025 at 9:31=E2=80=AFAM Adrian Klaver wrote: > On 4/15/25 09:21, Igor Korot wrote: > > > > > > > Hi, David, > > > > On Tue, Apr 15, 2025 at 9:56=E2=80=AFAM David G. Johnston > > > wrote: > > > > On Tuesday, April 15, 2025, Igor Korot > > 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. --000000000000c215af0632d3f39a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Apr 15, 2025 at 9:31=E2=80=AFAM Adrian Klaver <= adrian.klaver@aklaver.com&= gt; wrote:
On 4/15/25 09:21, Igor= Korot wrote:
>
>

> Hi, David,
>
> On Tue, Apr 15, 2025 at 9:56=E2=80=AFAM David G. Johnston
> <da= vid.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:<= br> >
>=C2=A0 =C2=A0 =C2=A0On Tuesday, April 15, 2025, Igor Korot <ikorot01@gmail.com
>=C2=A0 =C2=A0 =C2=A0<mailto:ikorot01@gmail.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Hi, ALL,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Is there a field in the pg_databases = table which indicates that
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0particular DB is a system one?
>
>
>=C2=A0 =C2=A0 =C2=A0What 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 database= s.

Based on that definition there is a bo= undary in the system where OIDs are considered bootstrap/system OIDs versus= user OIDs.=C2=A0 Key off of that.=C2=A0 Though since the names never chang= e, and there are always/only three, it seems pointless to use the OID aspec= t of initdb as a basis.

My definition of a "syste= m database" would be a database that, if it didn't exist, would ca= use the system to break.=C2=A0 i.e., is a database whose presence is integr= al to the operations of the system.=C2=A0 None of these qualify under that = definition.=C2=A0 Which is why there is no column in pg_database identifyin= g system databases - there are none.

The system will c= ontinue to operate if you do:

initdb
created= b newdb
psql -c 'alter database template0 is_template false;= 9;
dropdb template0
psql -c 'alter database template1 i= s_template false;'
dropdb template1
dropdb --maintenanc= e-db newdb postgres


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

David J= .

--000000000000c215af0632d3f39a--