MIME-Version: 1.0
From: Ayush Vatsa <ayushvatsa1810@gmail.com>
Date: Wed, 5 Feb 2025 00:19:56 +0530
Message-ID: <CACX+KaPVTfcmt__Ogwsf7xomtcCABktiA8iSJ+iCxOdbzKNnUA@mail.gmail.com>
Subject: Understanding ALTER DEFAULT PRIVILEGES Behavior in PostgreSQL
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000004d0d63062d557ad7"
Archived-At: <https://www.postgresql.org/message-id/CACX%2BKaPVTfcmt__Ogwsf7xomtcCABktiA8iSJ%2BiCxOdbzKNnUA%40mail.gmail.com>
Precedence: bulk

--0000000000004d0d63062d557ad7
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello PostgreSQL Community,

I was experimenting with default privileges in PostgreSQL and came across a
behavior I didn=E2=80=99t
fully understand. I would appreciate any insights on this.

I wanted to ensure that, by default, no roles had EXECUTE privileges on
functions created in my
schema. To achieve this, I ran the following:

postgres=3D# CREATE SCHEMA my_schema;
CREATE SCHEMA

postgres=3D# CREATE ROLE alex LOGIN;
CREATE ROLE

postgres=3D# ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema REVOKE EXECUTE ON
FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES

postgres=3D# CREATE OR REPLACE FUNCTION my_schema.hello_world()
RETURNS TEXT AS $$
BEGIN
    RETURN 'Hello, World!';
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION

postgres=3D# GRANT USAGE ON SCHEMA my_schema TO alex;
GRANT

postgres=3D# SET ROLE alex;
SET

postgres=3D> SELECT my_schema.hello_world();
  hello_world
---------------
 Hello, World!
(1 row)

To my surprise, alex was still able to execute the function hello_world,
even though I had
altered the default privileges before creating it. I was expecting the
function to be inaccessible
unless explicitly granted permissions.

Could someone help me understand why this happens? Also, what would be the
best way to
ensure that, by default, no roles (except the function owner) have any
privileges on new
functions created in my protected schema?
I know about REVOKE ALL ON ALL FUNCTIONS IN SCHEMA my_schema FROM public bu=
t
this won't work for the functions created after this revoke statement.

Thanks
Ayush Vatsa

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

<div dir=3D"ltr"><p>Hello PostgreSQL Community,</p><p>I was experimenting w=
ith default privileges in PostgreSQL and came across a behavior I didn=E2=
=80=99t<br>fully understand. I would appreciate any insights on this.</p><p=
>I wanted to ensure that, by default, no roles had <code>EXECUTE</code> pri=
vileges on functions created in my<br>schema. To achieve this, I ran the fo=
llowing:</p><p>postgres=3D# CREATE SCHEMA my_schema;<br>CREATE SCHEMA<br><b=
r>postgres=3D# CREATE ROLE alex LOGIN;<br>CREATE ROLE<br><br>postgres=3D# A=
LTER DEFAULT PRIVILEGES IN SCHEMA my_schema REVOKE EXECUTE ON FUNCTIONS FRO=
M PUBLIC;<br>ALTER DEFAULT PRIVILEGES<br><br>postgres=3D# CREATE OR REPLACE=
 FUNCTION my_schema.hello_world()<br>RETURNS TEXT AS $$<br>BEGIN<br>=C2=A0 =
=C2=A0 RETURN &#39;Hello, World!&#39;;<br>END;<br>$$ LANGUAGE plpgsql;<br>C=
REATE FUNCTION<br><br>postgres=3D# GRANT USAGE ON SCHEMA my_schema TO alex;=
<br>GRANT<br><br>postgres=3D# SET ROLE alex;<br>SET<br><br>postgres=3D&gt; =
SELECT my_schema.hello_world();<br>=C2=A0 hello_world =C2=A0<br>-----------=
----<br>=C2=A0Hello, World!<br>(1 row)<br><br>To my surprise, <code>alex</c=
ode> was still able to execute the function hello_world, even though I had<=
br>altered the default privileges before creating it. I was expecting the f=
unction to be inaccessible<br>unless explicitly granted permissions.</p><p>=
Could someone help me understand why this happens? Also, what would be the =
best way to<br>ensure that, by default, no roles (except the function owner=
) have any privileges on new<br>functions created in my protected schema?=
=C2=A0<br>I know about REVOKE ALL ON ALL FUNCTIONS IN SCHEMA my_schema FROM=
 public but<br>this won&#39;t work for the functions created after this rev=
oke statement.</p><p>Thanks<br>Ayush Vatsa</p></div>

--0000000000004d0d63062d557ad7--