From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matt Zagrabelny <mzagrabe@d.umn.edu>
cc: Adrian Klaver <adrian.klaver@aklaver.com>,
        "David G. Johnston" <david.g.johnston@gmail.com>,
        "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: grant connect to all databases
In-reply-to: <CAOLfK3W6K-r0+v7Sa9D6684hM3Jg0UxtZ4iOwZiJsteBj0rfoQ@mail.gmail.com>
References: <CAOLfK3Vj-PFBJi28y1170ZP3dGeW2qpG_8_9CbaJWvEgXQ8-jQ@mail.gmail.com> <CAKFQuwYG8uQhN50MgcF1seg8+dwvgTMFez=wA3Rg2rosob78cg@mail.gmail.com> <CAOLfK3XOHnyWsLv_CdFAegWg1FgM3AK3WsO_r+rXSNjp8TQXcg@mail.gmail.com> <cebb02fa-5487-4c06-8527-9c8ba29c13a9@aklaver.com> <CAOLfK3V10fBPAhXHLJ6hyZ41BVZQTk=Bbq7MODX30O_REPvQrg@mail.gmail.com> <0c089041-d4eb-4bf4-bfcb-6451224190c9@aklaver.com> <CAOLfK3W6K-r0+v7Sa9D6684hM3Jg0UxtZ4iOwZiJsteBj0rfoQ@mail.gmail.com>
Comments: In-reply-to Matt Zagrabelny <mzagrabe@d.umn.edu>
	message dated "Sat, 05 Oct 2024 13:24:47 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1062813.1728154679.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 05 Oct 2024 14:57:59 -0400
Message-ID: <1062814.1728154679@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1062814.1728154679%40sss.pgh.pa.us>
Precedence: bulk

Matt Zagrabelny <mzagrabe@d.umn.edu> writes:
>  ...but I still cannot connect:

> $ psql -d test -U alice
> psql: error: connection to server on socket
> "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  permission denied fo=
r
> database "test"
> DETAIL:  User does not have CONNECT privilege.

This shouldn't be happening, since as mentioned upthread our default
for newly-created databases is that they have CONNECT granted to
PUBLIC.  It works fine for me, even for a user with no special
permissions:

postgres=3D# create user alice;
CREATE ROLE
postgres=3D# create database test;
CREATE DATABASE
postgres=3D# \c test alice
You are now connected to database "test" as user "alice".

If I explicitly revoke the privilege, I can duplicate your
results:

test=3D> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=3D# revoke connect on database test from public;
REVOKE
postgres=3D# \c test alice
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  permis=
sion denied for database "test"
DETAIL:  User does not have CONNECT privilege.

I wonder if your puppet recipe is revoking that behind your
back, or if you are using some modified version of Postgres
with different ideas about default privileges.  Looking at
psql \l output for the test DB might be informative.

			regards, tom lane