Row-Level Security Policies

Row-Level Security (RLS) is the foundation of Artbase Studio's multi-tenant architecture. Every table has RLS enabled with policies that enforce organization-scoped access.

How RLS Works

-- Enable RLS on a table
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Create a policy
CREATE POLICY "policy_name"
  ON table_name
  FOR operation (SELECT, INSERT, UPDATE, DELETE, ALL)
  USING (condition_for_existing_rows)     -- For SELECT, UPDATE, DELETE
  WITH CHECK (condition_for_new_rows);    -- For INSERT, UPDATE

Policy Patterns

Organization Membership Check

Most policies check that the user belongs to the organization:

-- Helper function for checking org membership
CREATE OR REPLACE FUNCTION auth.user_org_ids()
RETURNS UUID[] AS $$
  SELECT ARRAY_AGG(org_id)
  FROM organization_members
  WHERE user_id = auth.uid()
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Using in policy
CREATE POLICY "Members access org data"
  ON products FOR ALL
  USING (org_id = ANY(auth.user_org_ids()));

Role-Based Access

Some operations require specific roles:

CREATE POLICY "Admins can delete"
  ON products FOR DELETE
  USING (
    org_id IN (
      SELECT org_id FROM organization_members
      WHERE user_id = auth.uid()
      AND role IN ('owner', 'admin')
    )
  );

Public Read Access

Storefront data is publicly readable:

CREATE POLICY "Public can view active products"
  ON products FOR SELECT
  USING (status = 'active');

Core Table Policies

profiles

-- Users can only see their own profile
CREATE POLICY "Users view own profile"
  ON profiles FOR SELECT
  USING (id = auth.uid());

-- Users can only update their own profile
CREATE POLICY "Users update own profile"
  ON profiles FOR UPDATE
  USING (id = auth.uid());

organizations

-- Members can view their organizations
CREATE POLICY "Members view organization"
  ON organizations FOR SELECT
  USING (id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Only owners can update organization
CREATE POLICY "Owners update organization"
  ON organizations FOR UPDATE
  USING (id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
    AND role = 'owner'
  ));

-- Public can view basic org info (for storefronts)
CREATE POLICY "Public view org storefronts"
  ON organizations FOR SELECT
  USING (true)
  WITH CHECK (false);  -- Read-only for public

organization_members

-- Members can view other members in their orgs
CREATE POLICY "Members view org members"
  ON organization_members FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Only owners/admins can add members
CREATE POLICY "Admins add members"
  ON organization_members FOR INSERT
  WITH CHECK (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('owner', 'admin')
  ));

-- Only owners can remove members
CREATE POLICY "Owners remove members"
  ON organization_members FOR DELETE
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
    AND role = 'owner'
  ));

plans

-- Anyone can view active plans
CREATE POLICY "Public view plans"
  ON plans FOR SELECT
  USING (is_active = true);

Product Table Policies

products

-- Members view org products
CREATE POLICY "Members view org products"
  ON products FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Public view active products (storefronts)
CREATE POLICY "Public view active products"
  ON products FOR SELECT
  USING (status = 'active');

-- Members create products
CREATE POLICY "Members create org products"
  ON products FOR INSERT
  WITH CHECK (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Members update products
CREATE POLICY "Members update org products"
  ON products FOR UPDATE
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Admins delete products
CREATE POLICY "Admins delete org products"
  ON products FOR DELETE
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('owner', 'admin')
  ));

product_variants

-- Access through product relationship
CREATE POLICY "Members view variants"
  ON product_variants FOR SELECT
  USING (product_id IN (
    SELECT id FROM products
    WHERE org_id IN (
      SELECT org_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  ));

-- Public view variants for active products
CREATE POLICY "Public view active variants"
  ON product_variants FOR SELECT
  USING (
    is_active = true
    AND product_id IN (
      SELECT id FROM products WHERE status = 'active'
    )
  );

inventory_items

-- Members manage inventory
CREATE POLICY "Members view inventory"
  ON inventory_items FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

CREATE POLICY "Members update inventory"
  ON inventory_items FOR UPDATE
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

Order Table Policies

orders

-- Members view org orders
CREATE POLICY "Members view org orders"
  ON orders FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- System creates orders (via service role)
-- No insert policy for regular users

-- Members update order status
CREATE POLICY "Members update orders"
  ON orders FOR UPDATE
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

order_items

-- Access through order relationship
CREATE POLICY "Members view order items"
  ON order_items FOR SELECT
  USING (order_id IN (
    SELECT id FROM orders
    WHERE org_id IN (
      SELECT org_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  ));

customers

-- Members view org customers
CREATE POLICY "Members view org customers"
  ON customers FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Members update customers
CREATE POLICY "Members update org customers"
  ON customers FOR UPDATE
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

Email Table Policies

email_subscribers

-- Members view org subscribers
CREATE POLICY "Members view subscribers"
  ON email_subscribers FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Public can subscribe (insert)
CREATE POLICY "Public can subscribe"
  ON email_subscribers FOR INSERT
  WITH CHECK (true);

email_campaigns

-- Members manage campaigns
CREATE POLICY "Members view campaigns"
  ON email_campaigns FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

CREATE POLICY "Members create campaigns"
  ON email_campaigns FOR INSERT
  WITH CHECK (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

Channel Table Policies

channel_accounts

-- Members view connected channels
CREATE POLICY "Members view channel accounts"
  ON channel_accounts FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Admins manage channels
CREATE POLICY "Admins manage channels"
  ON channel_accounts FOR ALL
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
    AND role IN ('owner', 'admin')
  ));

Service Role Bypass

Background services use the service role key to bypass RLS:

// services/etsy-sync/index.ts
const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!  // Bypasses all RLS
);

// Now can access all data without policy restrictions
const { data: accounts } = await supabase
  .from('channel_accounts')
  .select('*');

warning

The service role key should never be exposed to clients. Only use it in server-side code and background services.

Debugging Policies

Check Current User

-- Get authenticated user ID
SELECT auth.uid();

-- Get user's organizations
SELECT org_id, role
FROM organization_members
WHERE user_id = auth.uid();

Test Policy Access

-- Test as specific user
SET request.jwt.claim.sub = 'user-uuid';

-- Run query
SELECT * FROM products WHERE org_id = 'org-uuid';

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

View Active Policies

SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Best Practices

Always enable RLS on new tables
Use SECURITY DEFINER functions for complex permission checks
Test policies with different user roles
Avoid policy recursion - don't reference the same table in USING clause
Index columns used in policy conditions for performance
Use permissive policies (default) - they OR together
Document policy intent with clear names and comments

How RLS Works​

Policy Patterns​

Organization Membership Check​

Role-Based Access​

Public Read Access​

Core Table Policies​

profiles​

organizations​

organization_members​

plans​

Product Table Policies​

products​

product_variants​

inventory_items​

Order Table Policies​

orders​

order_items​

customers​

Email Table Policies​

email_subscribers​

email_campaigns​

Channel Table Policies​

channel_accounts​

Service Role Bypass​

Debugging Policies​

Check Current User​

Test Policy Access​

View Active Policies​

Best Practices​