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 1rtWqz-00FP5I-MV for pgsql-general@arkaria.postgresql.org; Sun, 07 Apr 2024 18:02:54 +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 1rtWqy-00AAgv-N8 for pgsql-general@arkaria.postgresql.org; Sun, 07 Apr 2024 18:02:52 +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 1rtWqy-00AAgE-CX for pgsql-general@lists.postgresql.org; Sun, 07 Apr 2024 18:02:52 +0000 Received: from mail-ed1-x52d.google.com ([2a00:1450:4864:20::52d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rtWqu-001QXH-UI for pgsql-general@lists.postgresql.org; Sun, 07 Apr 2024 18:02:51 +0000 Received: by mail-ed1-x52d.google.com with SMTP id 4fb4d7f45d1cf-56c404da0ebso5673440a12.0 for ; Sun, 07 Apr 2024 11:02:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1712512968; x=1713117768; 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=JqlpKrvMrtvQZ7riNFSXTfDzRUvioYtrmHyp6d5CjpU=; b=JYGzAQjv+HcJAZoGDCR1nigUh3bD/rHIENivK5Kq2NJ8JPiG4Civ1E0bdPMVYgwsP5 QCWdbqilPsnjsveErP1KKJJCr8eRD64nGO+qBZcGF7i2wD9rOcaR/JmkSOhKIyRUTmpj WeO/nzfyYJwLNQfd/kXsc50V3T3qxmf5QRguJ1pDrWSXvwEZmxFVChmkbmM2Qie6PSOB Tz+oPqpWQxw7pLEJGikD6NhnzJ35/f2uJGOK4alNxdMI8RvxBSdRPsaBWhAFvYxfeBg9 223cOvR+oivGW2xeTZ/yrIOKf5WUIZxDdJVgJjzzVZ/FNvTKi4b8bhpZQwa6hUwLDHZu +F4Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712512968; x=1713117768; 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=JqlpKrvMrtvQZ7riNFSXTfDzRUvioYtrmHyp6d5CjpU=; b=uv4znJ7Ge17UkPDvEY3Cu06cJPc5MQMmhdxTxP/9Gi32K99YaDrCsiixhIP2vcifP5 dinynYSN7O9xgCzabMOw2WA+wUNfqNVvaRy9SDA4JnEJeu1nT/Ib5oOG1TrOZewzFEYn aR6OvtE/03DWWLOPnyRr5dxMb1eKueMur8afcCBoKwT5Q9A6aYjsgAGb2KUvaUvC5nNH RyC+Wcb46X92RQ+qJfnFC65bPxDaKDDDb2wSMCrMdliIswU3iHbcEIlQS5nPuLTkCx3n azY3M02ZVnvBb+cgoG0+EWXmEaWmY0iCXOukK47D5CK6B87xxdgU0Q//i6Fr3UNAd02R WZ7g== X-Gm-Message-State: AOJu0YyC4VOA5O1qRaQfrWGf9vpq+bJwRxKo+fVC5Ae6N8PlI0XvrjRR GOy6RJpBP/bfghd3gldwBfxKNZ7VRyIeodZ+bMs2DNtvsM9yO3X5ihNbQ7MEvBXwAqrdeQZKNdB tE9pOhu2cIYc48s5i5jNE6qp9WC8= X-Google-Smtp-Source: AGHT+IH6RVkMENrGnQVSCFAVpz8ZmOMTo6qBzWblx+3Mj6AzX2qdSWmsdxnLn7vYQrLIsuey79dftSg6DQMFQyoqgk4= X-Received: by 2002:a50:cd56:0:b0:56e:42e0:e53c with SMTP id d22-20020a50cd56000000b0056e42e0e53cmr3341498edj.34.1712512967902; Sun, 07 Apr 2024 11:02:47 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ayush Vatsa Date: Sun, 7 Apr 2024 23:32:36 +0530 Message-ID: Subject: Re: Clarification on View Privileges and Operator Execution in PostgreSQL To: "David G. Johnston" Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000200b9d0615857f32" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000200b9d0615857f32 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > If you want to confirm what the documentation says create a custom operator/function that alex is not permitted to execute and have them query a view defined by postgres that uses that function. Thanks for the suggestion, it helped and I found out alex could not execute the view as it didn't have privileges for the function associated with operator But a small doubt arises here I have to revoke the execution of the function using the command REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public from public; but when I tried REVOKE EXECUTE ON FUNCTION text_equals(text,text) FROM alex; or REVOKE ALL ON FUNCTION text_equals(text,text) FROM alex; It didn't work i.e alex can still execute text_equals function. Why is it so? Thanks Ayush Vatsa SDE AWS On Sun, 7 Apr 2024 at 22:31, David G. Johnston wrote: > On Sun, Apr 7, 2024 at 9:32=E2=80=AFAM Ayush Vatsa > wrote: > >> but who will execute the >> > underlying function inside the ( > ) operator ? Is it postgres or alex= ? >> >>> > I'm reasonably confident that all the built-in functions are security > invoker. Not that a pure function like greater-than really cares. > > David J. > > --000000000000200b9d0615857f32 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
>= ; If you want to=C2=A0confirm what the=C2=A0documentation=C2=A0says create= =C2=A0a custom operator/function that alex is not permitted to execute and = have them query a view defined by postgres that uses that function.<= br>
Thanks for the suggesti= on, it helped and I found out alex could not execute the view as it didn= 9;t have privileges for the function associated with operator
<= div>
But a small doubt arises here I have to revoke the execu= tion of the function using the command=C2=A0
REVOKE ALL ON ALL FU= NCTIONS IN SCHEMA public from public;
but when I tried=C2=A0<= /div>
REVOKE EXECUTE ON FUNCTION text_equals(text,text) FROM alex;
<= /div>
or
REVOKE ALL ON FUNCTION text_equals(text,text) FROM a= lex;
It didn't work i.e alex can still execute text_equals fu= nction. Why is it so?

Thanks
Ayush Vatsa=
SDE AWS


On Sun, 7 Apr 2024 at 22:31, David G= . Johnston <david.g.johnst= on@gmail.com> wrote:
On Sun, Apr 7, 2024 at 9:32=E2=80=AFAM Ayush V= atsa <ayus= hvatsa1810@gmail.com> wrote:
=C2=A0but who will execute the=C2=A0
=
> underlying function inside the ( > ) operator ? Is it postgres= or alex?
=
=C2=A0
I'= m reasonably confident that all the built-in functions are security invoker= .=C2=A0 Not that a pure function like greater-than really cares.
David J.

--000000000000200b9d0615857f32--