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 1u54MQ-00BhZn-2M for pgsql-general@arkaria.postgresql.org; Wed, 16 Apr 2025 15:07:34 +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 1u54MN-00Gk80-Cd for pgsql-general@arkaria.postgresql.org; Wed, 16 Apr 2025 15:07:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u54MN-00Gk7s-1E for pgsql-general@lists.postgresql.org; Wed, 16 Apr 2025 15:07:31 +0000 Received: from mail-oo1-xc34.google.com ([2607:f8b0:4864:20::c34]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u54MJ-000PXR-28 for pgsql-general@lists.postgresql.org; Wed, 16 Apr 2025 15:07:31 +0000 Received: by mail-oo1-xc34.google.com with SMTP id 006d021491bc7-601a891ab8fso501594eaf.1 for ; Wed, 16 Apr 2025 08:07:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744816046; x=1745420846; darn=lists.postgresql.org; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=eikNkCdTENjz7d9RXCc04Zn8n/ciQo0KUOBJhC1PkLo=; b=CHQqN4h+d0sT49f1OFMrrxue6KRhKK8ds4sBv32i7wPKL5/Qm+IJs1jAu6LK2a1zZF jkFhp5SWkz6ht+bIgrgKamCn9XMTtbMlZq4zmz0wnCxPAig8/Op4JcDQ/Vnv4HgoXw6K S6cvQe8kpds9fq6O8d6lnzPD7C8W5w4zpYKKluBzsAghddrPRAg9KNl9MIOF8QKO3TXj /2N+t8h9D2cIJkFwtsuzfaIewIlutWMP9/3Y3LHI3kHfrqFUO06OAGsrzG1mAWmvoV/I s6xumyE/HMFEi4jC2ew/2kL1oFf2CuD6BELy7EWfo3K7ng3LGXzxukQy+qe+F1LeL2Mc uPIQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744816046; x=1745420846; h=content-transfer-encoding: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=eikNkCdTENjz7d9RXCc04Zn8n/ciQo0KUOBJhC1PkLo=; b=jrv5m7S2Etcebi1/W3pROLKj2p4pjxS2ElJsucRTFhaz8woFJT9MZBirjQgVAPxuoe VRlZ6Q93+HewmQBteRd6aYrtwd8eib8zn3ybzQtAagXZsVwSHO7QQQ8MoENL1ZLO19eX yMVkl+pA0bn9716tM5CamP7MySz9qCPvxSO3dml7GvItF/CONClMdxheqDprlotbFS2N Rl5q4VtEgmLvR2b4z2ixsyM8SrWxvG25n99KfigropTYT3bmOkb3Y+yIwMnh+0v7I4sv IweVcNKOpsSRbp1jK5pJaU1iNX6KIZ3zpe5svP8wDPGVRFM5gWhXgLO0keHft78RWRKY XKHA== X-Forwarded-Encrypted: i=1; AJvYcCVOqx0frcRZHJ97ItH63wK76GPmBTo7q9nnebcaShznOg9DJeU3ODVU0wkhP2ACjPmCQH/66a9tIkA7hGVk@lists.postgresql.org X-Gm-Message-State: AOJu0YwTyAKNaVH3HHaQT1HD60vP/taYqRQqRv9CJbqRgJNh46NPutnA Bl5QKoTLmbxCL9RqOB97YC12AYW3BJeY7vNWt7Z8Bg+y5P0iqIaAjn/eWBRsKriSs+7WNNZVo/x llwImL8SvrnCM/CGg9RuhDgl8b2ysxMPjzLA= X-Gm-Gg: ASbGncs7DOA1t0Hh2634McwGwaUsU7fSKwFkUnwq4EPNoZoFnNVi7RMbB7vlPedSKvb 6SbWt8vB2ikxdsAX3XjtjEJlEFOSAe4X3Nb9dDeJCIs2+ufVHtAuaucfbeE9AvduUAOGUfxSwIZ tc2O8ziQQI4iUOBkeRVNFF7iOc X-Google-Smtp-Source: AGHT+IHLB1aqh/i7cjLP6cvjp3MjTk7tcBD/Pl/fR/rJmdsXA3BmxhX+N29wAH9IWZNxN5DA/3dcyIAApr8NEx43OZE= X-Received: by 2002:a05:6820:201c:b0:600:5673:69ef with SMTP id 006d021491bc7-604a9592b0cmr1265746eaf.1.1744816046612; Wed, 16 Apr 2025 08:07:26 -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: <1898934.1744814354@sss.pgh.pa.us> From: Dominique Devienne Date: Wed, 16 Apr 2025 17:06:57 +0200 X-Gm-Features: ATxdqUF0YICnllr99SUXf7F2LT-xHjWOKxpiOD4f7jTZTkckt2KNbfMRuzUvFNA Message-ID: Subject: Re: Fwd: Identify system databases To: Tom Lane Cc: Laurenz Albe , Adrian Klaver , "David G. Johnston" , Igor Korot , "pgsql-generallists.postgresql.org" Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, Apr 16, 2025 at 4:39=E2=80=AFPM Tom Lane wrote: > 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 on= ly". Naively. Sounds like you are saying use the "postgres" DB for that, and move on. --D= D 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