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 1rybzn-00H8Us-Cp for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 18:32:59 +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 1rybzl-00Fe8B-MV for pgsql-general@arkaria.postgresql.org; Sun, 21 Apr 2024 18:32:57 +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 1rybzl-00Fe80-A6 for pgsql-general@lists.postgresql.org; Sun, 21 Apr 2024 18:32:57 +0000 Received: from mail-oo1-xc2a.google.com ([2607:f8b0:4864:20::c2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rybzg-002ClC-4Z for pgsql-general@lists.postgresql.org; Sun, 21 Apr 2024 18:32:56 +0000 Received: by mail-oo1-xc2a.google.com with SMTP id 006d021491bc7-5ad0467fa80so2066455eaf.1 for ; Sun, 21 Apr 2024 11:32:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1713724369; x=1714329169; 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=tmeaQk3+EUijj4+XPx/EtydatH9uHD10nx+vxNBPuRU=; b=criOrslz6p4cPSTNxiBxifjrHum/QV2x1u/rn+eAFAkXrQxlol+X9Yo1551cXpEplh QJb3dGpj/xGRxDOTc7FBbbTDZLAoV6BPlr5EbWscz/UTu/t6kXvVk2Ha4cPsvsvq14Km rH6vIQhv6+J7dCyiSj8oyWNDnSEJlj7PU/5I3ndxDZO74wyCFLJ5q9b+ifOroAyRORqc +dKczEtWAbN2JeErwg/+szOq6KHaX1iZ1df6fmwIQz4wx+hgwxTiCX+PXNHbBJVAqQkx I8lWTqWXwqlkK5mIoUbYNkXF8jeAzBGVAKWLJC/nni4t65O7arBFsicpiBDox+tAH93R Z6Qw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713724369; x=1714329169; 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=tmeaQk3+EUijj4+XPx/EtydatH9uHD10nx+vxNBPuRU=; b=eJ1IvS2bpWje1imboz2cqx7mGH/wj0UvA+NvnQvvYQ7tHnhEM2ZBNME7QC2rYCtbFA Pb5ZJk3Wa/iNJnRWPag6dCntXecGLkqa6II5egQlxq6fikz4pMEngIROXvtjVR76fwzW 4Yd72DCy15VKBx3MhE0NEsPjZQCOx8Sv5QI3lTBeASjaw8+L1LtcnH939Sm8UUsEWzji 7IJ+7yc2Hj+MEpNtwlH+HAPvsHlnPeBefNvwOoHERZp0lpWJx93kKXyXqE3JQEDbTatR PgCS6M/zvfJWo2XfVGj6PuPCdyhFcKyyYvavOZL8sLRzCS1Bwj0xf8maSSib6Fb8G5s2 8d1g== X-Forwarded-Encrypted: i=1; AJvYcCUalmYiA796CCDlvnpuohFGw047xG3c0tlOiXSnbQ+03PgXyX08sXbYbWa+WKYQLhyRkQoNyrMjqjr80Krvkt51kI5uSaHECAEyigQbBEiTVXTL X-Gm-Message-State: AOJu0YwIXO3WkvLDTVl1YR4Tl6yYUtxo/dT3hxgH8k9K27dtvLonQdZ9 2SZqgk4ddAKKR8fy5jmVa9AqNdVmLpOOl1Dt7Ge+Fd0vpbZTh/hS8vg7nW9J56GHM/MLyWOz3VU Lpua58hw7o3MHyV/tYPxUo14PQzc= X-Google-Smtp-Source: AGHT+IEcVGYnAE2fdRypcumxxDk46gGgK937LIYSSodJ95+8XclG44xJ5dejFTN/QDQgfhpnuqIUuYa64Y/18M3tJZ0= X-Received: by 2002:a4a:ad8e:0:b0:5ac:9ec3:78aa with SMTP id v14-20020a4aad8e000000b005ac9ec378aamr8534243oom.8.1713724369161; Sun, 21 Apr 2024 11:32:49 -0700 (PDT) MIME-Version: 1.0 References: <2720974.1713710606@sss.pgh.pa.us> In-Reply-To: From: "David G. Johnston" Date: Sun, 21 Apr 2024 11:32:11 -0700 Message-ID: Subject: Re: error in trigger creation To: yudhi s Cc: Tom Lane , pgsql-general Content-Type: multipart/alternative; boundary="00000000000044552a06169f8c18" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000044552a06169f8c18 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, Apr 21, 2024 at 11:20=E2=80=AFAM yudhi s wrote: > > On Sun, Apr 21, 2024 at 8:13=E2=80=AFPM Tom Lane wrot= e: > >> "David G. Johnston" writes: >> > On Sunday, April 21, 2024, yudhi s wrote= : >> >> 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 >> mean, >> >> 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. >> >> You might as well just give that user superuser and be done with it. >> It's foolish to imagine that you have any shred of security left >> if you're letting a user that's not 100.00% trusted write event >> triggers. (Much less execute any SQL command whatsoever, which >> is what it sounds like David is suggesting you create a function >> to do.) >> >> > So do you mean , we should not create the event trigger using the > "security definer" , rather have the super user do this each time we have > to create the event trigger? > I suggest you share a script that demonstrates exactly what you are trying to accomplish. Which event triggers you need to create from the application and what the functions those triggers call do. > Actually , I am not very much aware about the security part, but is it > fine to give the super user privilege to the application user(say app_use= r) > from which normally scripts/procedures get executed by the application, b= ut > nobody(individual person) can login using that user. > app_user should not be superuser nor own objects in the database. The role that performs schema migrations for the database should be able to become superuser via set role so when doing migrations if there is a need to do something as superuser it is possible but explicit. It is during schema migrations that event triggers are expected to be installed, not in response to some user hitting your website and having your middleware execute some SQL while connected as the app_user role. David J. --00000000000044552a06169f8c18 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sun, Apr 21, 2024 at 11:20=E2=80=AFAM yudhi s <learnerdatabase99@gmail.com&g= t; wrote:

On Sun,= Apr 21, 2024 at 8:13=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" &l= t;david.g.j= ohnston@gmail.com> writes:
> On Sunday, April 21, 2024, yudhi s <learnerdatabase99@gmail.com> wrote= :
>> Are you saying something like below, in which we first create the<= br> >> function from super user and then execute the grant? But doesn'= ;t that 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 def= iner function?

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

You might as well just give that user superuser and be done with it.
It's foolish to imagine that you have any shred of security left
if you're letting a user that's not 100.00% trusted write event
triggers.=C2=A0 (Much less execute any SQL command whatsoever, which
is what it sounds like David is suggesting you create a function
to do.)


So do you mean , we should = not create the event trigger using the "security definer" , rathe= r have the super user do this each time we have to create the event trigger= ?

I suggest you share a scr= ipt that demonstrates exactly what you are trying to accomplish.=C2=A0 Whic= h event triggers you need to create from the application and what the funct= ions those triggers call do.


=
Actually , I am not very much aware about the security part, but= is it fine to give the super user privilege to the application user(say ap= p_user) from which normally scripts/procedures get executed by the applicat= ion, but nobody(individual person) can login using that user.

app_user should not be superuser no= r own objects in the database.=C2=A0 The role that performs schema migratio= ns for the database should be able to become superuser via set role so when= doing migrations if there is a need to do something as superuser it is pos= sible but explicit.

It is during schema migrations tha= t event triggers are expected to be installed, not in response to some user= hitting your website and having your middleware execute some SQL while con= nected as the app_user role.

David J.

--00000000000044552a06169f8c18--