Week 04 ‐ Devlog

Locking down ownership

FINALLY - time to fix those RLS policies so I can stop getting warnings and feeling paranoid about the data. Before pushing anything live to production, it is important to get these next issues fully resolved.

RLS on all five tables:

-- Enable RLS on all five tables
ALTER TABLE public.profiles   ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.spaces     ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.plants     ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.data_logs  ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tasks      ENABLE ROW LEVEL SECURITY;

-- PROFILES
CREATE POLICY "profiles_select_own" ON public.profiles
  FOR SELECT USING (auth.uid() = id);
CREATE POLICY "profiles_insert_own" ON public.profiles
  FOR INSERT WITH CHECK (auth.uid() = id);
CREATE POLICY "profiles_update_own" ON public.profiles
  FOR UPDATE USING (auth.uid() = id);

-- SPACES
CREATE POLICY "spaces_select_own" ON public.spaces
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "spaces_insert_own" ON public.spaces
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "spaces_update_own" ON public.spaces
  FOR UPDATE USING (auth.uid() = user_id);

-- PLANTS
CREATE POLICY "plants_select_own" ON public.plants
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "plants_insert_own" ON public.plants
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "plants_update_own" ON public.plants
  FOR UPDATE USING (auth.uid() = user_id);

-- DATA LOGS: SELECT and INSERT only, no UPDATE
CREATE POLICY "data_logs_select_own" ON public.data_logs
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "data_logs_insert_own" ON public.data_logs
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- TASKS: full CRUD
CREATE POLICY "tasks_select_own" ON public.tasks
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "tasks_insert_own" ON public.tasks
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "tasks_update_own" ON public.tasks
  FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "tasks_delete_own" ON public.tasks
  FOR DELETE USING (auth.uid() = user_id);

For verification:

SELECT tablename, policyname, cmd
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

15 rows returned, and now when you check the tables, they have globe icons still. Alternative way to check if it stuck (outputs will be in screenshots near the end):

SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

Which will return the row security as true, and a globe will indicate next to the tables in the actual Table Editor as a globe, which means it is public and exposed through the PostgREST API. Postman will communicate with this during testing later on. And with RLS enabled, that access can be controlled through that same API. The table is now reachable and the policies are deployed so that each user only sees their own rows.

Four verification queries for Issue #9 to check out the schema and ensure everything is correct and being built to spec. First, check the tables:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

Foreign keys:

SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS referenced_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public'
ORDER BY tc.table_name;

Triggers:

SELECT trigger_name, event_object_table
FROM information_schema.triggers
WHERE trigger_schema = 'public'
   OR trigger_schema = 'auth'
ORDER BY event_object_table, trigger_name;

Policies:

SELECT tablename, policyname, cmd
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Issues Worked On:

• https://github.com/givecoffee/firstfrostmvp/issues/8
• https://github.com/givecoffee/firstfrostmvp/issues/9

Branches:

• https://github.com/givecoffee/firstfrostmvp/tree/schema%2F8-rls-policies
• https://github.com/givecoffee/firstfrostmvp/tree/9-schema-verification

Pull Requests:

• https://github.com/givecoffee/firstfrostmvp/pull/25
• https://github.com/givecoffee/firstfrostmvp/pull/26

Notes:

• RLS enabled on all five tables with 15 policies.
• rowsecurity = true confirms the policies are enforced on every request.

Verification/Screenshots:

rls policy verify

row security true

schema check tables

schema check foreign keys

schema check triggers

schema check policy