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 1qQsvo-001IEV-LM for pgsql-general@arkaria.postgresql.org; Tue, 01 Aug 2023 17:13:14 +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 1qQsvl-008Zya-Q1 for pgsql-general@arkaria.postgresql.org; Tue, 01 Aug 2023 17:13:11 +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 1qQsvl-008ZyS-Bq for pgsql-general@lists.postgresql.org; Tue, 01 Aug 2023 17:13:10 +0000 Received: from nebrizuno.out.prorelay.nl ([185.233.175.149]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qQsvh-000DZt-Ah for pgsql-general@lists.postgresql.org; Tue, 01 Aug 2023 17:13:09 +0000 Received: from mgw0-0.prorelay.nl (localhost.localdomain [127.0.0.1]) by mgw0-0.prorelay.nl (ProRelay) with ESMTP id 01CBB62AA3 for ; Tue, 1 Aug 2023 19:13:02 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cyberfusion.nl; h=cc:content-transfer-encoding:content-type:content-type:date :from:from:message-id:mime-version:reply-to:subject:subject:to :to; s=default; bh=jp6o8+ZV6sXqLKzlTlZRdfTr2VxMB1f2x/9tACQcSuY=; b= L13UbJbhkF9c9xYxywFDQlgbI7UJuxzDBFrq+U+7MqfdtXcGhk35S7RCJbvRJEur WupGyBDC2nO8z/BfGH0Nq5C11hbJytve7IgJGdTTPYxyAekbUMKBYsxSTJe5oanF gJAj6tUmVSHVLw4vwSvllh54AZ+3HqtY9YtByQFActQBI2MwqVeHkpJtI92McLY+ nq6UL7UlfrYN0hOw676u1MKE/Neatd+2q/oCkdYGVSqTM8bTgKTZsCO59ai24684 yMY23xYQjuVLWDtIni1LZ/KqeoAVzc/VyXhhmMykN6b8WLJBQPFTd1WZBfistfAG eSOKD8PytR9E2oKqZCvBp+8s1QP6SFxXA8lTJ/CGw5lytPaiJHLzY/s4S4wpazkJ fMx2TW4iEG/xlS113THSoDOSwC/dfMWT4qD8nrE92bZrf/5LNkAoEBDE/B5wznPI BB3ayiPmAUdI7tKYfW1l9eRxqlkNEzJNvUpT2Nas4EgdRAScxL5ElPu0sv3Z5jkL 5n/nSvXJQUv4bUkOtLvNFujNkxZZfUbu0kj43h9/ETgCjyfz3kYcmLA+IrDKXwv/ XOMEdIyjpxtGcKKjn2btRUVeN+qKzde1g3t3HteMkSjynM1NHhW/yUegF5xzm7xz wJ/5klX/9oL2rJGYut50oJQvb2qojkTwkLNlsE5ZhQo= X-Envelope-To: pgsql-general@lists.postgresql.org MIME-Version: 1.0 Date: Tue, 01 Aug 2023 19:13:00 +0200 From: William Edwards To: pgsql-general@lists.postgresql.org Subject: Sample pg_hba.conf allows local users to access all databases Message-ID: <07a0ac4f2b98152f355c64e967763924@cyberfusion.nl> X-Sender: wedwards@cyberfusion.nl Content-Type: text/plain; charset=US-ASCII; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, The sample pg_hba.conf in master (https://github.com/postgres/postgres/blob/master/src/backend/libpq/pg_hba.conf.sample) contains the following lines: ``` # IPv4 local connections: host all all 127.0.0.1/32 @authmethodhost@ # IPv6 local connections: host all all ::1/128 @authmethodhost@ ``` 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. This was tested with PostgreSQL 14.x on Debian 11 using its default pg_hba.conf that also contains the lines above. I can imagine that this is not desirable on machines to which unprivileged users have access. It seems likely that a PostgreSQL administrator would expect users to be able to access only the databases of which they are a member, unless configured otherwise manually. Why are these lines shipped by default, and/or am I overestimating the impact in real-world scenarios? Proof: ``` root@sandbox:~# sudo -u postgres psql postgres=# create database john; CREATE DATABASE postgres=# create database jack; CREATE DATABASE postgres=# CREATE USER john; CREATE ROLE postgres=# CREATE USER jack; CREATE ROLE postgres=# ALTER USER john WITH PASSWORD 'password'; ALTER ROLE postgres=# ALTER USER jack WITH PASSWORD 'password'; ALTER ROLE postgres=# grant all privileges on database john to john; GRANT postgres=# grant all privileges on database jack to jack; GRANT postgres=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- ... jack | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | jack=CTc/postgres john | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | john=CTc/postgres root@sandbox:~# psql john john -h 127.0.0.1 -W Password: psql (14.8 (Debian 14.8-1.pgdg110+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. john=> \c jack Password: SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) You are now connected to database "jack" as user "john". ``` With kind regards, William Edwards