public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dominique Devienne <[email protected]>
To: [email protected]
Subject: Re: Latest patches break one of our unit-test, related to RLS
Date: Fri, 12 Sep 2025 14:22:54 +0200
Message-ID: <CAFCRh-93hsRvoJqQCwXq06=AJJJ_s783gVsGHKc-QjgOO6jGVg@mail.gmail.com> (raw)
In-Reply-To: <CAFCRh-8NwJd0jq6P=R3qhHyqU7hw0BTor3W0SvUcii24et+zAw@mail.gmail.com>
References: <CAFCRh-8NwJd0jq6P=R3qhHyqU7hw0BTor3W0SvUcii24et+zAw@mail.gmail.com>
On Thu, Sep 4, 2025 at 5:03 PM Dominique Devienne <[email protected]> wrote:
> OK with 16.9 and 17.5 (we cannot test on beta2 anymore)
> KO with 16.10 and 17.6 (and beta3 too, released at the same time)
I've tracked down the regression to this particular query, FWIW:
select rolname, rolsuper, rolinherit, rolcreaterole,
rolcreatedb, rolcanlogin, rolreplication, rolbypassrls,
oid, shobj_description(oid, 'pg_authid')
from pg_roles
where rolname SIMILAR TO $1 AND pg_has_role(oid, 'SET')
order by rolname
In 17.5, returns 3 rows.
In 17.6, returns 0 rows.
I've used a libpq trace on both, diff'd them, and it's the 1st
significant difference.
Given my troubles with roles, I immediately imagined a change in pg_has_role().
But turns out, it's SIMILAR TO that changed. See trace extract below
for both versions.
On 17.6
On the two queries below, the first is the real one,
(modulo some mild renaming, to Acme and FOO)
and the second is one where I replaced the [\d\w] with a _
The correct answer for the 1st should be (7 rows).
This particular char I test on, can be / and :
but I want to avoid those entries, thus [\d\w]
So, it this a regression? A bug fix, and my pattern is somehow wrong?
If it's not a bug/regression, what do you suggest we use instead?
If it is a bug, any chance it might be in the upcoming v18 release
(and associated earlier version patches???)
I think I've found the smoking gun. Haven't verified whether our
troubles with v18 pre-releases is related.
Thanks, --DD
PS: From those 7 rows, pg_has_role() is supposed to narrow it down to 3.
acme=> select rolname from pg_roles where rolname similar to
'Acme-FOO:8n8igcOH[\d\w]_____________:%' order by 1;
rolname
---------
(0 rows)
acme=> select rolname from pg_roles where rolname similar to
'Acme-FOO:8n8igcOH______________:%' order by 1;
rolname
-------------------------------------------
...
(14 rows)
And the same as above, on 17.5:
acme=> select rolname from pg_roles where rolname similar to
'Acme-FOO:8vjqDaeT[\d\w]_____________:%' order by 1;
rolname
----------------------------------------
...
(7 rows)
acme=> select rolname from pg_roles where rolname similar to
'Acme-FOO:8vjqDaeT______________:%' order by 1;
rolname
-------------------------------------------
...
(14 rows)
--------------- llibpq traces -------------------
17.5
2025-09-12 13:50:32.267733 B 5 ReadyForQuery I
2025-09-12 13:50:32.267772 F 270 Parse "" "
select rolname, rolsuper, rolinherit, rolcreaterole,
rolcreatedb, rolcanlogin, rolreplication, rolbypassrls,
oid, shobj_description(oid, 'pg_authid')
from pg_roles
where rolname SIMILAR TO $1 AND pg_has_role(oid, 'SET')
order by rolname
" 1 25
2025-09-12 13:50:32.267775 F 58 Bind "" "" 1 1 1 38
'Acme-FOO:8vjqDaeT[\d\w]_____________:%' 1 1
2025-09-12 13:50:32.267777 F 6 Describe P ""
2025-09-12 13:50:32.267779 F 9 Execute "" 0
2025-09-12 13:50:32.267780 F 4 Sync
2025-09-12 13:50:32.271148 B 4 ParseComplete
2025-09-12 13:50:32.271161 B 4 BindComplete
2025-09-12 13:50:32.271165 B 302 RowDescription 10 "rolname" 12000 1
19 64 -1 1 "rolsuper" 12000 2 16 1 -1 1 "rolinherit" 12000 3 16 1 -1 1
"rolcreaterole" 12000 4 16 1 -1 1 "rolcreatedb" 12000 5 16 1 -1 1
"rolcanlogin" 12000 6 16 1 -1 1 "rolreplication" 12000 7 16 1 -1 1
"rolbypassrls" 12000 11 16 1 -1 1 "oid" 12000 13 26 4 -1 1
"shobj_description" 0 0 25 65535 -1 1
...
2025-09-12 13:50:32.271200 B 13 CommandComplete "SELECT 3"
in 17.6
2025-09-12 13:50:52.512043 B 5 ReadyForQuery I
2025-09-12 13:50:52.512082 F 270 Parse "" "
select rolname, rolsuper, rolinherit, rolcreaterole,
rolcreatedb, rolcanlogin, rolreplication, rolbypassrls,
oid, shobj_description(oid, 'pg_authid')
from pg_roles
where rolname SIMILAR TO $1 AND pg_has_role(oid, 'SET')
order by rolname
" 1 25
2025-09-12 13:50:52.512085 F 58 Bind "" "" 1 1 1 38
'Acme-FOO:8n8igcOH[\d\w]_____________:%' 1 1
2025-09-12 13:50:52.512088 F 6 Describe P ""
2025-09-12 13:50:52.512089 F 9 Execute "" 0
2025-09-12 13:50:52.512091 F 4 Sync
2025-09-12 13:50:52.540088 B 4 ParseComplete
2025-09-12 13:50:52.540104 B 4 BindComplete
2025-09-12 13:50:52.540109 B 302 RowDescription 10 "rolname" 12000 1
19 64 -1 1 "rolsuper" 12000 2 16 1 -1 1 "rolinherit" 12000 3 16 1 -1 1
"rolcreaterole" 12000 4 16 1 -1 1 "rolcreatedb" 12000 5 16 1 -1 1
"rolcanlogin" 12000 6 16 1 -1 1 "rolreplication" 12000 7 16 1 -1 1
"rolbypassrls" 12000 11 16 1 -1 1 "oid" 12000 13 26 4 -1 1
"shobj_description" 0 0 25 65535 -1 1
2025-09-12 13:50:52.540119 B 13 CommandComplete "SELECT 0"
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Latest patches break one of our unit-test, related to RLS
In-Reply-To: <CAFCRh-93hsRvoJqQCwXq06=AJJJ_s783gVsGHKc-QjgOO6jGVg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox