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 1rybmi-00H7Jw-0k for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 18:19:28 +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 1rybmg-00FVpe-91 for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 18:19:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rybmf-00FVpP-3z for pgsql-general@lists.postgresql.org; Sun, 21 Apr 2024 18:19:25 +0000 Received: from mail-oo1-xc34.google.com ([2607:f8b0:4864:20::c34]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rybmY-003x6t-4P for pgsql-general@lists.postgresql.org; Sun, 21 Apr 2024 18:19:23 +0000 Received: by mail-oo1-xc34.google.com with SMTP id 006d021491bc7-5aa2bd6f651so2422889eaf.0 for ; Sun, 21 Apr 2024 11:19:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713723557; x=1714328357; 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=EInwiP3hk3zfPO17CM8JPuccEao9BSg2fk15sT87W7s=; b=F+TI7RUWLpo/NaYzWgI5b4WNN9aXx6lMsV0KZAsAUDo9E6RF87BC7G2D/27GsdRDLl HjOou5zc4KYAWWDNdl5FYh/mG/YX7RsNT96H0dQGlJDq04b1YV22siri0JTKT01SR9oj EST9mII43Vh4z5C1z5DPB2u1EhncIhl8BDLXc83N+5MUpE3RyuhjvlEv585JYVmzejjW QCiuKlwm2J49MPZZbiL+U05dptOj1VOSUPFAAxXDGCbqXXcIzPpyk9gMrQXvacIZSBHD R+SHVKHugvPXhGLDCV/+n9OXto8HdnYS45pJ+8xw3GZH0/Oirc1BUB5klDEadMr2+gc8 2Mxg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713723557; x=1714328357; 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=EInwiP3hk3zfPO17CM8JPuccEao9BSg2fk15sT87W7s=; b=XKAxzgt5wTV4TiYCN5CEhankKbdzMzCQbe3GsgF26Df3vIiVsBS/jxJGg7od8oVfxT dTxq/VtqwxQKIw3/n0JofP5e3OuCsHyT4hSmhXyT9j3cqSLg89bfNibwZIRirG6CiwO8 ZWNNxeP8hIu0nJxakj9XmDPl+yJ5A+KCKqQSdBVYuBHhc0LFGNJbk3D4eSRf0G8wZ02y WVDbkdfb7vemQaUG7jqlUi3DaRr5IpPQzSM3DJ1ux2KQ8sPeKiAlepVgF9kfLcglqycV APQ8qqICh0DltuX1pnmTKoWmuFydHXhzBwThGBVlv16g8rzuAFW4CDoSVjOsPTyOxacz GIGw== X-Gm-Message-State: AOJu0YyCstAU/jnRQN3f3xcgfaJ/ot3+1AggdzL36iBa+YUH9p8DNjY5 4yfKNk8vLMKCuLZ8DiulM0xDAOiOB5KSYnKBVv/Y8rwAZgFiCTMY2PhcgpwHBA5ns75JmoRIIF3 xAje0gHzVrbt4Sg4u9pQi8FQuctQ= X-Google-Smtp-Source: AGHT+IGtRD7w0ZIZV6GhkgxuYkua2C7Qj1Al9shRbjVJsFsH3KZoFGoTDaORkyNDsTFzysuyMnpChSTuv/ipuKcvT/Q= X-Received: by 2002:a4a:301:0:b0:5aa:6a1f:f673 with SMTP id 1-20020a4a0301000000b005aa6a1ff673mr8279176ooi.1.1713723557240; Sun, 21 Apr 2024 11:19:17 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Sun, 21 Apr 2024 11:18:39 -0700 Message-ID: Subject: Re: error in trigger creation To: yudhi s Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000df666f06169f5bc0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000df666f06169f5bc0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, Apr 21, 2024 at 11:10=E2=80=AFAM yudhi s wrote: > > 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 t= o >>>>> 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_use= r >>>> 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 m= ean, >>> 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 tha= t > , 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 bod= y? > > 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; > If you don't allow the caller to pass in parameters then no, you likely gain nothing from using a security definer function. It is a tool and I don't have enough info or desire to write the internals of said function(s) for your need. As Tom says, it very well may be impossible to accomplish your goal even with a security definer function. But absent a predefined role there is no other mechanism for the owners of objects or superusers to delegate their non-grantable abilities to ordinary users. David J. --000000000000df666f06169f5bc0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sun, Apr 21, 2024 at 11:10=E2=80=AFAM yudhi s <learnerdatabase99@gmail.com&g= t; wrote:

On Sun,= Apr 21, 2024 at 7:55=E2=80=AFPM David G. Johnston <david.g.johnston@gmail.com&= gt; wrote:
On Su= nday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> wrote:
On Sun, Apr 21, 2024 at 1:55=E2=80=AFPM David G. Johnston <david.g.johnston@g= mail.com> wrote:
On Sunday, April 21, 2024, yudhi s <<= a href=3D"mailto:learnerdatabase99@gmail.com" target=3D"_blank">learnerdata= base99@gmail.com> 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 ev= ent trigger script from super user itself?

Write a security-definer function owned by superuser and grant app_us= er permission to execute it.

David J.
= =C2=A0

Thank You David.
=C2=A0Are you saying something like below, in which we first create = the function from super user and then execute the grant? But doesn't th= at mean, each time we want to create a new event trigger we have to be agai= n dependent on the "super user" to modify the security definer fu= nction?

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

David J.
=C2=A0

Even if we create the= =C2=A0 event trigger using "security definer" function embedding = the "create event trigger" with in its body using dynamic sql(som= ething 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 wil= l need it it to be compiled using user "super user", is my unders= tanding correct=C2=A0here?=C2=A0
Or=C2=A0
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 a= pp_user) will be able to perform updates=C2=A0and compile to the function b= ody?

CREATE OR REPLACE FU= NCTION create_event_trigger_func()
=C2=A0 RETURNS void
=C2=A0 LANGUAG= E plpgsql
=C2=A0 SECURITY DEFINER
=C2=A0 AS $$
=C2=A0 BEGIN
=C2= =A0 =C2=A0 EXECUTE 'CREATE EVENT TRIGGER event_trigger_name ON schema_n= ame ...';
=C2=A0 END;
$$;

GRANT EXECUTE ON FUNCTION create_event_trigger_func() TO app_use= r;

If you don't = allow the caller to pass in parameters then no, you likely gain nothing fro= m using a security definer function.=C2=A0 It is a tool and I don't hav= e enough info or desire to write the internals of said function(s) for your= need.=C2=A0 As Tom says, it very well may be impossible to accomplish your= goal even with a security definer function.=C2=A0 But absent a predefined = role there is no other mechanism for the owners of objects or superusers to= delegate their non-grantable abilities to ordinary users.

David J.



--000000000000df666f06169f5bc0--