Row Level Security (RLS)

Overview

CEDIS Pedidos implements PostgreSQL Row Level Security (RLS) to enforce data access control at the database level. All tables have RLS enabled with policies based on user roles and branch assignments.

Security Model

Roles

admin: Full access to all data across all branches
sucursal: Limited access to own branch’s data only

Key Principles

Branch Isolation: Sucursal users only see their own branch’s orders
Status-based Editing: Sucursal users can only edit draft orders
Admin Override: Admins bypass all restrictions
Authenticated Only: All operations require authentication

Policy Reference

sucursales

All authenticated users can read all branches (needed for dropdown lists).

Policies

sucursales_select

SELECT

Condition: auth.role() = 'authenticated'Allows: All authenticated users can read all branches

ALTER TABLE sucursales ENABLE ROW LEVEL SECURITY;

CREATE POLICY "sucursales_select" ON sucursales FOR SELECT 
  USING (auth.role() = 'authenticated');

users

Users can manage their own profile; admins can view all profiles.

Policies

users_select

SELECT

Condition: User is viewing their own profile OR user is admin

id = auth.uid() OR 
EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')

users_insert

INSERT

Condition: id = auth.uid()Allows: Users can insert their own profile (self-registration)

users_update

UPDATE

Condition: id = auth.uid()Allows: Users can update their own profile

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users_select" ON users FOR SELECT
  USING (id = auth.uid() OR 
         EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin'));

CREATE POLICY "users_insert" ON users FOR INSERT 
  WITH CHECK (id = auth.uid());

CREATE POLICY "users_update" ON users FOR UPDATE 
  USING (id = auth.uid());

materiales

All authenticated users can read the materials catalog.

Policies

materiales_select

SELECT

Condition: auth.role() = 'authenticated'Allows: All authenticated users can read all materials

ALTER TABLE materiales ENABLE ROW LEVEL SECURITY;

CREATE POLICY "materiales_select" ON materiales FOR SELECT 
  USING (auth.role() = 'authenticated');

pedidos Policies

Complex multi-policy setup for order management with role-based access.

SELECT

pedidos_select

SELECT

Condition: Admin sees all orders OR user sees only their branch’s orders

EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')
OR sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())

INSERT

pedidos_insert

INSERT

Condition: Order must belong to user’s branch

sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())

Effect: Sucursal users can only create orders for their own branch

UPDATE

pedidos_update_sucursal

UPDATE

USING Condition: Order is draft AND belongs to user’s branch

estado = 'borrador'
AND sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())

WITH CHECK: Order must still belong to user’s branch after update

sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())

Effect: Sucursal users can only edit draft orders from their branch

pedidos_update_admin

UPDATE

USING Condition: User is admin

EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')

Effect: Admins can update any order regardless of status or branch

DELETE

pedidos_delete_sucursal

DELETE

Condition: Order is draft AND belongs to user’s branch

estado = 'borrador'
AND sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())

Effect: Sucursal users can only delete their own draft orders

Complete SQL

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;

-- SELECT: sucursal sees own; admin sees all
CREATE POLICY "pedidos_select" ON pedidos FOR SELECT USING (
  EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')
  OR sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
);

-- INSERT: must be user's own branch
CREATE POLICY "pedidos_insert" ON pedidos FOR INSERT WITH CHECK (
  sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
);

-- UPDATE (sucursal): only drafts from own branch
CREATE POLICY "pedidos_update_sucursal" ON pedidos FOR UPDATE USING (
  estado = 'borrador'
  AND sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
) WITH CHECK (
  sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
);

-- UPDATE (admin): unrestricted
CREATE POLICY "pedidos_update_admin" ON pedidos FOR UPDATE USING (
  EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')
);

-- DELETE (sucursal): only drafts from own branch
CREATE POLICY "pedidos_delete_sucursal" ON pedidos FOR DELETE USING (
  estado = 'borrador'
  AND sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
);

pedido_detalle Policies

Line items inherit access rules from their parent order.

SELECT

detalle_select

SELECT

Condition: User can access the parent order

EXISTS (
  SELECT 1 FROM pedidos p
  WHERE p.id = pedido_id AND (
    EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')
    OR p.sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
  )
)

INSERT (Sucursal)

detalle_insert

INSERT

Condition: Parent order is draft AND belongs to user’s branch

EXISTS (
  SELECT 1 FROM pedidos p
  WHERE p.id = pedido_id
    AND p.estado = 'borrador'
    AND p.sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
)

detalle_insert_admin

INSERT

Condition: User is admin

EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')

UPDATE

detalle_update

UPDATE

Condition: Parent order is draft AND belongs to user’s branch

EXISTS (
  SELECT 1 FROM pedidos p
  WHERE p.id = pedido_id
    AND p.estado = 'borrador'
    AND p.sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
)

detalle_update_admin

UPDATE

Condition: User is admin

EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')

DELETE

detalle_delete

DELETE

Condition: Parent order is draft AND belongs to user’s branch

EXISTS (
  SELECT 1 FROM pedidos p
  WHERE p.id = pedido_id
    AND p.estado = 'borrador'
    AND p.sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
)

detalle_delete_admin

DELETE

Condition: User is admin

EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')

Complete SQL

ALTER TABLE pedido_detalle ENABLE ROW LEVEL SECURITY;

-- SELECT: follows parent pedido rules
CREATE POLICY "detalle_select" ON pedido_detalle FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.id = pedido_id AND (
      EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')
      OR p.sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
    )
  )
);

-- INSERT (sucursal): parent must be draft + own branch
CREATE POLICY "detalle_insert" ON pedido_detalle FOR INSERT WITH CHECK (
  EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.id = pedido_id
      AND p.estado = 'borrador'
      AND p.sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
  )
);

-- INSERT (admin): unrestricted
CREATE POLICY "detalle_insert_admin" ON pedido_detalle FOR INSERT WITH CHECK (
  EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')
);

-- UPDATE (sucursal): parent must be draft + own branch
CREATE POLICY "detalle_update" ON pedido_detalle FOR UPDATE USING (
  EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.id = pedido_id
      AND p.estado = 'borrador'
      AND p.sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
  )
);

-- UPDATE (admin): unrestricted
CREATE POLICY "detalle_update_admin" ON pedido_detalle FOR UPDATE USING (
  EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')
);

-- DELETE (sucursal): parent must be draft + own branch
CREATE POLICY "detalle_delete" ON pedido_detalle FOR DELETE USING (
  EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.id = pedido_id
      AND p.estado = 'borrador'
      AND p.sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())
  )
);

-- DELETE (admin): unrestricted
CREATE POLICY "detalle_delete_admin" ON pedido_detalle FOR DELETE USING (
  EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')
);

solicitudes_acceso Policies

Access request table for user registration workflow.

SELECT

sol_admin_sel

SELECT

Condition: User is admin

EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')

Effect: Admins see all access requests

sol_own_sel_2

SELECT

Condition: user_id = auth.uid()Effect: Users can see their own access requests

INSERT

sol_insert

INSERT

Condition: auth.role() = 'authenticated'Effect: Any authenticated user can create access requests (self-registration)

UPDATE

sol_admin_upd

UPDATE

Condition: User is admin

EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')

Effect: Admins can approve/reject requests

Complete SQL

ALTER TABLE solicitudes_acceso ENABLE ROW LEVEL SECURITY;

-- Admins see all solicitudes
CREATE POLICY "sol_admin_sel" ON solicitudes_acceso FOR SELECT
  USING (EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin'));

-- Admins can approve/reject
CREATE POLICY "sol_admin_upd" ON solicitudes_acceso FOR UPDATE
  USING (EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin'));

-- Any authenticated user can insert (self-registration)
CREATE POLICY "sol_insert" ON solicitudes_acceso FOR INSERT
  WITH CHECK (auth.role() = 'authenticated');

-- Users can see their own solicitud
CREATE POLICY "sol_own_sel_2" ON solicitudes_acceso FOR SELECT
  USING (user_id = auth.uid());

Policy Testing

Testing as Sucursal User

-- Set session to simulate sucursal user
SET LOCAL request.jwt.claims TO '{"sub": "user-uuid-here"}';

-- Should only see own branch's orders
SELECT * FROM pedidos;

-- Should only update draft orders from own branch
UPDATE pedidos SET total_kilos = 100 WHERE id = 'draft-order-uuid';

-- Should fail for submitted orders
UPDATE pedidos SET total_kilos = 100 WHERE id = 'enviado-order-uuid';
-- Error: new row violates row-level security policy

Testing as Admin

-- Set session to simulate admin user
SET LOCAL request.jwt.claims TO '{"sub": "admin-uuid-here"}';

-- Should see all orders
SELECT * FROM pedidos;

-- Should update any order
UPDATE pedidos SET estado = 'aprobado' WHERE id = 'any-order-uuid';

Common Patterns

Pattern 1: Role-based Access

-- Check if user is admin
EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND rol = 'admin')

Pattern 2: Branch Isolation

-- Check if resource belongs to user's branch
sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())

Pattern 3: Status-based Editing

-- Only allow editing draft orders
estado = 'borrador' AND sucursal_id = (SELECT sucursal_id FROM users WHERE id = auth.uid())

Pattern 4: Parent-child Access

-- Child inherits parent's access rules
EXISTS (
  SELECT 1 FROM pedidos p
  WHERE p.id = pedido_id
    AND [parent access condition]
)

Security Best Practices

Always enable RLS on tables containing user data
Test policies with different user roles before deploying
Use SECURITY DEFINER functions carefully (they bypass RLS)
Audit policy changes regularly
Document policy logic for maintainability

Superadmin Bypass

Superadmins are identified by the es_superadmin flag in the users table:

UPDATE users 
SET es_superadmin = true, rol = 'admin', estado_cuenta = 'activo'
WHERE email IN ('[email protected]', '[email protected]');

This flag can be used for additional permission checks in application logic.

Architecture

Database

Deployment

​Overview

​Security Model

​Roles

​Key Principles

​Policy Reference

​sucursales

​Policies

​users

​Policies

​materiales

​Policies

​pedidos Policies

​SELECT

​INSERT

​UPDATE

​DELETE

​Complete SQL

​pedido_detalle Policies

​SELECT

​INSERT (Sucursal)

​UPDATE

​DELETE

​Complete SQL

​solicitudes_acceso Policies

​SELECT

​INSERT

​UPDATE

​Complete SQL

​Policy Testing

​Testing as Sucursal User

​Testing as Admin

​Common Patterns

​Pattern 1: Role-based Access

​Pattern 2: Branch Isolation

​Pattern 3: Status-based Editing

​Pattern 4: Parent-child Access

​Security Best Practices

​Superadmin Bypass

Build docs developers (and LLMs) love

Overview

Security Model

Roles

Key Principles

Policy Reference

sucursales

Policies

users

Policies

materiales

Policies

pedidos Policies

SELECT

INSERT

UPDATE

DELETE

Complete SQL

pedido_detalle Policies

SELECT

INSERT (Sucursal)

UPDATE

DELETE

Complete SQL

solicitudes_acceso Policies

SELECT

INSERT

UPDATE

Complete SQL

Policy Testing

Testing as Sucursal User

Testing as Admin

Common Patterns

Pattern 1: Role-based Access

Pattern 2: Branch Isolation

Pattern 3: Status-based Editing

Pattern 4: Parent-child Access

Security Best Practices

Superadmin Bypass