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 1u54f8-00BmUo-9x for pgsql-general@arkaria.postgresql.org; Wed, 16 Apr 2025 15:26:54 +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 1u54e8-00H7FV-3t for pgsql-general@arkaria.postgresql.org; Wed, 16 Apr 2025 15:25:52 +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 1u54e7-00H7BK-K9 for pgsql-general@lists.postgresql.org; Wed, 16 Apr 2025 15:25:52 +0000 Received: from mail-oo1-xc2d.google.com ([2607:f8b0:4864:20::c2d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u54e6-000OJG-0j for pgsql-general@lists.postgresql.org; Wed, 16 Apr 2025 15:25:51 +0000 Received: by mail-oo1-xc2d.google.com with SMTP id 006d021491bc7-60297d7ab47so3908072eaf.2 for ; Wed, 16 Apr 2025 08:25:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744817150; x=1745421950; 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=s/mZtgR6hHx8Rokn8q/OkItPshQMmbvqq82QsLbfs1Y=; b=BqhZkuQM0PHiPK+9OcSizPWebjaEKYy29Qk+ErvkxqmKed7zkbki6yw0jpYQ/eTpL0 0xtTGaLOaegQJJnMw2Lmdg4P2AvpnaZoH1fBD4z+KgcVqQUcNKQbJ4LeN3S4mMjTVGR6 1KVqjE8si/BznH1nn6IIeCMG7KVLPjQlNicSVSpjdVpewmnLwQ0tgiRjidUB0mRbPW6f WvyNkhoWHHIpBxeI5hOaemFD8kjt7s0j9TDHMixqJveZvs+2qp4nMH6nkNEN17V4MFQA YxCM0NvRslIik47iSbI8CwAeab0wQJm2rRi++1czti4gZl21rE65dxzUrQJoZ5Z7eAJD c3qQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744817150; x=1745421950; 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=s/mZtgR6hHx8Rokn8q/OkItPshQMmbvqq82QsLbfs1Y=; b=HstU/5rK68enuRIHzrw7hG5bcobb0UUuuhxhlErCQTQ5tybS91MvTxnhMQROimNgTn D/n+Gfxu4fBieJhrm5bvNTCfrLW7kU1zuDW766dP3rWDIcI3ddum84qBwzYyE1sYFtYH FpLikcKGTNmUT7VDfAp29Z07gDWo0GG1fsQGpGA2VYKpbbhQF3Z2TrO5Fc8W5jE3nKMk lABwQslZ3GlLa1ZR5YXSRtXG9feqrrx9J2UYT0R2HGABGzvHH6NfSiBE0C3d8jYs4s7Y k5uzLZADmdPF0NFvQtu6T8+EgtJo9ZVy4M4WnszCxqVbrPkJdiy1f19pozb/IiWiC3yZ oBug== X-Forwarded-Encrypted: i=1; AJvYcCVcYDCprcAWeF7pppbNiUfzxo8Npicpqoarix94JQa7efNArKPLi1eNXzQatS5AgaBLwwgaVnKlcHVxE9Ly@lists.postgresql.org X-Gm-Message-State: AOJu0YwZzFqxD1OwuAd5OejZRYwzTGm11OcAXiwBQMUuGAvnUcCQtZDY JK06YwHQ9KrMAyFTaBYy2+utCiYoitsQMEqByg58e/7N6a02f6r1Bkr0cKWGuKyYCcXDF5dw1AE 9vKiqN84bZKw8JfDCmfMhkxOuz4U= X-Gm-Gg: ASbGnctiPzhqWgkf8ULN//Rwu3qN30r9QDcDyuL2lWApyg6OfqLoamwMTwcZzLoMP3P jB/VE2KIlfnApowpPm7Pk9VlKFKqrqQ89g4JoBA3DoRcs4W+pPdL+lp33RP+NklQ6jxTLWeaifP kkVHbmqs70Hn5+UnVEFpdx X-Google-Smtp-Source: AGHT+IEqqaPBeIF9t3tfdwEL2vtRDmce08dfiIG2fremNVmqmk9euqPLw6toVLp1Od1pfjMZj3b4kSQD2XLIbpSwWiI= X-Received: by 2002:a05:6871:a90a:b0:29e:32e7:5f17 with SMTP id 586e51a60fabf-2d4d2d3b6e4mr1326927fac.28.1744817149964; Wed, 16 Apr 2025 08:25:49 -0700 (PDT) MIME-Version: 1.0 References: <817abeac-d628-4279-bfef-9e1e3aa1884b@aklaver.com> <25b6cd49-f871-47fe-bf3b-5416637736ca@aklaver.com> <2dffe860b085b927726a052bcfe16ede704ab923.camel@cybertec.at> <1898934.1744814354@sss.pgh.pa.us> In-Reply-To: From: "David G. Johnston" Date: Wed, 16 Apr 2025 08:25:12 -0700 X-Gm-Features: ATxdqUHipnA1yytlArsLl7Pt2IqRRapnwUO8RRt6t9yJyVOq0b_SLCU478WfWVU Message-ID: Subject: Re: Fwd: Identify system databases To: Dominique Devienne Cc: Tom Lane , Laurenz Albe , Adrian Klaver , Igor Korot , "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000006bfc580632e6e6d3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006bfc580632e6e6d3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Apr 16, 2025 at 8:07=E2=80=AFAM Dominique Devienne wrote: > On Wed, Apr 16, 2025 at 4:39=E2=80=AFPM Tom Lane wrot= e: > > Laurenz Albe 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. --0000000000006bfc580632e6e6d3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Apr 16, 2025 at 8:07=E2=80=AFAM Dominique Devienne= <ddevienne@gmail.com> wro= te:
On Wed, Apr 16, 2025 at 4:39= =E2=80=AFPM 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.=
=C2=A0
Yet have a way to discover which DBs I can connect to, from the "clust= er only".

Kinda surprised you d= on't consider this a feature...give all of your databases UUID names an= d 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=C2=A0v4 protocol.=C2=A0 The use case se= ems reasonable and doable (on the basis of the replication protocol works).=

https://wiki.postgresql.org/wiki/Todo#Wi= re_Protocol_Changes_.2F_v4_Protocol

David J.
=
--0000000000006bfc580632e6e6d3--