Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rybdk-00H6VT-Vj for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 18:10:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rybdj-00FST4-Ln for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 18:10:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rybdj-00FSSt-A4 for pgsql-general@lists.postgresql.org; Sun, 21 Apr 2024 18:10:11 +0000 Received: from mail-qk1-x735.google.com ([2607:f8b0:4864:20::735]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rybdg-002Cae-UE for pgsql-general@lists.postgresql.org; Sun, 21 Apr 2024 18:10:10 +0000 Received: by mail-qk1-x735.google.com with SMTP id af79cd13be357-78efd0fcec4so245986485a.1 for ; Sun, 21 Apr 2024 11:10:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713723007; x=1714327807; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=cDIK1C091x3n6Vo4M2ymTzId7hx8pR5bV6avKHtVcgM=; b=gWgdIB9EpLcdutgaWqMyOaap9pX+4ixE8e7V6phcYRhBGB7Gx6Kj4Jd2NFM/9W+E+1 0xu7T121OLx81pk7dCCKONmkCo554vTF1ieiQ2xomf8jXq3AvJPaKfssJQlgg3R42/ms wycn8IT1gw8VCilvkuT6mBh0n79o3xOfwj5Tz3ArzJkIw4dPyXU8bwj9JGW54NUBh6io KzUaFwgq8lCs1zBZA79bR9Y7Y4cRs5icg9KyTmm3+oW+a6lvBIgPPDPCRCVgah9nBiBJ ZLFxZYVA/vneNKuRBLbXPNPfkV+47Fb/e0eXWUojkx6TntXDsOz/KMa4k+VI4XZl1JQD qtzQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713723007; x=1714327807; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=cDIK1C091x3n6Vo4M2ymTzId7hx8pR5bV6avKHtVcgM=; b=vXAOjzUTSfmHSACO7dNvabJ0ZC60eeBHJlZAarb5E+oqxfPdqOt/2en2EDFu054qaR dmtpR6tX8FdOawBskLWe3kLN8kaY8YTtL0Epu6DqqXKr+HgA1rB1OodGxKMggpb70rxJ 12yvJxu+D0FiUY1YmHbdME6O7TIRTKQd31bj3R6H+dbCxF133wroayRcSt75qeS0RZB4 DwUdrfRgNaydgtJj3zSQZKGbLmQDAWXfyTYi+n/62nz2775he9ltu7AcbJEkJ7v+w8mp d3fdIDAQxFgrKnTohRgGFYRNNScIGBVHQ3jkilc/EO1vFO3oZwuOwg8cp7ru70U6PE+d R1nw== X-Gm-Message-State: AOJu0YzODZEBFMjahsKV3yLjl6gooW11iWA1vPm5y7PRpyf6eSy067bk cUhGPPs+HFaXkSa3yZI3Yotg+z0vnEoRyqYClNtk1GJPtLdPyQeB/cK1uJJrxXlm/VnfWem6eHV qXXbyS/dngdNm+nppdDBmzR7s8cs= X-Google-Smtp-Source: AGHT+IF3D/Lgq2axJZKt7O+Q0mqwnZM1thYjlwL2NZVM6Nf3q/nEdSVyB+yNZpgnk361xrtQQ3SDpAPOZRFAqCtBD0c= X-Received: by 2002:a05:6214:e6f:b0:6a0:7dd5:6835 with SMTP id jz15-20020a0562140e6f00b006a07dd56835mr1713946qvb.33.1713723007124; Sun, 21 Apr 2024 11:10:07 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: yudhi s Date: Sun, 21 Apr 2024 23:39:56 +0530 Message-ID: Subject: Re: error in trigger creation To: "David G. Johnston" Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000154f2906169f3bbd" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000154f2906169f3bbd Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, Apr 21, 2024 at 7:55=E2=80=AFPM David G. Johnston < david.g.johnston@gmail.com> wrote: > On Sunday, April 21, 2024, yudhi s wrote: > >> On Sun, Apr 21, 2024 at 1:55=E2=80=AFPM David G. Johnston < >> david.g.johnston@gmail.com> wrote: >> >>> On Sunday, April 21, 2024, yudhi s wrote: >>> >>>> so that it will be able to assign the privilege, so we will be able to >>>> create the event trigger without need to run the event trigger script = from >>>> super user itself? >>>> >>> >>> Write a security-definer function owned by superuser and grant app_user >>> permission to execute it. >>> >>> David J. >>> >>> >> >> Thank You David. >> >> Are you saying something like below, in which we first create the >> function from super user and then execute the grant? But doesn't that me= an, >> each time we want to create a new event trigger we have to be again >> dependent on the "super user" to modify the security definer function? >> > > Dynamic SQL. See =E2=80=9Cexecute=E2=80=9D in plpgsql. > > David J. > > Even if we create the event trigger using "security definer" function embedding the "create event trigger" with in its body using dynamic sql(something as below), and in future if we need to create another event trigger , we need to again update the function and re-compile and for that , we will need it it to be compiled using user "super user", is my understanding correct here? Or it will just need the "super user" to create the function for the first time , but after that the user who has the "execute grant" given (say app_user) will be able to perform updates and compile to the function body? CREATE OR REPLACE FUNCTION create_event_trigger_func() RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$ BEGIN EXECUTE 'CREATE EVENT TRIGGER event_trigger_name ON schema_name ...'; END; $$; GRANT EXECUTE ON FUNCTION create_event_trigger_func() TO app_user; --000000000000154f2906169f3bbd Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Sun, Apr 21, 2024 at 7:55=E2=80=AFPM D= avid G. Johnston <david.g.= johnston@gmail.com> wrote:
On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com<= /a>> wrote:
On Sunday, April 21= , 2024, yudhi s <learnerdatabase99@gmail.com> wrote:
so that it will be able t= o assign the privilege, so we will be able to create the event trigger with= out need to run the event trigger script from super user itself?

Write a security-definer function owned by supe= ruser and grant app_user permission to execute it.

David J.
=C2=A0

Thank You = David.

=C2=A0Are you saying something like below, in wh= ich we first create the function from super user and then execute the grant= ? But doesn't that mean, each time we want to create a new event trigge= r we have to be again dependent on the "super user" to modify the= security definer function?

Dyn= amic SQL.=C2=A0 See =E2=80=9Cexecute=E2=80=9D in plpgsql.

David J.
=C2=A0

Eve= n if we create the=C2=A0 event trigger using "security definer" f= unction embedding the "create event trigger" with in its body usi= ng dynamic sql(something as below), and in future if we need to create anot= her event trigger , we need to again update the function and re-compile and= for that , we will need it it to be compiled using user "super user&q= uot;, is my understanding correct=C2=A0here?=C2=A0
Or=C2=A0
=
it will just need the "super user" to create the function fo= r the first time , but after that the user who has the "execute grant&= quot; given (say app_user) will be able to perform updates=C2=A0and compile= to the function body?

CR= EATE OR REPLACE FUNCTION create_event_trigger_func()
=C2=A0 RETURNS void=
=C2=A0 LANGUAGE plpgsql
=C2=A0 SECURITY DEFINER
=C2=A0 AS $$
= =C2=A0 BEGIN
=C2=A0 =C2=A0 EXECUTE 'CREATE EVENT TRIGGER event_trigg= er_name ON schema_name ...';
=C2=A0 END;
$$;

=
GRANT EXECUTE ON FUNCTION create_event_trigge= r_func() TO app_user;
--000000000000154f2906169f3bbd--