public inbox for [email protected]
help / color / mirror / Atom feedFrom: Isaac Morland <[email protected]>
To: Tom Lane <[email protected]>
Cc: Jacek Trocinski <[email protected]>
Cc: PostgreSQL Developers <[email protected]>
Subject: Re: Why is EXECUTE granted to PUBLIC for all routines?
Date: Sat, 23 Apr 2022 22:43:33 -0400
Message-ID: <CAMsGm5eDJu5N7XhECP1x35_q-7UYgRuV+17s_SNt3MASZMWZEg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAPBN_=npERDpVuvJ63-7gn8PCGPCu_7q0DTwaURg8Of+7a7M+Q@mail.gmail.com>
<[email protected]>
On Fri, 22 Apr 2022 at 13:44, Tom Lane <[email protected]> wrote:
> There is zero security concern for non-SECURITY-DEFINER functions,
> since they do nothing callers couldn't do for themselves. For those,
> you typically do want to grant out permissions. As for SECURITY DEFINER
> functions, there is no reason to make one unless it is meant to be called
> by someone besides the owner. Perhaps PUBLIC isn't the scope you want to
> grant it to, but no-privileges wouldn't be a useful default there either.
>
No privileges would be a safe default, not entirely unlike the default "can
only connect from localhost" pg_hba.conf, …
> In any case, changing this decision now would cause lots of problems,
> such as breaking existing dump files. We're unlikely to revisit it.
>
… but, yeah, this would be rather hard to change without causing more
trouble.
> As noted in the docs, best practice is to adjust the permissions
> as you want them in the same transaction that creates the function.
>
I wrote a function which resets the permissions on all objects in the
specified schemas to default. Then for each project I have a
privileges-granting file which starts by resetting all permissions, then
grants exactly the permissions I want. Most of the resetting is done by
checking the existing privileges and revoking them; then it ASSERTs that
this leaves an empty ACL, and finally does an UPDATE on the relevant system
table to change the ACL from empty to NULL. For SECURITY DEFINER functions,
the reset function then revokes PUBLIC privileges, leaving it to the
specific project to grant the appropriate privileges.
BTW, the reg* types are amazing for writing this kind of stuff. Makes all
sorts of things so much easier.
view thread (4+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Why is EXECUTE granted to PUBLIC for all routines?
In-Reply-To: <CAMsGm5eDJu5N7XhECP1x35_q-7UYgRuV+17s_SNt3MASZMWZEg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox