public inbox for [email protected]
help / color / mirror / Atom feedUnderstanding ALTER DEFAULT PRIVILEGES Behavior in PostgreSQL
3+ messages / 3 participants
[nested] [flat]
* Understanding ALTER DEFAULT PRIVILEGES Behavior in PostgreSQL
@ 2025-02-04 18:49 Ayush Vatsa <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: Ayush Vatsa @ 2025-02-04 18:49 UTC (permalink / raw)
To: [email protected]
Hello PostgreSQL Community,
I was experimenting with default privileges in PostgreSQL and came across a
behavior I didn’t
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=# CREATE SCHEMA my_schema;
CREATE SCHEMA
postgres=# CREATE ROLE alex LOGIN;
CREATE ROLE
postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema REVOKE EXECUTE ON
FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES
postgres=# CREATE OR REPLACE FUNCTION my_schema.hello_world()
RETURNS TEXT AS $$
BEGIN
RETURN 'Hello, World!';
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# GRANT USAGE ON SCHEMA my_schema TO alex;
GRANT
postgres=# SET ROLE alex;
SET
postgres=> 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 but
this won't work for the functions created after this revoke statement.
Thanks
Ayush Vatsa
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Understanding ALTER DEFAULT PRIVILEGES Behavior in PostgreSQL
@ 2025-02-04 19:00 David G. Johnston <[email protected]>
parent: Ayush Vatsa <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2025-02-04 19:00 UTC (permalink / raw)
To: Ayush Vatsa <[email protected]>; +Cc: [email protected] <[email protected]>
On Tuesday, February 4, 2025, Ayush Vatsa <[email protected]> wrote:
>
> postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema REVOKE EXECUTE ON
> FUNCTIONS FROM PUBLIC;
> ALTER DEFAULT PRIVILEGES
>
As the documentation explains:
Default privileges that are specified per-schema are added to whatever the
global default privileges are for the particular object type. This means
you cannot revoke privileges per-schema if they are granted globally
(either by default, or according to a previous ALTER DEFAULT PRIVILEGES command
that did not specify a schema). Per-schema REVOKE is only useful to reverse
the effects of a previous per-schema GRANT.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Understanding ALTER DEFAULT PRIVILEGES Behavior in PostgreSQL
@ 2025-02-04 21:47 Greg Sabino Mullane <[email protected]>
parent: Ayush Vatsa <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Greg Sabino Mullane @ 2025-02-04 21:47 UTC (permalink / raw)
To: Ayush Vatsa <[email protected]>; +Cc: [email protected]
On Tue, Feb 4, 2025 at 1:50 PM Ayush Vatsa <[email protected]>
wrote:
> 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?
>
Create them in another schema altogether, then move it to my_schema once
the privs have been set.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-02-04 21:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-04 18:49 Understanding ALTER DEFAULT PRIVILEGES Behavior in PostgreSQL Ayush Vatsa <[email protected]>
2025-02-04 19:00 ` David G. Johnston <[email protected]>
2025-02-04 21:47 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox