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 1qR6up-000aGn-Kt for pgsql-general@arkaria.postgresql.org; Wed, 02 Aug 2023 08:09:08 +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 1qR6uo-003asW-0b for pgsql-general@arkaria.postgresql.org; Wed, 02 Aug 2023 08:09:06 +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 1qR6un-003arw-JC for pgsql-general@lists.postgresql.org; Wed, 02 Aug 2023 08:09:06 +0000 Received: from ethorus.out.prorelay.nl ([185.233.175.147]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qR6uk-0005yg-14 for pgsql-general@lists.postgresql.org; Wed, 02 Aug 2023 08:09:04 +0000 Received: from mgw0-0.prorelay.nl (localhost.localdomain [127.0.0.1]) by mgw0-0.prorelay.nl (ProRelay) with ESMTP id C4C146433C; Wed, 2 Aug 2023 10:08:57 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cyberfusion.nl; h=cc:cc:content-transfer-encoding:content-type:content-type :date:from:from:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to; s=default; bh=aAUd+UOU8grDUT1+y Zl13c0Y6ireOL4sDnQchfzliUk=; b=XGIxPAp3DLue/nSAh2gXJD/82L4kQBKXc gCd/u63vi+WgWrQnL0JLwDboKe3ibab8TtlMtunaO7LhzZx9VG4fkC2oQW9C9KaH dDQFzFw2RMjhRrOVSj/LqgES5FMurXwUUmnBy0Ar6NTJjA65A+6oFqlWHJGJhGmr VdsrceoaJJCDCODrhcC8Bv2v6tGvZu6+DKTtgLFRnoypBc1hD9IswaUhC4qYUC+7 WBQyV4FBh6l9EsV0F6L5OgX3fufkPGvKWQdvW71OBQG9Fh40o3kC5YaNd5hM5fMF KJd3fRyATTnUw74Llq0jwvGKGXOf4TiyiiiwNYoP7StD4Vr56PmBXrnebPrmculX dhC/EX0XLUyc0voLB3BLsc0o3BHKhelF5J8FF1VLjHAykSMztRtXraIidoM97t32 vVQQ7PhcMn1LHm0QUEVXSMNfuLluvKJc76J4UhcJCxq9C4IHyLZGeSClbRxr1ZJU ubwZcn7/Cucur6YE1bUY7Iyo/NHIBfh1j9i/LsYchTUuWUPg+eJcSIinB/SNh+jj hvAAM+hUwU8gNQJhBqbAB4Z35T9HDg3xX4j78wHyG7QWMP04lbngbNIcsV/la//W rYR/YdPNtTVmP3g4pvcJYSd9m6035hoLBt/x6RQ0ZMwvEPSmN2/9MLtOLj25HUB3 kbi66uG8Po= X-Envelope-To: david.g.johnston@gmail.com X-Envelope-To: pgsql-general@lists.postgresql.org MIME-Version: 1.0 Date: Wed, 02 Aug 2023 10:08:56 +0200 From: William Edwards To: "David G. Johnston" Cc: pgsql-general@lists.postgresql.org Subject: Re: Sample pg_hba.conf allows local users to access all databases In-Reply-To: References: <07a0ac4f2b98152f355c64e967763924@cyberfusion.nl> Message-ID: <54edc22f9addec5fcb8ea8190274cf06@cyberfusion.nl> X-Sender: wedwards@cyberfusion.nl Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi David, David G. Johnston schreef op 2023-08-01 19:35: > On Tue, Aug 1, 2023 at 10:13 AM William Edwards > wrote: > >> This allows all local users connecting over TCP to access all >> databases, >> not only the databases that the user is a member of as one might >> expect. >> >> Proof that user is able to access database that it is not a member >> of is >> below. > > Roles do not gain membership in databases. I mixed up \du and \l output (the latter has a 'Member of' column) because I used identical names for some roles and databases. Sorry for the confusion. > Roles can be granted > permissions on databases (mainly CONNECT). And all roles, via PUBLIC, > get connect privileges on all databases by default. So the > pg_hba.conf entry is not causing something to happen against the > wishes of the privileges system. > > https://www.postgresql.org/docs/current/ddl-priv.html > > And yes, this is a usability vs secure-by-default that hasn't seen > enough complaint to take on changing the default. Understood - records in pg_hba.conf limit access preemptively during client authentication and do not control privileges. For completeness' sake: from what I understand, with default privileges, this does allow users to manipulate and read objects in any 'public' schema pre PostgreSQL 15.x (https://www.postgresql.org/docs/15/release-15.html E.4.2). > > David J. Met vriendelijke groeten, William Edwards