Skip to main content

​
Security Overview

The PAE Inventory System implements Row Level Security (RLS) on all tables to enforce role-based access control at the database level.

​
Security Principles

  1. Defense in Depth: Security enforced at database level, not just application layer
  2. Least Privilege: Users only have access to operations required for their role
  3. Audit Trail: All modifications are logged automatically
  4. Role Hierarchy: Clear permission levels (Desarrollador > Director > Madre Procesadora > Supervisor)

​
Role Hierarchy

Desarrollador (id_rol = 4)

Full System Administrator
  • All permissions on all tables
  • Can modify Director accounts
  • Can assign Director role
  • Only assignable from database directly

Director (id_rol = 1)

School Administrator
  • Approve/reject delivery guides
  • Manage users (except other Directors and Desarrolladores)
  • Full CRUD on inventory
  • Delete permissions on products

Madre Procesadora (id_rol = 2)

Kitchen Manager
  • Create delivery guides and inputs
  • Record daily operations
  • Modify products and inventory
  • Cannot approve guides or manage users

Supervisor (id_rol = 3)

Read-Only Observer
  • View all data
  • No modification permissions
  • Supervision and reporting only

​
RLS Policies by Table

​
users - User Management

​
Users: Todos pueden ver
Operation: SELECTPolicy: USING (true)Description: All authenticated users can view user accounts
​
Users: Director puede insertar
Operation: INSERTPolicy: WITH CHECK (get_user_role() IN (1, 4))Description: Only Director and Desarrollador can create user accounts
​
Users: Director puede actualizar
Operation: UPDATEPolicy: USING (get_user_role() IN (1, 2, 3, 4))Description: All users can update (but protect_director_users() trigger enforces additional restrictions)Note: The trigger prevents:
  • Self-modification
  • Modifying Desarrollador accounts
  • Directors modifying other Directors
  • Non-Desarrolladores promoting to Director
User modifications are protected by BOTH RLS policies AND trigger functions. The trigger protect_director_users() enforces business rules beyond simple role checking.

​
product - Product Catalog

​
Products: Todos pueden ver
Operation: SELECTPolicy: USING (true)
​
Products: Director y Madre Procesadora pueden crear
Operation: INSERTPolicy: WITH CHECK (get_user_role() IN (1, 2, 4))
​
Products: Director y Madre Procesadora pueden actualizar
Operation: UPDATEPolicy: USING (get_user_role() IN (1, 2, 4))
​
Products: Solo Director puede borrar
Operation: DELETEPolicy: USING (get_user_role() IN (1, 4))Rationale: Product deletion is restricted to prevent accidental data loss

​
category - Product Categories

​
Categories: Todos pueden ver
Operation: SELECTPolicy: USING (true)
​
Categories: Admin puede modificar
Operation: ALL (INSERT, UPDATE, DELETE)Policy: USING (get_user_role() IN (1, 4))Rationale: Categories are structural data, only administrators should modify

​
guia_entrada - Delivery Guides

​
guia_entrada_select
Operation: SELECTPolicy: USING (true)Description: All users can view delivery guides
​
guia_entrada_insert
Operation: INSERTPolicy: WITH CHECK (get_user_role() IN (1, 2, 4))Description: Madre Procesadora and Director can create guides
​
guia_entrada_update
Operation: UPDATEPolicy: USING (get_user_role() IN (1, 4))Description: Only Director/Desarrollador can update (typically for approval/rejection)Note: Updates are usually done via aprobar_guia() or rechazar_guia() RPC functions
The maker-checker pattern is enforced here: Madre Procesadora creates, Director approves. This separation of duties ensures oversight.

​
input - Delivery Line Items

​
Input: Todos pueden ver
Operation: SELECTPolicy: USING (true)
​
Input: Admin y Cocinera pueden crear
Operation: INSERTPolicy: WITH CHECK (get_user_role() IN (1, 2, 4))Description: Created when delivery guides are submitted
No UPDATE policy for input table. Once created, input records should not be modified directly. Changes require deleting the entire guide and recreating it.

​
output - Stock Withdrawals

​
Output: Todos pueden ver
Operation: SELECTPolicy: USING (true)
​
Output: Admin y Cocinera pueden crear
Operation: INSERTPolicy: WITH CHECK (get_user_role() IN (1, 2, 4))Description: Created during daily operations or manual withdrawalsNote: The update_stock_on_output() trigger automatically deducts stock

​
registro_diario - Daily Meal Records

​
registro_diario_select
Operation: SELECTPolicy: USING (true)
​
registro_diario_insert
Operation: INSERTPolicy: WITH CHECK (get_user_role() IN (1, 2, 4))Description: Created via procesar_operacion_diaria() RPC function

​
receta_porcion - Portion Yields

​
Porciones: Todos pueden ver
Operation: SELECTPolicy: USING (true)
​
Porciones: Admin y Cocinera pueden modificar
Operation: ALL (INSERT, UPDATE, DELETE)Policy: USING (get_user_role() IN (1, 2, 4))Description: Configuration table for meal calculations

​
audit_log - Audit Trail

​
Audit: Todos pueden ver
Operation: SELECTPolicy: USING (true)Description: Read-only for all users. No INSERT/UPDATE/DELETE policies.Note: Audit records are only created via trigger functions
The audit_log table has no INSERT/UPDATE/DELETE policies for regular users. All entries are created automatically by trigger functions running with elevated privileges.

​
Legacy Tables (asistencia_diaria, menu_diario, menu_detalle)

​
Asistencia: Todos pueden ver
Operation: SELECT | Policy: USING (true)
​
Asistencia: Admin y Cocinera pueden crear
Operation: INSERT | Policy: WITH CHECK (get_user_role() IN (1, 2, 4))
​
Asistencia: Admin y Cocinera pueden actualizar
Operation: UPDATE | Policy: USING (get_user_role() IN (1, 2, 4))
​
Asistencia: Admin y Cocinera pueden eliminar
Operation: DELETE | Policy: USING (get_user_role() IN (1, 2, 4))

​
Security Functions

​
get_user_role()

Helper function used throughout RLS policies. 
CREATE OR REPLACE FUNCTION get_user_role()
RETURNS INTEGER AS $$
    SELECT id_rol FROM users WHERE id_user = auth.uid();
$$ LANGUAGE SQL SECURITY DEFINER;
Purpose:
  • Retrieves the current user’s role ID
  • Used in USING and WITH CHECK clauses
  • Runs with elevated privileges (SECURITY DEFINER)
Example Usage: 
CREATE POLICY "example_policy" ON some_table
FOR INSERT WITH CHECK (get_user_role() IN (1, 2));

​
Trigger-Based Security

Beyond RLS policies, several triggers enforce business rules:

​
User Protection Triggers

-- BEFORE UPDATE ON users
-- Prevents:
-- ✗ Self-modification from user management
-- ✗ Modifying Desarrollador accounts
-- ✗ Directors modifying other Directors
-- ✗ Non-Desarrolladores promoting users to Director
-- ✗ Anyone assigning Desarrollador role

​
Stock Validation Trigger

-- update_stock_on_output()
-- AFTER INSERT ON output
-- Enforces:
-- ✓ Stock cannot go negative
-- ✓ Withdrawals must have sufficient inventory
-- ✓ Atomic stock updates with row locking

​
Permission Matrix

Quick reference for role permissions:
TableDesarrolladorDirectorMadre ProcesadoraSupervisor
users
SELECT✓✓✓✓
INSERT✓✓✗✗
UPDATE✓✓*✗✗
DELETE✗✗✗✗
product
SELECT✓✓✓✓
INSERT✓✓✓✗
UPDATE✓✓✓✗
DELETE✓✓✗✗
category
SELECT✓✓✓✓
INSERT✓✓✗✗
UPDATE✓✓✗✗
DELETE✓✓✗✗
guia_entrada
SELECT✓✓✓✓
INSERT✓✓✓✗
UPDATE✓✓✗✗
DELETE✗✗✗✗
input
SELECT✓✓✓✓
INSERT✓✓✓✗
UPDATE✗✗✗✗
DELETE✗✗✗✗
output
SELECT✓✓✓✓
INSERT✓✓✓✗
UPDATE✗✗✗✗
DELETE✗✗✗✗
registro_diario
SELECT✓✓✓✓
INSERT✓✓✓✗
UPDATE✗✗✗✗
DELETE✗✗✗✗
audit_log
SELECT✓✓✓✓
INSERTAutoAutoAutoAuto
UPDATE✗✗✗✗
DELETE✗✗✗✗
Legend:
  • ✓ = Allowed
  • ✗ = Denied
  • * = Allowed but restricted by triggers
  • Auto = Automatically created by triggers

​
RPC Function Security

RPC functions have internal permission checks:

​
aprobar_guia() / rechazar_guia()

SELECT id_rol INTO v_user_role FROM users WHERE id_user = auth.uid();
IF v_user_role IS NULL OR v_user_role NOT IN (1, 4) THEN
  RAISE EXCEPTION 'No tiene permisos para aprobar guias.';
END IF;
Allowed Roles: Director (1), Desarrollador (4)

​
procesar_operacion_diaria()

SELECT id_rol INTO v_user_role FROM users WHERE id_user = auth.uid();
IF v_user_role IS NULL OR v_user_role NOT IN (1, 2, 4) THEN
  RAISE EXCEPTION 'No tiene permisos para registrar operaciones diarias.';
END IF;
Allowed Roles: Director (1), Madre Procesadora (2), Desarrollador (4)

​
get_lotes_por_vencer()

No explicit permission check - Available to all authenticated users for reporting

​
Security Best Practices

Always Use RLS

Never disable RLS on production tables. All client queries automatically respect RLS policies.

Principle of Least Privilege

Grant only the minimum permissions required for each role. Supervisors get read-only access.

Use SECURITY DEFINER Carefully

Functions marked SECURITY DEFINER bypass RLS. Only use for trusted business logic with internal validation.

Audit Everything

All state-changing operations create audit trail entries. Never disable audit triggers.

Validate in Functions

RPC functions should validate permissions internally, not rely solely on RLS.

​
Testing RLS Policies

To test policies as different users: 
-- Temporarily become a specific user
SET LOCAL ROLE authenticated;
SET LOCAL request.jwt.claim.sub = '<user-uuid>';

-- Run queries to test access
SELECT * FROM product;
INSERT INTO product (product_name, unit_measure) VALUES ('Test', 'kg');

-- Reset
RESET ROLE;
Always test RLS policies in a development environment before deploying to production.

​
Common Security Patterns

​
Read All, Write Restricted

Most tables follow this pattern: 
-- Everyone can read
CREATE POLICY "select_all" ON table_name FOR SELECT USING (true);

-- Only specific roles can write
CREATE POLICY "insert_restricted" ON table_name FOR INSERT
  WITH CHECK (get_user_role() IN (1, 2, 4));

​
Admin Only Modifications

Structural tables (categories, roles): 
CREATE POLICY "admin_only" ON table_name FOR ALL
  USING (get_user_role() IN (1, 4));

​
Append-Only Logs

Audit tables: 
-- Only SELECT policy, no INSERT/UPDATE/DELETE for users
CREATE POLICY "read_only" ON audit_log FOR SELECT USING (true);

-- Inserts only via triggers with SECURITY DEFINER

Build docs developers (and LLMs) love

Get started for freeTalk to us