MIME-Version: 1.0
From: Ayush Vatsa <ayushvatsa1810@gmail.com>
Date: Sun, 7 Apr 2024 21:40:05 +0530
Message-ID: <CACX+KaN-Lqv+k6d4b37oKFiqF6ibMkq1RVHbcTby5EjRtNQewg@mail.gmail.com>
Subject: Clarification on View Privileges and Operator Execution in PostgreSQL
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000bbbc2d061583ecd6"
Archived-At: <https://www.postgresql.org/message-id/CACX%2BKaN-Lqv%2Bk6d4b37oKFiqF6ibMkq1RVHbcTby5EjRtNQewg%40mail.gmail.com>
Precedence: bulk

--000000000000bbbc2d061583ecd6
Content-Type: text/plain; charset="UTF-8"

Hi PostgreSQL community,
I am recently studying about operators and views and I had doubts in two
small things
1. I know if a view (security definer) is accessing a table then it is
getting accessed by view owners privileges
but what about the view which contains inbuilt operators or inbuilt
functions with whose privileges those will be executed. Eg.
SET ROLE postgres;
CREATE TABLE x(id INT);
CREATE VIEW v AS SELECT * FROM x WHERE id > 100;
CREATE ROLE alex;
GRANT SELECT ON v TO alex;
SET ROLE alex;
SELECT * FROM v;

Now table x will be accessed (SELECT * FROM x) with "postgres" privileges
but who will execute the
underlying function inside the ( > ) operator ? Is it postgres or alex?

2. What if I used a user defined operator in the above example, then with
whose privileges that operator will be executed?

Thanks
Ayush Vatsa
SDE AWS

--000000000000bbbc2d061583ecd6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi PostgreSQL community,<div>I am recently studying about =
operators and views and I had doubts in two small things</div><div>1. I kno=
w if a view (security definer) is accessing a table=C2=A0then it is getting=
 accessed by view owners privileges=C2=A0</div><div>but what about the view=
 which contains inbuilt operators or inbuilt functions with whose privilege=
s those will be executed. Eg.</div><div>SET ROLE postgres;</div><div>CREATE=
 TABLE x(id INT);</div><div>CREATE VIEW v AS SELECT * FROM x WHERE id &gt; =
100;</div><div>CREATE ROLE alex;</div><div>GRANT SELECT ON v TO alex;</div>=
<div>SET ROLE alex;</div><div>SELECT * FROM v;</div><div><br></div><div>Now=
 table x will be accessed (SELECT * FROM x) with &quot;postgres&quot;=C2=A0=
privileges but who will execute the=C2=A0</div><div>underlying function ins=
ide the ( &gt; ) operator ? Is it postgres or alex?</div><div><br></div><di=
v>2. What if I used a user defined operator in the above example, then with=
 whose privileges that operator will be executed?</div><div><br></div><div>=
Thanks</div><div>Ayush Vatsa</div><div>SDE AWS</div></div>

--000000000000bbbc2d061583ecd6--