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
