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: https://github.com/sperrow1/inspect-os I can provide minimal, sanitized SQL examples if helpful. Any technical guidance or pointers to relevant documentation would be greatly appreciated. Thank you!
