PostgreSQL 17: RLS, SECURITY DEFINER and migration role safety questions
PostgreSQL 17: RLS, SECURITY DEFINER and migration role safety questions
От:
Samir Gadzhiev <mr.sperrow111@gmail.com>
Дата:
Hello PostgreSQL community,
I'm working on INSPECT OS, an inspection management application using PostgreSQL 17, Node.js and Next.js.
We're reviewing the database security design before merging several security-sensitive changes, and I'd appreciate technical guidance from experienced PostgreSQL users.
Our architecture includes:
- FORCE ROW LEVEL SECURITY for tenant isolation
- Separate application, authentication and migration roles
- Dedicated NOLOGIN roles owning SECURITY DEFINER functions
- Restricted EXECUTE privileges and explicit search_path
- A transactional migration that validates existing role attributes and memberships before changing function ownership
I have three questions:
1. Is a role-attribute and membership preflight performed by the migration runner within the migration transaction a sound approach, or should the checks also be implemented directly in SQL?
2. What additional checks are recommended to ensure runtime roles cannot acquire privileges of SECURITY DEFINER function owners, including through role membership?
3. What PostgreSQL-specific negative tests would you recommend for RLS isolation, effective function privileges and session-based access revocation?
The relevant work is in migration 008 and its accompanying security tests.
Project repository:
I can provide minimal, sanitized SQL examples if helpful.
Any technical guidance or pointers to relevant documentation would be greatly appreciated.
Thank you!
Re: PostgreSQL 17: RLS, SECURITY DEFINER and migration role safety questions
От:
Dominique Devienne <ddevienne@gmail.com>
Дата:
On Tue, Oct 13, 2026 at 10:15 AM Samir Gadzhiev 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
Re: PostgreSQL 17: RLS, SECURITY DEFINER and migration role safety questions
От:
Samir Gadzhiev <mr.sperrow111@gmail.com>
Дата:
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@gmail.com>:
On Tue, Oct 13, 2026 at 10:15 AM Samir Gadzhiev <mr.sperrow111@gmail.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
Re: PostgreSQL 17: RLS, SECURITY DEFINER and migration role safety questions
От:
Dominique Devienne <ddevienne@gmail.com>
Дата:
On Tue, Oct 13, 2026 at 11:45 AM Samir Gadzhiev wrote: > 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? I would err on the side of granularity. With too many SCHEMAs, it's easy to GRANT ... ON ALL ROUTINES IN SCHEMA + GRANT USAGE ON SCHEMA. While with too few, you're back to having to deal with per-routine GRANTs, i.e. many more I suspect. If you have fewer security domains than routines, that is, of course. You lose the benefit of SCHEMA-wide granting. For routines, SCHEMAs replace Oracle's packages. Note that one thing I really miss from Oracle is the ability to grant something to a particular routine (and not a role). But TBH, I hope someone more experienced than me would chime as well :) My uses of PostgreSQL is definitely not mainstream I fear. --DD