Horton

Thread

  1. Yet more ROLE changes in v18 beta1???

    Dominique Devienne <ddevienne@gmail.com> — 2025-06-04T13:52:42Z

    (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

  2. Re: Yet more ROLE changes in v18 beta1???

    Robert Haas <robertmhaas@gmail.com> — 2025-06-04T14:24:57Z

    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

  3. Re: Yet more ROLE changes in v18 beta1???

    Dominique Devienne <ddevienne@gmail.com> — 2025-06-04T14:32:52Z

    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

  4. Re: Yet more ROLE changes in v18 beta1???

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-06-04T15:29:44Z

    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

  5. Re: Yet more ROLE changes in v18 beta1???

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-04T15:34:50Z

    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

  6. Re: Yet more ROLE changes in v18 beta1???

    Dominique Devienne <ddevienne@gmail.com> — 2025-06-04T16:39:58Z

    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.

  7. Re: Yet more ROLE changes in v18 beta1???

    Dominique Devienne <ddevienne@gmail.com> — 2025-06-04T16:42:24Z

    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

  8. Re: Yet more ROLE changes in v18 beta1???

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-06-04T17:53:37Z

    
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

  9. Re: Yet more ROLE changes in v18 beta1???

    Ron <ronljohnsonjr@gmail.com> — 2025-06-04T23:17:02Z

    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!

  10. Re: Yet more ROLE changes in v18 beta1???

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-06-05T00:30:42Z

    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

  11. Re: Yet more ROLE changes in v18 beta1???

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-06-05T00:52:01Z

    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

  12. Re: Yet more ROLE changes in v18 beta1???

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-05T02:57:19Z

    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

  13. Re: Yet more ROLE changes in v18 beta1???

    Dominique Devienne <ddevienne@gmail.com> — 2025-07-31T12:34:47Z

    On Thu, Jun 5, 2025 at 4:57 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

Of course Tom. And as a quick update, we've installed beta2 now, but
still get the same v18-specific failure, compared to v17 and v16 (see
below). I'll be looking at this soon. And will report back. --DD

```
D:\>set POSTGRESQL_UTCONFIG=C:\Users\ddevienne\ut_pg_config-18.json
D:\>tests_schemas -t ...  --pq-version
Connected to postgresql://ddevienne@.../ddevienne (17.4, server 18.0)
Running 1 test case...
unknown location(0): fatal error: in "...": class pq::Error: #42501
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:
*** tests_schemas in 0.596s (user: 0.062s) 23 MB

D:\>set POSTGRESQL_UTCONFIG=C:\Users\ddevienne\ut_pg_config-17.json
D:\>tests_schemas -t ... --pq-version
Connected to postgresql://ddevienne@.../ddevienne (17.4, server 17.5)
Running 1 test case...
*** No errors detected
*** tests_schemas in 0.706s (user: 0.062s) 25 MB

D:\>set POSTGRESQL_UTCONFIG=C:\Users\ddevienne\ut_pg_config-16.json
D:\>tests_schemas -t ... --pq-version
Connected to postgresql://ddevienne@.../ddevienne (17.4, server 16.9)
Running 1 test case...

*** No errors detected
*** tests_schemas in 0.612s (user: 0.047s) 26 MB
```

  14. Re: Yet more ROLE changes in v18 beta1???

    Dominique Devienne <ddevienne@gmail.com> — 2025-09-26T14:19:33Z

    On Thu, Jul 31, 2025 at 2:34 PM Dominique Devienne <ddevienne@gmail.com> wrote:
> On Thu, Jun 5, 2025 at 4:57 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 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.
>
> Of course Tom. And as a quick update, we've installed beta2 now, but
> still get the same v18-specific failure, compared to v17 and v16 (see
> below). I'll be looking at this soon. And will report back. --DD

To close out this thread, I'm happy to report that all is well with
v18 official.
Came a bit as a surprise, given our poor experience with betas and the RC,
but magically all tests pass on the official v18. So a GOOD surprise!

It's not impossible this is due to recent (unrelated) changes on our end...
Good thing I never investigated these v18beta/rc failures, I guess. --DD