Re: DEFINER / INVOKER conundrum
> On 04/04/2023 07:55 CEST walt...@technowledgy.de wrote: > > 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. Nice. It's equivalent to my version without the domain if the client can execute SET ROLE before calling f, thereby injecting any role for which pg_has_role(session_user, calling_user, 'MEMBER') returns true. Dominique did not say whether he controls the clients or not. -- Erik
Re: jsonb @@ jsonpath operator doc: Only the first item of the result is taken into account
> On 04/04/2023 03:50 CEST jian he wrote: > > > "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." ? I omitted the "otherwise false" part because of the corner cases which I did not want to gloss over. But the corner cases also apply if the predicate matches some value, depending on strict mode, as I noticed later and wrote in my previous message. Suggestion: "Returns true if any JSON value at the given path matches the predicate, otherwise returns false. Unless the predicate compares different types (depending on strict mode) or the jsonpath is not a path predicate, in which case NULL is returned." I guess it's best to document the corner cases in detail in the notes section as Adrian pointed out and have the function doc refer to the notes. > also, should it be "Return true"? (since only one value returned)? The third-person singular "returns" is correct in this case. It does not refer to the number of returned values. -- Erik
Re: DEFINER / INVOKER conundrum
First, let me say I was holding off replying/thanking everyone to have the time to properly test this. Erik's quasi-question makes me break that silence. On Tue, Apr 4, 2023 at 10:46 AM Erik Wienhold wrote: > > On 04/04/2023 07:55 CEST walt...@technowledgy.de wrote: > > 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) > > Nice. It's equivalent to my version without the domain if the client can > execute SET ROLE before calling f, thereby injecting any role for which > pg_has_role(session_user, calling_user, 'MEMBER') returns true. > First, thanks to Christoph, Erik, and Walther, for helping out. Second, very nice indeed. I'll test it out soon. > Dominique did not say whether he controls the clients or not. > I would say I don't. The decision was made to go with a 2-tier architecture, so the desktop apps connect to PostgreSQL using a typically per-OS-user PostgreSQL LOGIN user, so even though it's our app's code that access the DB, so we control that part, nothing prevents those users to connect directly via psql or pgAdmin or any libpq or protocol-talking client, and try to do mischiefs or worse. Thus the server-side permission model must be as tight as it can be. Which means "regular" users don't have DDL privs, of course; thus the need for a mid-tier "more privileged" service to "mediate" the DDLs we sometimes need to do on behalf of "regular" users; thus the need to properly authenticate those users with the mid-tier services, based on the fact they can connect to the DB and its schema(s) and access/execute that JWT returning function. Hopefully that's clear :)
Re: Patroni vs pgpool II
On Mon, 3 Apr 2023 06:33:46 + Inzamam Shafiq wrote: [...] > 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? Load balancing is best achieved from the application side. The most popular auto failover solution is Patroni. Other solutions are involving Pacemaker to either: * build a shared storage cluster with a standalone instance moving from node to node (but this can include standbys) * build a cluster with a promotable resource using eg. the PAF resource agent, that will decide where to start the standbys and which one to promote. No matter the solution you pick, be prepared to learn and train. A lot.
Re: Patroni vs pgpool II
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? Having used pgpool in multiple production deployments I swore to never use it again, ever. The first reason is that you need a doctorate degree to try to understand how it actually works, what the pcp commands do in each scenario and how to correctly write the failover scripts. It is basically a daemon glued together with scripts for which you are entirely responsible for. Any small mistake in failover scripts and cluster enters a broken state. Even once you have it set up as it should, yes, it will fail over correctly but it won't autoheal without manual intervention. You also often end up in weird situation when backends are up, pgpool reports down and similar scenarios and then you need to run the precise sequence of pcp commands to recover or destroy your whole cluster in the process if you mistype. I haven't used patroni yet but it surely can't be worse. Best regards, cen
Re: jsonb @@ jsonpath operator doc: Only the first item of the result is taken into account
On 4/4/23 02:01, Erik Wienhold wrote: On 04/04/2023 03:50 CEST jian he wrote: "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." ? I omitted the "otherwise false" part because of the corner cases which I did not want to gloss over. But the corner cases also apply if the predicate matches some value, depending on strict mode, as I noticed later and wrote in my previous message. Suggestion: "Returns true if any JSON value at the given path matches the predicate, otherwise returns false. Unless the predicate compares different types (depending on strict mode) or the jsonpath is not a path predicate, in which case NULL is returned." That I understand. Thanks. I guess it's best to document the corner cases in detail in the notes section as Adrian pointed out and have the function doc refer to the notes. also, should it be "Return true"? (since only one value returned)? The third-person singular "returns" is correct in this case. It does not refer to the number of returned values. -- Erik -- Adrian Klaver adrian.kla...@aklaver.com
Re: Patroni vs pgpool II
Hi, > 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. I am not sure if Patroni provides load balancing feature. > Along with this, can you please share the company/client names using these > tools for large PG databases? I can't give you names but we (SRA OSS) have many customers using PostgreSQL and some of them are using Pgpool-II. Best reagards, -- Tatsuo Ishii SRA OSS LLC English: http://www.sraoss.co.jp/index_en/ Japanese:http://www.sraoss.co.jp
Re: Patroni vs pgpool II
Hi, On Wed, 5 Apr 2023 at 01:01, Tatsuo Ishii wrote: > Hi, > > I am not sure if Patroni provides load balancing feature. > It depends on understanding of load-balancing: - If we talk about load balancing read-only traffic across multiple replicas - it is very easy to achieve with Patroni. - If we talk about parsing all queries in order to figure out whether they are read-write or read-only, then no. BUT, even if there is a solution that parses queries to make a decision it I would not recommend anyone to use it unless all consequences are understood. Specifically, not every read-only query could be salefy sent to a replica, because they could be lagging behind the primary. Only application (developers) could decide whether for a specific query they could afford slightly outdated results. Most of the popular application frameworks support configuring two connection strings for this purpose. Regards, -- Alexander Kukushkin
