Thread: Yet more ROLE changes in v18 beta1???
(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
Hi Dominique, Thanks for testing. This time, whatever is going wrong here is probably not my fault, because I don't think I changed anything in this area for v18. Actually, I'm unaware of anyone else having made significant changes either, but that could very easily be a case of me not paying enough attention. I think we might need to know more about what exactly happened in order to track it down. -- Robert Haas EDB: http://www.enterprisedb.com
On Wed, Jun 4, 2025 at 4:25 PM Robert Haas <robertmhaas@gmail.com> wrote: > Hi Dominique, Hi Rober, > Thanks for testing. This time, whatever is going wrong here is > probably not my fault, because I don't think I changed anything in > this area for v18. Actually, I'm unaware of anyone else having made > significant changes either, but that could very easily be a case of me > not paying enough attention. I think we might need to know more about > what exactly happened in order to track it down. OK, good to know. And thanks for the quick answer. Unfortunately, digging into this is not something I can do right away. v18 is still a few months out, I do hope I can investigate before that. In any case, if anyone else knows about changes in this area, I'm interested. Thanks, --DD
On 6/4/25 06:52, Dominique Devienne wrote: > 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) What is '(17.4, server 18.0)' referring to? What is the actual test being run below? > 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 > > -- Adrian Klaver adrian.klaver@aklaver.com
Dominique Devienne <ddevienne@gmail.com> writes: > In any case, if anyone else knows about changes in this area, I'm interested. Digging through the commit log didn't find much, but conceivably https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=01463e1cc Ensure that AFTER triggers run as the instigating user. regards, tom lane
On Wed, Jun 4, 2025 at 5:29 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > $ ./myruntests.csh tests_pdgm_schemas -t "..." --pq-version > > Connected to ... (17.4, server 18.0) > > What is '(17.4, server 18.0)' referring to? > > What is the actual test being run below? 17.4 is the client-side version, i.e. the LibPQ used. server 18.0 is the server version we're connected to.
On Wed, Jun 4, 2025 at 5:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dominique Devienne <ddevienne@gmail.com> writes: > > In any case, if anyone else knows about changes in this area, I'm interested. > > Digging through the commit log didn't find much, but conceivably > > https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=01463e1cc > Ensure that AFTER triggers run as the instigating user. Thanks Tom. We don't drop ROLEs in trigger (yet, we'll get there). So that particular commit is probably not the reason. Thanks a bunch for looking into it. --DD
On 6/4/25 9:39 AM, Dominique Devienne wrote: > On Wed, Jun 4, 2025 at 5:29 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: >>> $ ./myruntests.csh tests_pdgm_schemas -t "..." --pq-version >>> Connected to ... (17.4, server 18.0) >> >> What is '(17.4, server 18.0)' referring to? >> >> What is the actual test being run below? > > 17.4 is the client-side version, i.e. the LibPQ used. > server 18.0 is the server version we're connected to. What happens if you use the 18 version of libpq? -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Jun 4, 2025 at 1:53 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 6/4/25 9:39 AM, Dominique Devienne wrote:
> On Wed, Jun 4, 2025 at 5:29 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>> $ ./myruntests.csh tests_pdgm_schemas -t "..." --pq-version
>>> Connected to ... (17.4, server 18.0)
>>
>> What is '(17.4, server 18.0)' referring to?
>>
>> What is the actual test being run below?
>
> 17.4 is the client-side version, i.e. the LibPQ used.
> server 18.0 is the server version we're connected to.
What happens if you use the 18 version of libpq?
Shouldn't the client version only matter for the SQL generated by psql meta-commands? (Because what meta-commands drop roles?)
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On 6/4/25 16:17, Ron Johnson wrote: > On Wed, Jun 4, 2025 at 1:53 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > > Shouldn't the client version only matter for the SQL generated by psql > meta-commands? (Because what meta-commands drop roles?) I would think if the version did not matter there would not be versions. In particular: https://www.postgresql.org/docs/18/protocol-overview.html#PROTOCOL-VERSIONS I don't think that applies here, but it would not hurt to eliminate the possibility. What would be really useful though is the actual test SQL being run so others can confirm/not confirm the error. > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, 2025-06-04 at 18:42 +0200, Dominique Devienne wrote: > On Wed, Jun 4, 2025 at 5:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Dominique Devienne <ddevienne@gmail.com> writes: > > > In any case, if anyone else knows about changes in this area, I'm interested. > > > > Digging through the commit log didn't find much, but conceivably > > > > https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=01463e1cc > > Ensure that AFTER triggers run as the instigating user. > > Thanks Tom. We don't drop ROLEs in trigger (yet, we'll get there). > So that particular commit is probably not the reason. > Thanks a bunch for looking into it. --DD That change is not about dropping roles, but: - before, if you had a deferred constraint trigger that was triggered while you temporarily assumed a different role (e.g., the DML statement is executed in a SECURITY DEFINER function), the trigger was executed as the current user at commit time - from v18 on, the trigger gets executed as the user that ran the DML statement I would be somewhat surprised if you hit this rather exotic case that went unnoticed for many years, but it is possible. Do you have deferred constraint triggers? Yours, Laurenz Albe
Dominique Devienne <ddevienne@gmail.com> writes:
> Unfortunately, digging into this is not something I can do right away.
> v18 is still a few months out, I do hope I can investigate before that.
It'd be good if you could prioritize that a bit more. If there is
a bug rather than an intentional change here, we'd much rather
find it and fix it before 18.0 comes out.
regards, tom lane