Re: PostgreSQL 17: RLS, SECURITY DEFINER and migration role safety questions

Hi Dominique,

Thank you very much for taking the time to share your experience. This is
exactly the kind of practical advice I was hoping to receive.

Your point about separating SECURITY DEFINER and SECURITY INVOKER functions
into different schemas makes a lot of sense, especially the interaction
between schema USAGE privileges and function EXECUTE permissions.

In INSPECT OS, we have already introduced dedicated, least-privilege owner
roles for different groups of SECURITY DEFINER functions. However, our
current migration still places these functions in the public schema.

Your advice has highlighted an important architectural improvement that is
worth evaluating before the system grows further.

I'm going to add a dedicated security review covering schema separation,
function ownership, GRANT/REVOKE permissions, search_path, and potential
migration risks.

One question, if you don't mind: would you generally recommend a dedicated
schema for all privileged SECURITY DEFINER functions, or separate schemas
for different security domains, such as authentication, sessions, and
document access?

Thanks again for sharing your experience. I really appreciate it.

Best regards,
Samir

вт, 13 окт. 2026 г., 12:34 Dominique Devienne <ddevienne(at)gmail(dot)com>:

> On Tue, Oct 13, 2026 at 10:15 AM Samir Gadzhiev <mr(dot)sperrow111(at)gmail(dot)com>
> wrote:
> > - Dedicated NOLOGIN roles owning SECURITY DEFINER functions
> > - Restricted EXECUTE privileges and explicit search_path
>
> Hi. I can't answer most of your questions.
> But I'll share this, after using SECURITY DEFINER for a while now.
>
> Something I wish I had known earlier is to use SCHEMAs for "security
> scoping".
> See, having EXECUTE on a func/proc is not enough, you also need USAGE
> on its SCHEMA.
> I have a mix of INVOKER and DEFINER func/proc, and w/o separation into
> distinct SCHEMAs,
> it's harder to control and QC their access (i.e. grants). In my kinda
> mature and largish system,
> it's not easy to do the split after the fact. So plan that early and
> from the get go, if you can.
>
> SCHEMAs are you friends IMHO. Use them to organize/group your objects
> based on security requirements.
>
> My $0.02. --DD
>