Row Level Security - Procurement Calendar

Overview

The Procurement Calendar uses Supabase Row Level Security (RLS) to enforce fine-grained access control at the database level. All tables have RLS enabled, and policies are defined based on user roles.

RLS policies are enforced at the PostgreSQL level, ensuring security even if application code is compromised.

Security Architecture

The security model is built on three pillars:

User Roles

Three distinct roles: admin, coordinadora, consulta

RLS Policies

Table-level policies control CRUD operations

Helper Functions

get_my_role() function for policy evaluation

User Roles

Roles are defined as a PostgreSQL enum and stored in the profiles table.

CREATE TYPE user_role AS ENUM ('admin', 'coordinadora', 'consulta');

Role Permissions Matrix

Resource	admin	coordinadora	consulta
Catalogs	Full CRUD	Read only	Read only
Requisitions	Full CRUD	Create, Read, Update	Read only
History	Read, Write	Read, Write	No access
Profiles	Full CRUD	Own profile only	Own profile only

The consulta role is designed for stakeholders who need visibility without the ability to modify data.

RLS Helper Function

The get_my_role() function retrieves the current user’s role for use in policy definitions.

CREATE OR REPLACE FUNCTION get_my_role()
RETURNS user_role AS $$
  SELECT rol FROM public.profiles WHERE id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER STABLE;

Function Details

Returns: The role of the currently authenticated user
Security: SECURITY DEFINER allows reading from profiles table
Performance: STABLE marking allows query optimization
Usage: Called in RLS policy USING and WITH CHECK clauses

Table-Level Policies

All tables have RLS enabled:

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE proveedores ENABLE ROW LEVEL SECURITY;
ALTER TABLE productos ENABLE ROW LEVEL SECURITY;
ALTER TABLE presentaciones ENABLE ROW LEVEL SECURITY;
ALTER TABLE destinos ENABLE ROW LEVEL SECURITY;
ALTER TABLE estatus ENABLE ROW LEVEL SECURITY;
ALTER TABLE unidades ENABLE ROW LEVEL SECURITY;
ALTER TABLE requisiciones ENABLE ROW LEVEL SECURITY;
ALTER TABLE requisiciones_historial ENABLE ROW LEVEL SECURITY;

Profiles Policies

Controls access to user profile data.

View Own Profile

CREATE POLICY "profiles: anyone can view own" ON profiles
  FOR SELECT USING (id = auth.uid());

Allows users to view their own profile information.

Admin View All

CREATE POLICY "profiles: admin can view all" ON profiles
  FOR SELECT USING (get_my_role() = 'admin');

Admins can view all user profiles for management purposes.

Update Own Profile

CREATE POLICY "profiles: user can update own" ON profiles
  FOR UPDATE USING (id = auth.uid());

Users can update their own profile (e.g., name, preferences).

Admin Full Access

CREATE POLICY "profiles: admin can manage all" ON profiles
  FOR ALL USING (get_my_role() = 'admin');

Admins can modify user roles. Role changes take effect on the user’s next request.

Catalog Tables Policies

Applied uniformly to: proveedores, productos, presentaciones, destinos, estatus, unidades

All Authenticated Can Read

CREATE POLICY "<table>: all authenticated can select" ON <table>
  FOR SELECT TO authenticated USING (TRUE);

Any authenticated user can read catalog data for dropdowns and filters.

Admin Full Access

CREATE POLICY "<table>: admin full access" ON <table>
  FOR ALL TO authenticated 
  USING (get_my_role() = 'admin')
  WITH CHECK (get_my_role() = 'admin');

Only admins can create, update, or delete catalog entries.

Dynamic Policy Creation

These policies are created programmatically using a PL/pgSQL loop:

DO $$
DECLARE
  tbl TEXT;
BEGIN
  FOREACH tbl IN ARRAY ARRAY['proveedores', 'productos', 'presentaciones', 'destinos', 'estatus', 'unidades'] LOOP
    EXECUTE format('
      CREATE POLICY "%s: all authenticated can select" ON %s
        FOR SELECT TO authenticated USING (TRUE);', tbl, tbl);
    EXECUTE format('
      CREATE POLICY "%s: admin full access" ON %s
        FOR ALL TO authenticated USING (get_my_role() = ''admin'')
        WITH CHECK (get_my_role() = ''admin'');', tbl, tbl);
  END LOOP;
END;
$$;

Requisiciones Policies

The main table with differentiated access by role.

SELECT: All Authenticated

CREATE POLICY "requisiciones: all authenticated can select" ON requisiciones
  FOR SELECT TO authenticated USING (TRUE);

All roles can view requisitions. This enables:

consulta: Read-only dashboard access
coordinadora: View requisitions before editing
admin: Full visibility

INSERT: Admin and Coordinadora

CREATE POLICY "requisiciones: admin and coordinadora can insert" ON requisiciones
  FOR INSERT TO authenticated
  WITH CHECK (get_my_role() IN ('admin', 'coordinadora'));

The WITH CHECK clause validates the role before allowing the insert to proceed.

UPDATE: Admin and Coordinadora

CREATE POLICY "requisiciones: admin and coordinadora can update" ON requisiciones
  FOR UPDATE TO authenticated
  USING (get_my_role() IN ('admin', 'coordinadora'))
  WITH CHECK (get_my_role() IN ('admin', 'coordinadora'));

USING vs WITH CHECK

USING: Determines which rows can be selected for update
WITH CHECK: Validates the new row data after update

Both must pass for the update to succeed.

DELETE: Admin Only

CREATE POLICY "requisiciones: admin can delete" ON requisiciones
  FOR DELETE TO authenticated
  USING (get_my_role() = 'admin');

Deleting a requisition also deletes all its history records due to ON DELETE CASCADE.

Requisiciones Historial Policies

Audit log access is restricted to prevent tampering.

SELECT: Admin and Coordinadora

CREATE POLICY "historial: admin and coordinadora can select" ON requisiciones_historial
  FOR SELECT TO authenticated
  USING (get_my_role() IN ('admin', 'coordinadora'));

History is visible to admin and coordinadora roles for audit purposes.

INSERT: System Only

CREATE POLICY "historial: system can insert" ON requisiciones_historial
  FOR INSERT TO authenticated
  WITH CHECK (get_my_role() IN ('admin', 'coordinadora'));

History records are created by application code when requisitions are modified. No manual editing is allowed.

Security Best Practices

Principle of Least Privilege

Users are granted only the minimum permissions needed for their role

Defense in Depth

Security enforced at database level, independent of application code

Audit Trail

All requisition changes are logged in the historial table

Immutable History

No UPDATE or DELETE policies on history table

Recommended Practices

Never bypass RLS: Always use Supabase client with user authentication
Test role permissions: Verify policies work as expected for each role
Monitor policy performance: RLS policies can impact query speed on large tables
Use service_role sparingly: The service role key bypasses RLS (use only for admin operations)
Regular audits: Review requisiciones_historial for suspicious activity

The Supabase service_role key bypasses ALL RLS policies. Never expose it to client-side code.

Testing RLS Policies

You can test policies in the Supabase SQL Editor:

-- Test as a specific user
SET request.jwt.claim.sub = '<user_uuid>';

-- Query should respect RLS
SELECT * FROM requisiciones;

-- Reset
RESET request.jwt.claim.sub;

Testing Different Roles

-- Create test users with different roles
INSERT INTO auth.users (id, email, raw_user_meta_data)
VALUES 
  ('uuid-for-admin', '[email protected]', '{"rol": "admin"}'),
  ('uuid-for-coord', '[email protected]', '{"rol": "coordinadora"}'),
  ('uuid-for-view', '[email protected]', '{"rol": "consulta"}');

-- Profiles will be auto-created by trigger

-- Test as coordinadora
SET request.jwt.claim.sub = 'uuid-for-coord';
INSERT INTO requisiciones (...) VALUES (...);
-- Should succeed

-- Test as consulta
SET request.jwt.claim.sub = 'uuid-for-view';
INSERT INTO requisiciones (...) VALUES (...);
-- Should fail with permission error

Troubleshooting

Common Issues

Policy not working

Verify RLS is enabled: ALTER TABLE <table> ENABLE ROW LEVEL SECURITY;
Check user has a profile with valid role
Ensure get_my_role() returns expected value
Review policy definitions for typos

Performance issues

Add indexes on columns used in policy conditions
Use STABLE or IMMUTABLE function markers when possible
Avoid complex subqueries in policy definitions
Consider materialized views for complex access patterns

User can't access their own data

Check if profile exists: SELECT * FROM profiles WHERE id = auth.uid()
Verify authentication token is valid
Ensure policy uses auth.uid() correctly
Check for policy conflicts (multiple policies with OR logic)

Next Steps

Database Schema

Review the complete database structure

Data Model

Understand entity relationships

Get Started

Core Features

User Guides

Database

Deployment

​Overview

​Security Architecture

User Roles

RLS Policies

Helper Functions

​User Roles

​Role Permissions Matrix

​RLS Helper Function

​Table-Level Policies

​Profiles Policies

​View Own Profile

​Admin View All

​Update Own Profile

​Admin Full Access

​Catalog Tables Policies

​All Authenticated Can Read

​Admin Full Access

​Requisiciones Policies

​SELECT: All Authenticated

​INSERT: Admin and Coordinadora

​UPDATE: Admin and Coordinadora

​DELETE: Admin Only

​Requisiciones Historial Policies

​SELECT: Admin and Coordinadora

​INSERT: System Only

​Security Best Practices

Principle of Least Privilege

Defense in Depth

Audit Trail

Immutable History

​Recommended Practices

​Testing RLS Policies

​Troubleshooting

​Common Issues

​Next Steps

Database Schema

Data Model

Build docs developers (and LLMs) love

Overview

Security Architecture

User Roles

Role Permissions Matrix

RLS Helper Function

Table-Level Policies

Profiles Policies

View Own Profile

Admin View All

Update Own Profile

Admin Full Access

Catalog Tables Policies

All Authenticated Can Read

Admin Full Access

Requisiciones Policies

SELECT: All Authenticated

INSERT: Admin and Coordinadora

UPDATE: Admin and Coordinadora

DELETE: Admin Only

Requisiciones Historial Policies

SELECT: Admin and Coordinadora

INSERT: System Only

Security Best Practices

Recommended Practices

Testing RLS Policies

Troubleshooting

Common Issues

Next Steps