AW: BUG #18725: "set role" requires "grant connect on database" - Mailing list pgsql-bugs

Thank you very much for your effort. 
Now I have more details (I'm not a developer).         '
The problem only occurs with queries on large tables that run parallel:


Preparation:
    [Login superuser]
    CREATE ROLE "fdba-Group" WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;
    GRANT CONNECT ON DATABASE "DATABASE1" TO "fdba-Group";
    CREATE ROLE user1 WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS PASSWORD
'user1';
    CREATE ROLE "schema1_owner" WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS; --
ohneLogin, ohne Connect-Recht
 
    GRANT "fdba-Group", "schema1_owner" TO user1;
    CREATE SCHEMA schema1 AUTHORIZATION schema1_owner;
    GRANT ALL ON SCHEMA schema1 TO schema1_owner;
    CREATE TABLE schema1.table1 (column1 INT, column2 VARCHAR(50));
    ALTER TABLE schema1.table1 OWNER to schema1_owner;
    GRANT ALL ON TABLE schema1.table1 TO schema1_owner;
    INSERT INTO schema1.table1 (column1, column2) VALUES (1, 'abc'), (2, 'def');      


Implementation Version <= 16.4 is OK:
    [Login user1]
    SET ROLE schema1_owner;
        SET
    set debug_parallel_query = off;
        SET
    select column1 from schema1.table1;
        1
        2
    set debug_parallel_query = on;
        SET
    select column1 from schema1.table1;
        1
        2
    RESET ROLE;
        RESET


Implementation Version 16.5, 16.6:
    [Login user1]
    SET ROLE schema1_owner;
        SET
    set debug_parallel_query = off;
        SET
    select column1 from schema1.table1;
        1
        2
    set debug_parallel_query = on;
        SET
    select column1 from schema1.table1;
        ERROR:  Benutzer hat das CONNECT-Privileg nicht.keine Berechtigung für Datenbank »DATABASE1« 
        FEHLER:  keine Berechtigung für Datenbank »DATABASE1«
        SQL state: 42501
        Detail: Benutzer hat das CONNECT-Privileg nicht.
        Context: paralleler Arbeitsprozess
    RESET ROLE;
        RESET


(We manage 550 databases. Three customers have already contacted us 
because they received an error message. Our workaround is "grant connect".)

Kind regards Susanne Birner



-----Ursprüngliche Nachricht-----
Von: Tom Lane <tgl@sss.pgh.pa.us> 
Gesendet: Mittwoch, 27. November 2024 19:11
An: PostgreSQL TDBA (LDBV) <PostgreSQL_TDBA@ldbv.bayern.de>
Cc: pgsql-bugs@lists.postgresql.org
Betreff: Re: BUG #18725: "set role" requires "grant connect on database"

PG Bug reporting form <noreply@postgresql.org> writes:
> After minor release update to version 16.5 or 16.6 "set role" to role 
> without "grant connect on database" generates error:

> "ERROR:  Benutzer hat das CONNECT-Privileg nicht.keine Berechtigung 
> für Datenbank »...«
> FEHLER:  keine Berechtigung für Datenbank »...« SQL state: 42501
> Detail: Benutzer hat das CONNECT-Privileg nicht.
> Context: paralleler Arbeitsprozess"

This bug report is not actionable, because you didn't show what you did to provoke the error.  I did a little bit of
testingby guessing what you might be talking about, and I don't see a problem:
 

regression=# create role r;
CREATE ROLE
regression=# create database d;
CREATE DATABASE
regression=# revoke connect on database d from public; REVOKE regression=# \c d You are now connected to database "d"
asuser "postgres".
 
d=# set role r;                -- should work, and does
SET
d=> reset role;
RESET
d=# create user joe;
CREATE ROLE
d=# grant r to joe;
GRANT ROLE
d=# alter user joe set role = r;
ALTER ROLE
d=# \c d joe                   -- should fail, and does
connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  permission denied for database "d"
DETAIL:  User does not have CONNECT privilege.
Previous connection kept
d=# grant connect on database d to joe;
GRANT
d=# \c d joe                   -- now it should work, and does
You are now connected to database "d" as user "joe".
d=> show role;
 role
------
 r
(1 row)

Please provide a self-contained script that reproduces your problem.

            regards, tom lane