MIME-Version: 1.0
From: Dominique Devienne <ddevienne@gmail.com>
Date: Wed, 4 Jun 2025 15:52:42 +0200
Message-ID: <CAFCRh-_gUzbsUcr0LAazQZKYn4Mp-HrbgJXiybokM-GVTSZ=pg@mail.gmail.com>
Subject: Yet more ROLE changes in v18 beta1???
To: pgsql-general@postgresql.org
Cc: robertmhaas@gmail.com
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-_gUzbsUcr0LAazQZKYn4Mp-HrbgJXiybokM-GVTSZ%3Dpg%40mail.gmail.com>
Precedence: bulk

(Robert, you were kind enough to help me understand the v16 changes,
so I'm hoping you might have insight on this too. And this time
around, it's not one year too late I hope, since I'm testing betas
now. See https://www.postgresql.org/message-id/CA%2BTgmoZMqsg6-6qN_fuMZTGu%3DVdyjv-u9ZgWbEnOTvRE450uvQ%40mail.gmail.com).

Still on the subject of the recent v18 beta1 release, we're
discovering new unit-test failures, on a particularly sore subject for
us already, i.e. ROLE permission changes.

We are still full digesting the v16+ changes, but do have swallowed
already the "must have CREATEROLE *AND* ADMIN option" on the DROP'd
ROLE, and that's reflected in the fact our unit test works just fine
on v17 (show below, second output), and v16 (not shown).

But the exact same code run against v18.0 fails. I have not even tried
to analyze what's going on yet, but I certainly was not expecting new
changes after the ones introduced in v16, in that area. And since the
release notes are still partial (cf the "to be completed") at
https://www.postgresql.org/docs/18/release-18.html, and I'm not
finding anything related to ROLEs in what's there already, I wonder if
someone knows how ROLE permissions changes this time around, in v18?

Thanks, --DD

PS: I'm not ruling out our code is at fault. Especially since we have
conditional code based on the server version these days. But I'm
pre-emptively asking for known changes in this area, to the experts.

$ ./myruntests.csh tests_pdgm_schemas -t "..." --pq-version
Connected to ... (17.4, server 18.0)
Running 1 test case...
unknown location(0): fatal error: in "...": #42501: ERROR:  permission
denied to drop role
DETAIL:  Only roles with the CREATEROLE attribute and the ADMIN option
on role "...:ADMIN" may drop this role.

*** 1 failure is detected in the test module "Master Test Suite"
*** failure detail:
(1)     : ... failed. #42501: ERROR:  permission denied to drop role
DETAIL:  Only roles with the CREATEROLE attribute and the ADMIN option
on role "...:ADMIN" may drop this role.


*** tests_pdgm_schemas in 1.790s (user: 0.290s) 52 MB

$ vim .../postgres_test_config.xml
$ ./myruntests.csh tests_pdgm_schemas -t "..." --pq-version
Connected to ... (17.4, server 17.5)
Running 1 test case...

*** No errors detected
*** tests_pdgm_schemas in 1.700s (user: 0.300s) 53 MB