Subject: Yet more ROLE changes in v18 beta1???

From: Dominique Devienne <[email-masked]>
Date: Wed, 4 Jun 2025 15:52:42 +0200

(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

---

From: Robert Haas <[email-masked]>
Date: Wed, 4 Jun 2025 10:24:57 -0400

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

---

From: Dominique Devienne <[email-masked]>
Date: Wed, 4 Jun 2025 16:32:52 +0200

> Hi Dominique,

Hi Rober,

> 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

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 4 Jun 2025 08:29:44 -0700

> Connected to ... (17.4, server 18.0)

What is '(17.4, server 18.0)' referring to?

What is the actual test being run below?

-- 
Adrian Klaver
[email-masked]

---

From: Tom Lane <[email-masked]>
Date: Wed, 04 Jun 2025 11:34:50 -0400

> 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

---

From: Dominique Devienne <[email-masked]>
Date: Wed, 4 Jun 2025 18:39:58 +0200

> 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.

---

From: Dominique Devienne <[email-masked]>
Date: Wed, 4 Jun 2025 18:42:24 +0200

> 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

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 4 Jun 2025 10:53:37 -0700

> server 18.0 is the server version we're connected to.

What happens if you use the 18 version of libpq?

---

From: Ron Johnson <[email-masked]>
Date: Wed, 4 Jun 2025 19:17:02 -0400

> 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!

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 4 Jun 2025 17:30:42 -0700

On 6/4/25 16:17, Ron Johnson wrote:
> On Wed, Jun 4, 2025 at 1:53 PM Adrian Klaver <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
> 

> 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.

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 04 Jun 2025 19:52:01 -0500

> 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