RE: Cannot terminate backend
> From: Paul Ramsey > Afraid so. You can wait longer, I guess. You may have found two bugs… the > lack of an interrupt in the dbscan loop, which I’m working on now. And maybe > an infinite looping case? In which case if you want a fix on that, you’ll > have to share your data and query. I let the query run over the week-end and they are now gone, so I guess your dbscan fix is what I was missing. Thanks ! Regards Arnaud
Re: PostgreSQL
On Sun, 2023-04-02 at 23:21 +0200, Joseph Kennedy wrote: > I have one more question on this topic regarding Row Level Security. > > I would like to hide some rows for users who should have full rights to > database > eg. create Index, drop table, create tabel etc. but e.g. create index can be > done by owner or superusers but they are bypassed by Row Level Security. > > Is possible to use RLS for users who should have full rights to database ? If "full rights" implies dropping tables, the user must either be the owner of these tables or a superuser. You cannot use Row Level Security with a superuser. You can use ALTER TABLE ... FORCE ROW LEVEL SECURITY, so that Row Level Security applies to the table owner as well, but you cannot keep the owner from disabling that setting. Yours, Laurenz Albe
Re: Question on creating keys on partitioned tables
On Fri, 2023-03-31 at 10:35 -0700, Siddharth Jain wrote: > Is following correct? > > when a PK is created on (X,Y) on the parent table what happens internally is > that the > command is run individually on each of the child tables. nothing more. > nothing less. If you are talking about inheritance, no. Creating a constraint on the parent table has no effect on a child table. If you are talking about partitioning: If you create a primary key on the partitioned table, that will be implemented by a partitioned index. That partitioned index consists of a unique index for each partition. So I guess that is more or less what you mean. There are some differences between a primary key on the partitioned table and a unique index on each partition: - the primary key is a constraint, so it can be the target of a foreign key - the primary key will force a unique index on each new partition - the primary key keeps you from dropping the unique index on the partitions Yours, Laurenz Albe
DEFINER / INVOKER conundrum
My goal is to have clients connect to PostgreSQL, and call a function that return a JWT token. The JWT is supposed to capture the user (login role), and the current_role (which has meaning in our app), and sign it using a secret corresponding to a mid-tier service the client will connect to later. I've found https://github.com/michelp/pgjwt which seems perfect for my use case, but I'm struggling with something. On the one hand, I want a INVOKER security function, to be able to capture the login and current ROLEs. On the other hand, I want a DEFINER security function, to be able to access the secret to sign the JWT with. That secret will be in a table that regular users of our DB do NOT have access to, of course. But that the function doing the JWT signing does need access to, of course (again). I thought I'd have two layers of functions, one INVOKER that captures the ROLEs, which then calls the DEFINER one, passing the ROLEs captured, but since the INVOKER function must also be able to call the DEFINER function, what prevents the client from calling it directly, with different (spoofed) ROLEs? Is there a way out of that conundrum? I also thought about pg_stat_activity.usename with pg_backend_pid(), but there's no current_role in there, and also, I'm not sure how that would play with connection pooling!?!?!? So Is there a way to somehow mix INVOKER and DEFINER to achieve the stated goal? Or more broadly, mix information from the session and "private" information (JWT secret, part of the "app") in a server-side SQL function/procedure? Thanks, --DD
Re: Very slow queries followed by checkpointer process killed with signal 9
On 4/2/23 21:40, Tom Lane wrote: Jason McLaurin writes: Is there anywhere you'd suggest we start looking for hints? I'd be interested in increasing relevant logging verbosity so that we can see when key background processes are running, both in Postgres core and Timescale. It might be helpful to try to identify which wait events the slow queries are blocking on (pg_stat_activity.wait_event_type and .wait_event). I'm not sure if you're going to be able to extract useful data, because your query on pg_stat_activity is likely to be slow too. But it's a place to start. Also, given that you're evidently incurring the wrath of the OOM killer, you should try to understand why the kernel thinks it's under memory pressure. Do you have too many processes, or perhaps you've configured too much shared memory? Given this: This is Postgres 14.5 running in the TimescaleDB Docker image. Possibly the docker image is running with a cgroup memory.limit set? The OOM killer will trigger on any cgroup limit even if the host has plenty of free memory. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
Re: DEFINER / INVOKER conundrum
## Dominique Devienne (ddevie...@gmail.com):
> On the one hand, I want a INVOKER security function,
> to be able to capture the login and current ROLEs.
There's session_user ("the session user's name") which remains unchanged
on a SECURITY DEFINER function, and current_user ("the user name of the
current execution context") which changes according to the security
context set by SECURITY DEFINER/INVOKER.
-> https://www.postgresql.org/docs/current/functions-info.html
Regards,
Christoph
--
Spare Space.
Re: Very slow queries followed by checkpointer process killed with signal 9
## Tom Lane (t...@sss.pgh.pa.us):
> Jason McLaurin writes:
> > I'm troubleshooting an issue where about once a week, a database appears to
> > lock up and then the PostgreSQL process crashes and recovers. When this
> > happens, a few queries will be logged, but there is no pattern to which
> > queries are executing when the crash happens, and the queries logged don't
> > appear to be queries that would consume a lot of resources.
>
> Hmm, is it always the checkpointer that gets the OOM kill? That seems
> quite odd.
Are you sure it's OOM? That's a frequent source of SIGKILLs, but
SIGKILL is not exclusive to oomkiller. Maybe something in the
container stack (some cgroup resource limiting? would be in line
with slowdown at least; or a hard ulimit on cpu time?) or maybe even
some "anti virus" thingy ("It's been 0 days since AV ate the database").
Regards,
Christoph
--
Spare Space.
Re: Patroni vs pgpool II
On 4/3/23 01:33, Inzamam Shafiq wrote: Hi Guys, Hope you are doing well. Can someone please suggest what is one (Patroni vs PGPool II) is best for achieving HA/Auto failover, Load balancing for DB servers. Along with this, can you please share the company/client names using these tools for large PG databases? We're satisfied with PgPool for HA. Can't give the same, and it's only a few hundred GB, though. -- Born in Arizona, moved to Babylonia.
Re: DEFINER / INVOKER conundrum
> On 03/04/2023 13:18 CEST Dominique Devienne wrote: > > My goal is to have clients connect to PostgreSQL, > and call a function that return a JWT token. > > The JWT is supposed to capture the user (login role), > and the current_role (which has meaning in our app), > and sign it using a secret corresponding to a mid-tier > service the client will connect to later. > > I've found https://github.com/michelp/pgjwt which seems > perfect for my use case, but I'm struggling with something. > > On the one hand, I want a INVOKER security function, > to be able to capture the login and current ROLEs. > > On the other hand, I want a DEFINER security function, > to be able to access the secret to sign the JWT with. > > That secret will be in a table that regular users of our DB > do NOT have access to, of course. But that the function > doing the JWT signing does need access to, of course (again). > > I thought I'd have two layers of functions, one INVOKER > that captures the ROLEs, which then calls the DEFINER one, > passing the ROLEs captured, but since the INVOKER function > must also be able to call the DEFINER function, what prevents > the client from calling it directly, with different (spoofed) ROLEs? > > Is there a way out of that conundrum? A single DEFINER function works if you capture current_user with a parameter and default value. Let's call it claimed_role. Use pg_has_role[0] to check that session_user has the privilege for claimed_role (in case the function is called with an explicit value), otherwise raise an exception. Connect as postgres: CREATE FUNCTION f(claimed_role text default current_user) RETURNS TABLE (claimed_role text, curr_user text, sess_user text) SECURITY DEFINER LANGUAGE sql $$ SELECT claimed_role, current_user, session_user $$; Connect as alice: SELECT * FROM f(); claimed_role | curr_user | sess_user --+---+--- alice| postgres | alice (1 row) [0] https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE -- Erik
Re: jsonb @@ jsonpath operator doc: Only the first item of the result is taken into account
> On 02/04/2023 17:40 CEST Adrian Klaver wrote: > > That is a long way from: > > jsonb @@ jsonpath → boolean > > Returns the result of a JSON path predicate check for the specified JSON > value. Only the first item of the result is taken into account. If the > result is not Boolean, then NULL is returned. What do you mean? I responded to the OP's question. It's not a suggestion to update the docs. Obviously it's quite a mouthful and needs to be boiled down for the docs. Any suggestions? -- Erik
Re: jsonb @@ jsonpath operator doc: Only the first item of the result is taken into account
Erik Wienhold writes: > What do you mean? I responded to the OP's question. It's not a suggestion > to update the docs. Obviously it's quite a mouthful and needs to be boiled > down for the docs. Any suggestions? At least for the example at hand, I think a good part of the confusion stems from the undocumented way that the boolean test operator works. As far as I can find, the only mention of that is in Section 9.16.2 [1], which says (just before 9.16.2.1): PostgreSQL's implementation of the SQL/JSON path language has the following deviations from the SQL/JSON standard: A path expression can be a Boolean predicate, although the SQL/JSON standard allows predicates only in filters. This is necessary for implementation of the @@ operator. For example, the following jsonpath expression is valid in PostgreSQL: $.track.segments[*].HR < 70 That is a complete documentation fail if you ask me, because it doesn't explain what the construct actually does, nor is there anyplace else where you could find that out, not even the SQL standard. So I'd start by fleshing that out, and then see what to do with the description of the @@ operator (and the equivalent jsonb_path_match function). Some more examples for those might help. regards, tom lane [1] https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH
Re: jsonb @@ jsonpath operator doc: Only the first item of the result is taken into account
On 4/3/23 08:11, Erik Wienhold wrote: On 02/04/2023 17:40 CEST Adrian Klaver wrote: That is a long way from: jsonb @@ jsonpath → boolean Returns the result of a JSON path predicate check for the specified JSON value. Only the first item of the result is taken into account. If the result is not Boolean, then NULL is returned. What do you mean? I responded to the OP's question. It's not a suggestion to update the docs. Obviously it's quite a mouthful and needs to be boiled down for the docs. Any suggestions? For me I don't see how: Predicates have existence semantics, because their operands are item sequences. Pairs of items from the left and right operand's sequences are checked. TRUE returned only if any pair satisfying the condition is found. In strict mode, even if the desired pair has already been found, all pairs still need to be examined to check the absence of errors. If any error occurs, UNKNOWN (analogous to SQL NULL) is returned. resolves to : Only the first item of the result is taken into account. In other words reconciling "TRUE returned only if any pair satisfying the condition is found." and "...first item of the result..." -- Erik -- Adrian Klaver adrian.kla...@aklaver.com
Re: jsonb @@ jsonpath operator doc: Only the first item of the result is taken into account
> On 03/04/2023 17:36 CEST Adrian Klaver wrote: > > On 4/3/23 08:11, Erik Wienhold wrote: > >> On 02/04/2023 17:40 CEST Adrian Klaver wrote: > >> > >> That is a long way from: > >> > >> jsonb @@ jsonpath → boolean > >> > >> Returns the result of a JSON path predicate check for the specified JSON > >> value. Only the first item of the result is taken into account. If the > >> result is not Boolean, then NULL is returned. > > > > What do you mean? I responded to the OP's question. It's not a suggestion > > to update the docs. Obviously it's quite a mouthful and needs to be boiled > > down for the docs. Any suggestions? > > For me I don't see how: > > Predicates have existence semantics, because their operands are item > sequences. Pairs of items from the left and right operand's sequences > are checked. TRUE returned only if any pair satisfying the condition is > found. In strict mode, even if the desired pair has already been found, > all pairs still need to be examined to check the absence of errors. If > any error occurs, UNKNOWN (analogous to SQL NULL) is returned. > > resolves to : > > Only the first item of the result is taken into account. > > In other words reconciling "TRUE returned only if any pair satisfying > the condition is found." and "...first item of the result..." I see. Thinking about it now, I believe that "first item of the result" is redundant (and causing the OP's confusion) because the path predicate produces only a single item: true, false, or null. That's what I wanted to show with the first two jsonb_path_query examples in my initial response, where the second example returns multiple items. I think the gist of @@ and json_path_match is: "Returns true if any JSON value at the given path matches the predicate. Returns NULL when not a path predicate or comparing different types." -- Erik
Re: jsonb @@ jsonpath operator doc: Only the first item of the result is taken into account
On 4/3/23 09:21, Erik Wienhold wrote:
On 03/04/2023 17:36 CEST Adrian Klaver wrote:
On 4/3/23 08:11, Erik Wienhold wrote:
On 02/04/2023 17:40 CEST Adrian Klaver wrote:
That is a long way from:
jsonb @@ jsonpath → boolean
Returns the result of a JSON path predicate check for the specified JSON
value. Only the first item of the result is taken into account. If the
result is not Boolean, then NULL is returned.
What do you mean? I responded to the OP's question. It's not a suggestion
to update the docs. Obviously it's quite a mouthful and needs to be boiled
down for the docs. Any suggestions?
For me I don't see how:
Predicates have existence semantics, because their operands are item
sequences. Pairs of items from the left and right operand's sequences
are checked. TRUE returned only if any pair satisfying the condition is
found. In strict mode, even if the desired pair has already been found,
all pairs still need to be examined to check the absence of errors. If
any error occurs, UNKNOWN (analogous to SQL NULL) is returned.
resolves to :
Only the first item of the result is taken into account.
In other words reconciling "TRUE returned only if any pair satisfying
the condition is found." and "...first item of the result..."
I see.
Thinking about it now, I believe that "first item of the result" is redundant
(and causing the OP's confusion) because the path predicate produces only a
single item: true, false, or null. That's what I wanted to show with the first
two jsonb_path_query examples in my initial response, where the second example
returns multiple items.
I think the gist of @@ and json_path_match is:
"Returns true if any JSON value at the given path matches the predicate.
Returns NULL when not a path predicate or comparing different types."
So basically a variation of jsonb @? jsonpath that returns NULL instead
of false when confused:
select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ == "test")' ;
?column?
--
f
select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] == "test"' ;
?column?
--
NULL
Otherwise it does the same thing:
select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' ;
?column?
--
t
select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' ;
?column?
--
t
--
Erik
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: jsonb @@ jsonpath operator doc: Only the first item of the result is taken into account
> On 03/04/2023 18:37 CEST Adrian Klaver wrote:
>
> On 4/3/23 09:21, Erik Wienhold wrote:
> >> On 03/04/2023 17:36 CEST Adrian Klaver wrote:
> >>
> >> On 4/3/23 08:11, Erik Wienhold wrote:
> On 02/04/2023 17:40 CEST Adrian Klaver wrote:
>
> That is a long way from:
>
> jsonb @@ jsonpath → boolean
>
> Returns the result of a JSON path predicate check for the specified JSON
> value. Only the first item of the result is taken into account. If the
> result is not Boolean, then NULL is returned.
> >>>
> >>> What do you mean? I responded to the OP's question. It's not a
> >>> suggestion
> >>> to update the docs. Obviously it's quite a mouthful and needs to be
> >>> boiled
> >>> down for the docs. Any suggestions?
> >>
> >> For me I don't see how:
> >>
> >> Predicates have existence semantics, because their operands are item
> >> sequences. Pairs of items from the left and right operand's sequences
> >> are checked. TRUE returned only if any pair satisfying the condition is
> >> found. In strict mode, even if the desired pair has already been found,
> >> all pairs still need to be examined to check the absence of errors. If
> >> any error occurs, UNKNOWN (analogous to SQL NULL) is returned.
> >>
> >> resolves to :
> >>
> >> Only the first item of the result is taken into account.
> >>
> >> In other words reconciling "TRUE returned only if any pair satisfying
> >> the condition is found." and "...first item of the result..."
> >
> > I see.
> >
> > Thinking about it now, I believe that "first item of the result" is
> > redundant
> > (and causing the OP's confusion) because the path predicate produces only a
> > single item: true, false, or null. That's what I wanted to show with the
> > first
> > two jsonb_path_query examples in my initial response, where the second
> > example
> > returns multiple items.
> >
> > I think the gist of @@ and json_path_match is:
> >
> > "Returns true if any JSON value at the given path matches the predicate.
> > Returns NULL when not a path predicate or comparing different types."
>
> So basically a variation of jsonb @? jsonpath that returns NULL instead
> of false when confused:
>
> select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ == "test")' ;
> ?column?
> --
> f
The filter expression does not match any values because predicate '@ == "test"'
returns unknown. This follows SQL's three-valued logic.
"? (condition)
[...] The result of that step is filtered to include only those items that
satisfy the provided condition. SQL/JSON defines three-valued logic, so the
condition can be true, false, or unknown. The unknown value plays the same role
as SQL NULL and can be tested for with the is unknown predicate. Further path
evaluation steps use only those items for which the filter expression returned
true."https://www.postgresql.org/docs/current/functions-json.html
> select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] == "test"' ;
> ?column?
> --
> NULL
In this case @@ returns null because the predicate returns unknown for all array
elements. It gets interesting in strict mode.
Lax mode (default) with an array element of matching type found by the
predicate:
select '{"a":[1,2,3,4,5,"test"]}'::jsonb @@ '$.a[*] == "test"';
?column?
--
t
(1 row)
In strict mode the unknown result for the first array element causes the
predicate evaluation to short-circuit and return unknown right away instead
of testing the remaining elements:
select '{"a":[1,2,3,4,5,"test"]}'::jsonb @@ 'strict $.a[*] == "test"';
?column?
--
NULL
(1 row)
> Otherwise it does the same thing:
>
> select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' ;
> ?column?
> --
> t
>
> select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' ;
> ?column?
> --
> t
Yes, if the LHS and RHS types match. The equivalence is also supported by
documentation in src/backend/utils/adt/jsonb_gin.c:
The operators support, among the others, "jsonb @? jsonpath" and
"jsonb @@ jsonpath". Expressions containing these operators are easily
expressed through each other.
jb @? 'path' <=> jb @@ 'EXISTS(path)'
jb @@ 'expr' <=> jb @? '$ ? (expr)'
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/jsonb_gin.c;h=e941439d7493365f8954c791f0e2368c080189b8;hb=HEAD#l15
--
Erik
Re: jsonb @@ jsonpath operator doc: Only the first item of the result is taken into account
On 4/3/23 12:00, Erik Wienhold wrote:
On 03/04/2023 18:37 CEST Adrian Klaver wrote:
On 4/3/23 09:21, Erik Wienhold wrote:
On 03/04/2023 17:36 CEST Adrian Klaver wrote:
On 4/3/23 08:11, Erik Wienhold wrote:
On 02/04/2023 17:40 CEST Adrian Klaver wrote:
That is a long way from:
jsonb @@ jsonpath → boolean
Returns the result of a JSON path predicate check for the specified JSON
value. Only the first item of the result is taken into account. If the
result is not Boolean, then NULL is returned.
What do you mean? I responded to the OP's question. It's not a suggestion
to update the docs. Obviously it's quite a mouthful and needs to be boiled
down for the docs. Any suggestions?
For me I don't see how:
Predicates have existence semantics, because their operands are item
sequences. Pairs of items from the left and right operand's sequences
are checked. TRUE returned only if any pair satisfying the condition is
found. In strict mode, even if the desired pair has already been found,
all pairs still need to be examined to check the absence of errors. If
any error occurs, UNKNOWN (analogous to SQL NULL) is returned.
resolves to :
Only the first item of the result is taken into account.
In other words reconciling "TRUE returned only if any pair satisfying
the condition is found." and "...first item of the result..."
I see.
Thinking about it now, I believe that "first item of the result" is redundant
(and causing the OP's confusion) because the path predicate produces only a
single item: true, false, or null. That's what I wanted to show with the first
two jsonb_path_query examples in my initial response, where the second example
returns multiple items.
I think the gist of @@ and json_path_match is:
"Returns true if any JSON value at the given path matches the predicate.
Returns NULL when not a path predicate or comparing different types."
So basically a variation of jsonb @? jsonpath that returns NULL instead
of false when confused:
select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ == "test")' ;
?column?
--
f
The filter expression does not match any values because predicate '@ == "test"'
returns unknown. This follows SQL's three-valued logic.
"? (condition)
[...] The result of that step is filtered to include only those items that
satisfy the provided condition. SQL/JSON defines three-valued logic, so the
condition can be true, false, or unknown. The unknown value plays the same
role
as SQL NULL and can be tested for with the is unknown predicate. Further path
evaluation steps use only those items for which the filter expression returned
true."https://www.postgresql.org/docs/current/functions-json.html
select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] == "test"' ;
?column?
--
NULL
In this case @@ returns null because the predicate returns unknown for all array
elements. It gets interesting in strict mode.
Lax mode (default) with an array element of matching type found by the
predicate:
select '{"a":[1,2,3,4,5,"test"]}'::jsonb @@ '$.a[*] == "test"';
?column?
--
t
(1 row)
In strict mode the unknown result for the first array element causes the
predicate evaluation to short-circuit and return unknown right away instead
of testing the remaining elements:
select '{"a":[1,2,3,4,5,"test"]}'::jsonb @@ 'strict $.a[*] == "test"';
?column?
--
NULL
(1 row)
So from here:
https://www.postgresql.org/docs/current/functions-json.html
"
Note
The jsonpath operators @? and @@ suppress the following errors: missing
object field or array element, unexpected JSON item type, datetime and
numeric errors. The jsonpath-related functions described below can also
be told to suppress these types of errors. This behavior might be
helpful when searching JSON document collections of varying structure.
"
Needs to be updated to indicate that the above is true when in the
default lax mode, but changes if strict mode is specified.
Otherwise it does the same thing:
select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' ;
?column?
--
t
select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' ;
?column?
--
t
Yes, if the LHS and RHS types match. The equivalence is also supported by
documentation in src/backend/utils/adt/jsonb_gin.c:
The operators support, among the others, "jsonb @? jsonpath" and
"jsonb @@ jsonpath". Expressions containing these operators are easily
expressed through each other.
jb @? 'path' <=> jb @@ 'EXISTS(path)'
jb @@ 'expr' <=> jb @? '$ ? (expr)'
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/jsonb_gin.c;h=e941439d7493365f8954c791f0e2368c080189b8;hb=HEAD#l15
--
Erik
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Oracle to PostgreSQL Migration
This is a little left field… but we’ve migrated a lot of onprem oracle databases to Postgres (on aws rds) using aws database migration service - which has worked very well and supports cdc. I do wonder if you did the above and then add a 2nd cdc out from Postgres rds to your onprem Postgres. So onprem oracle -> rds Postgres -> onprem Postgres (all working simultaneously) (It wouldn’t work onprem oracle to onprem Postgres without a aws cloud setup) Just a random thought and fairly straightforward to setup. On Tue, 28 Mar 2023 at 19:27, Laurenz Albe wrote: > On Fri, 2023-03-24 at 08:00 +, Inzamam Shafiq wrote: > > Thanks, oracle_fdw can be used, but it doesn't provide real time sync, > we have to > > schedule jobs to insert data in actual schema from foreign tables, so > some delay might be there. > > In addition to the commercial tools that Christophe mentioned, and which > might be > your best option, let me point out that ora_migrator > (https://github.com/cybertec-postgresql/ora_migrator), which is open > source and based > on oracle_fdw, offers some degree of what you are looking for using a > cursor-based soution. > Certainly net refined and certainly not feasible in cases with high > transactional > workload, but perhaps it can be useful for you. > > Yours, > Laurenz Albe > > > -- Sent from iPhone
Re: jsonb @@ jsonpath operator doc: Only the first item of the result is taken into account
On Tue, Apr 4, 2023 at 12:22 AM Erik Wienhold wrote: > > On 03/04/2023 17:36 CEST Adrian Klaver > wrote: > > > > On 4/3/23 08:11, Erik Wienhold wrote: > > >> On 02/04/2023 17:40 CEST Adrian Klaver > wrote: > > >> > > >> That is a long way from: > > >> > > >> jsonb @@ jsonpath → boolean > > >> > > >> Returns the result of a JSON path predicate check for the specified > JSON > > >> value. Only the first item of the result is taken into account. If the > > >> result is not Boolean, then NULL is returned. > > > > > > What do you mean? I responded to the OP's question. It's not a > suggestion > > > to update the docs. Obviously it's quite a mouthful and needs to be > boiled > > > down for the docs. Any suggestions? > > > > For me I don't see how: > > > > Predicates have existence semantics, because their operands are item > > sequences. Pairs of items from the left and right operand's sequences > > are checked. TRUE returned only if any pair satisfying the condition is > > found. In strict mode, even if the desired pair has already been found, > > all pairs still need to be examined to check the absence of errors. If > > any error occurs, UNKNOWN (analogous to SQL NULL) is returned. > > > > resolves to : > > > > Only the first item of the result is taken into account. > > > > In other words reconciling "TRUE returned only if any pair satisfying > > the condition is found." and "...first item of the result..." > > I see. > > Thinking about it now, I believe that "first item of the result" is > redundant > (and causing the OP's confusion) because the path predicate produces only a > single item: true, false, or null. That's what I wanted to show with the > first > two jsonb_path_query examples in my initial response, where the second > example > returns multiple items. > > I think the gist of @@ and json_path_match is: > > "Returns true if any JSON value at the given path matches the predicate. > Returns NULL when not a path predicate or comparing different types." > > -- > Erik > > > "Returns true if any JSON value at the given path matches the predicate. > Returns NULL when not a path predicate or comparing different types." > in first sentence, should we add something "otherwise return false." ? also, should it be "Return true"? (since only one value returned)?
Re: DEFINER / INVOKER conundrum
Erik Wienhold: A single DEFINER function works if you capture current_user with a parameter and default value. Let's call it claimed_role. Use pg_has_role[0] to check that session_user has the privilege for claimed_role (in case the function is called with an explicit value), otherwise raise an exception. Connect as postgres: CREATE FUNCTION f(claimed_role text default current_user) RETURNS TABLE (claimed_role text, curr_user text, sess_user text) SECURITY DEFINER LANGUAGE sql $$ SELECT claimed_role, current_user, session_user $$; For me, checking whether session_user has the privilege for claimed_role is not enough, so I add a DOMAIN to the mix: CREATE DOMAIN current_user_only AS NAME CHECK (VALUE = CURRENT_USER); CREATE FUNCTION f(calling_user current_user_only DEFAULT CURRENT_USER) ... SECURITY DEFINER; This works, because the domain check is evaluated in the calling context. Best, Wolfgang
