public inbox for [email protected]help / color / mirror / Atom feed
Re: Latest patches break one of our unit-test, related to RLS 3+ messages / 2 participants [nested] [flat]
* Re: Latest patches break one of our unit-test, related to RLS @ 2025-09-12 12:22 Dominique Devienne <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Dominique Devienne @ 2025-09-12 12:22 UTC (permalink / raw) To: pgsql-general 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" ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Latest patches break one of our unit-test, related to RLS @ 2025-09-12 12:45 Laurenz Albe <[email protected]> parent: Dominique Devienne <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Laurenz Albe @ 2025-09-12 12:45 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; pgsql-general On Fri, 2025-09-12 at 14:22 +0200, Dominique Devienne wrote: > 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. That must be commit e3ffc3e91d. That commit fixed a bug in the conversion from SIMILAR TO expressions to POSIX regular expressions. You don't show us that data that match the pattern in 17.5, but not in 17.6. Unless you show us a counterexample, I'd say that the behavior in 17.6 is correct. Minor releases shouldn't change the behavior EXCEPT when the behavior is buggy. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Latest patches break one of our unit-test, related to RLS @ 2025-09-12 13:11 Dominique Devienne <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Dominique Devienne @ 2025-09-12 13:11 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: pgsql-general On Fri, Sep 12, 2025 at 2:45 PM Laurenz Albe <[email protected]> wrote: > On Fri, 2025-09-12 at 14:22 +0200, Dominique Devienne wrote: > > 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. > > That must be commit e3ffc3e91d. > > That commit fixed a bug in the conversion from SIMILAR TO > expressions to POSIX regular expressions. > > You don't show us that data that match the pattern in 17.5, but > not in 17.6. Unless you show us a counterexample, I'd say that > the behavior in 17.6 is correct. > > Minor releases shouldn't change the behavior EXCEPT when the > behavior is buggy. Can't get any simpler than the repro below, can it? So is this buggy or not? Clearly, there's a change in behavior. I tend to call a change in behavior as a regression myself :) But if someone can explain to me how what used to work was incorrect, compared to the documented behavior, I'm willing to change it of course. Any takers? --DD postgres=# show server_version; server_version ---------------- 16.9 (1 row) postgres=# with t(v) as (values ('foo:bar'), ('foo/bar'), ('foo0bar')) select v from t where v similar to 'foo[\d\w]_%'; v --------- foo0bar (1 row) postgres=# \c - - - 5416; psql (17.6, server 16.10) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: none) You are now connected to database "postgres" as user "postgres" on host "sr-pau-db" (address "10.65.53.13") at port "5416". postgres=# show server_version; server_version ---------------- 16.10 (1 row) postgres=# with t(v) as (values ('foo:bar'), ('foo/bar'), ('foo0bar')) select v from t where v similar to 'foo[\d\w]_%'; v --- (0 rows) postgres=# \c - - - 5475 psql (17.6, server 17.5) You are now connected to database "postgres" as user "postgres" on host "sr-pau-db" (address "10.65.53.13") at port "5475". postgres=# show server_version; server_version ---------------- 17.5 (1 row) postgres=# with t(v) as (values ('foo:bar'), ('foo/bar'), ('foo0bar')) select v from t where v similar to 'foo[\d\w]_%'; v --------- foo0bar (1 row) postgres=# \c - - - 5417 SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql) You are now connected to database "postgres" as user "postgres" on host "sr-pau-db" (address "10.65.53.13") at port "5417". postgres=# show server_version; server_version ---------------- 17.6 (1 row) postgres=# with t(v) as (values ('foo:bar'), ('foo/bar'), ('foo0bar')) select v from t where v similar to 'foo[\d\w]_%'; v --- (0 rows) postgres=# \c - - - 5481 psql (17.6, server 18rc1) WARNING: psql major version 17, server major version 18. Some psql features might not work. You are now connected to database "postgres" as user "postgres" on host "sr-pau-db" (address "10.65.53.13") at port "5481". postgres=# show server_version; server_version ---------------- 18rc1 (1 row) postgres=# with t(v) as (values ('foo:bar'), ('foo/bar'), ('foo0bar')) select v from t where v similar to 'foo[\d\w]_%'; v --- (0 rows) postgres=# ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-09-12 13:11 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-09-12 12:22 Re: Latest patches break one of our unit-test, related to RLS Dominique Devienne <[email protected]> 2025-09-12 12:45 ` Laurenz Albe <[email protected]> 2025-09-12 13:11 ` Dominique Devienne <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox