Row Level Security (RLS) - Portal Ciudadano Manta

Overview

Row Level Security (RLS) is a PostgreSQL feature that restricts which rows users can access in database tables. RLS is critical for data security in Portal Ciudadano Manta.

Without RLS enabled, any authenticated user could access ALL data in your database, including other users’ personal information and reports.

Why RLS is Important

The anon key used in client-side code allows any user to connect to your database. RLS policies ensure:

Citizens can only see their own reports
Admins can manage all content
Users only see news targeted to their location
Survey responses remain private

Enabling RLS

First, enable RLS on all tables:

ALTER TABLE public.usuarios ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.administradores ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.noticias ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.encuestas ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.respuestas_encuestas ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.reportes ENABLE ROW LEVEL SECURITY;

Once RLS is enabled, no user can access data until you create policies explicitly allowing access.

Reportes Policies

Overview

Reportes (citizen reports) have strict policies ensuring users can only manage their own reports while admins can manage all reports. Location: sql/reportes_rls.sql

Select Policies

Users See Own Reports

CREATE POLICY "Usuarios pueden ver sus propios reportes"
ON public.reportes
FOR SELECT
TO authenticated
USING (
  auth.uid() = usuario_id
);

Allows users to view only reports they created.

Admins See All Reports

CREATE POLICY "Administradores pueden ver todos los reportes"
ON public.reportes
FOR SELECT
TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM public.usuarios
    WHERE usuarios.id = auth.uid()
      AND usuarios.tipo = 'administrador'
  )
);

Allows administrators to view all reports for management.

Insert Policy

CREATE POLICY "Usuarios pueden crear reportes"
ON public.reportes
FOR INSERT
TO authenticated
WITH CHECK (
  auth.uid() = usuario_id
);

Users can create reports, but only assigned to themselves.

Update Policies

Users Update Own Pending Reports

CREATE POLICY "Usuarios pueden actualizar sus propios reportes"
ON public.reportes
FOR UPDATE
TO authenticated
USING (
  auth.uid() = usuario_id
  AND estado = 'pendiente'
)
WITH CHECK (
  auth.uid() = usuario_id
  AND estado = 'pendiente'
);

Users can only edit their reports while still pendiente (not yet reviewed).

Admins Update Any Report

CREATE POLICY "Administradores pueden actualizar reportes"
ON public.reportes
FOR UPDATE
TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM public.usuarios
    WHERE usuarios.id = auth.uid()
      AND usuarios.tipo = 'administrador'
  )
);

Administrators can update any report (status, priority, response).

Delete Policy

CREATE POLICY "Administradores pueden eliminar reportes"
ON public.reportes
FOR DELETE
TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM public.usuarios
    WHERE usuarios.id = auth.uid()
      AND usuarios.tipo = 'administrador'
  )
);

Only administrators can delete reports.

Noticias Policies

Overview

News articles support geotargeting (parroquia/barrio) and are managed exclusively by administrators. Location: sql/noticias_rls.sql

Select Policy

CREATE POLICY "noticias_select_authenticated"
ON public.noticias
FOR SELECT
TO authenticated
USING (
  parroquia_destino IS NULL OR
  EXISTS (
    SELECT 1 FROM public.usuarios
    WHERE usuarios.id = auth.uid()
    AND (
      usuarios.parroquia = noticias.parroquia_destino
      AND (noticias.barrio_destino IS NULL OR usuarios.barrio = noticias.barrio_destino)
    )
  )
);

Logic:

Global news (parroquia_destino IS NULL) visible to all
Parroquia-specific news visible to users in that parroquia
Barrio-specific news visible only to users in that barrio

Insert Policy

CREATE POLICY "noticias_insert_admin"
ON public.noticias
FOR INSERT
TO authenticated
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.administradores
    WHERE administradores.id = auth.uid()
    AND administradores.activo = true
  )
  AND administrador_id = auth.uid()
);

Only active administrators can create news.

Update & Delete Policies

CREATE POLICY "noticias_update_admin"
ON public.noticias
FOR UPDATE
TO authenticated
USING (
  administrador_id = auth.uid()
  AND EXISTS (
    SELECT 1 FROM public.administradores
    WHERE administradores.id = auth.uid()
    AND administradores.activo = true
  )
);

CREATE POLICY "noticias_delete_admin"
ON public.noticias
FOR DELETE
TO authenticated
USING (
  administrador_id = auth.uid()
  AND EXISTS (
    SELECT 1 FROM public.administradores
    WHERE administradores.id = auth.uid()
    AND administradores.activo = true
  )
);

Administrators can only modify/delete news they created.

Policy Operations

Policy Types

Operation	SQL Command	Purpose
SELECT	`FOR SELECT`	Control which rows users can read
INSERT	`FOR INSERT`	Control which rows users can create
UPDATE	`FOR UPDATE`	Control which rows users can modify
DELETE	`FOR DELETE`	Control which rows users can remove
ALL	`FOR ALL`	Apply same rule to all operations

Policy Clauses

USING: Determines which existing rows are visible/modifiable
WITH CHECK: Validates new/modified rows before saving

Helper Functions

auth.uid()

Returns the UUID of the currently authenticated user:

auth.uid() = usuario_id  -- Check if user owns the row

EXISTS Subqueries

Check if a related record exists:

EXISTS (
  SELECT 1 FROM public.administradores
  WHERE administradores.id = auth.uid()
)

Returns true if user is in administradores table.

Testing Policies

Test as User

-- Set session to simulate a specific user
SET request.jwt.claims = '{"sub": "user-uuid-here"}'::json;

-- Try to query
SELECT * FROM reportes;

-- Should only see that user's reports

Test as Admin

-- Set session as admin user
SET request.jwt.claims = '{"sub": "admin-uuid-here"}'::json;

-- Try to query
SELECT * FROM reportes;

-- Should see all reports

Verifying Policies

Run diagnostic query:

SELECT 
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd
FROM pg_policies
WHERE tablename IN ('reportes', 'noticias', 'encuestas')
ORDER BY tablename, policyname;

Location: sql/diagnostico_rls.sql:16 Expected output:

tablename	policyname	cmd
noticias	noticias_delete_admin	DELETE
noticias	noticias_insert_admin	INSERT
noticias	noticias_select_authenticated	SELECT
noticias	noticias_update_admin	UPDATE
reportes	Administradores pueden actualizar reportes	UPDATE
reportes	Administradores pueden eliminar reportes	DELETE
reportes	Administradores pueden ver todos los reportes	SELECT
reportes	Usuarios pueden crear reportes	INSERT
reportes	Usuarios pueden actualizar sus propios reportes	UPDATE
reportes	Usuarios pueden ver sus propios reportes	SELECT

Performance Considerations

Indexes for RLS

RLS policies can impact query performance. Ensure indexes exist:

-- Index on usuario_id for reportes queries
CREATE INDEX IF NOT EXISTS idx_reportes_usuario_id ON reportes(usuario_id);

-- Index on administrador_id for noticias queries
CREATE INDEX IF NOT EXISTS idx_noticias_administrador_id ON noticias(administrador_id);

-- Index on parroquia/barrio for geotargeting
CREATE INDEX IF NOT EXISTS idx_usuarios_parroquia_barrio ON usuarios(parroquia, barrio);

Query Plans

Check if RLS policies are efficient:

EXPLAIN ANALYZE
SELECT * FROM reportes
WHERE estado = 'pendiente';

Look for sequential scans that should be index scans.

Common Patterns

User Owns Row

auth.uid() = usuario_id

User is Admin

EXISTS (
  SELECT 1 FROM public.administradores
  WHERE id = auth.uid()
)

Admin Created Row

administrador_id = auth.uid()
AND EXISTS (
  SELECT 1 FROM public.administradores
  WHERE id = auth.uid() AND activo = true
)

Geotargeting Match

parroquia_destino IS NULL  -- Global
OR EXISTS (
  SELECT 1 FROM usuarios
  WHERE usuarios.id = auth.uid()
  AND usuarios.parroquia = table.parroquia_destino
)

Troubleshooting

No Data Returns

Problem: Query returns empty even though data exists Cause: RLS policy is blocking access Solution:

Verify user is authenticated: SELECT auth.uid();
Check policy logic matches user’s attributes
Check user record exists in usuarios or administradores

Permission Denied

Problem: Error “new row violates row-level security policy” Cause: WITH CHECK clause rejecting insert/update Solution:

Ensure user_id matches authenticated user
Verify user has required role/status
Check WITH CHECK logic in policy

Next Steps

Storage Policies

Secure file uploads and downloads

Database Types

View complete type definitions

Deployment Guides

Configuration

​Overview

​Why RLS is Important

​Enabling RLS

​Reportes Policies

​Overview

​Select Policies

​Users See Own Reports

​Admins See All Reports

​Insert Policy

​Update Policies

​Users Update Own Pending Reports

​Admins Update Any Report

​Delete Policy

​Noticias Policies

​Overview

​Select Policy

​Insert Policy

​Update & Delete Policies

​Policy Operations

​Policy Types

​Policy Clauses

​Helper Functions

​auth.uid()

​EXISTS Subqueries

​Testing Policies

​Test as User

​Test as Admin

​Verifying Policies

​Performance Considerations

​Indexes for RLS

​Query Plans

​Common Patterns

​User Owns Row

​User is Admin

​Admin Created Row

​Geotargeting Match

​Troubleshooting

​No Data Returns

​Permission Denied

​Next Steps

Storage Policies

Database Types

Build docs developers (and LLMs) love

Overview

Why RLS is Important

Enabling RLS

Reportes Policies

Overview

Select Policies

Users See Own Reports

Admins See All Reports

Insert Policy

Update Policies

Users Update Own Pending Reports

Admins Update Any Report

Delete Policy

Noticias Policies

Overview

Select Policy

Insert Policy

Update & Delete Policies

Policy Operations

Policy Types

Policy Clauses

Helper Functions

auth.uid()

EXISTS Subqueries

Testing Policies

Test as User

Test as Admin

Verifying Policies

Performance Considerations

Indexes for RLS

Query Plans

Common Patterns

User Owns Row

User is Admin

Admin Created Row

Geotargeting Match

Troubleshooting

No Data Returns

Permission Denied

Next Steps