public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Matt Zagrabelny <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: grant connect to all databases
Date: Sat, 05 Oct 2024 14:57:59 -0400
Message-ID: <[email protected]> (raw)
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>
	<[email protected]>
	<CAOLfK3V10fBPAhXHLJ6hyZ41BVZQTk=Bbq7MODX30O_REPvQrg@mail.gmail.com>
	<[email protected]>
	<CAOLfK3W6K-r0+v7Sa9D6684hM3Jg0UxtZ4iOwZiJsteBj0rfoQ@mail.gmail.com>

Matt Zagrabelny <[email protected]> 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 for
> 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=# create user alice;
CREATE ROLE
postgres=# create database test;
CREATE DATABASE
postgres=# \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=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# revoke connect on database test from public;
REVOKE
postgres=# \c test alice
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  permission 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

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: grant connect to all databases
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox