Hostaway MCP Server

-- Migration: 20251013_002_rls_policies.sql -- Purpose: Enable RLS and create access policies for all 7 tables -- Author: Claude Code -- Date: 2025-10-13 -- ==================================================================== -- 1. organizations - RLS Policies -- ==================================================================== ALTER TABLE organizations ENABLE ROW LEVEL SECURITY; CREATE POLICY "Users access own organizations" ON organizations FOR ALL USING ( id IN ( SELECT organization_id FROM organization_members WHERE user_id = auth.uid() ) ); -- ==================================================================== -- 2. organization_members - RLS Policies -- ==================================================================== ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY; CREATE POLICY "Users see own memberships" ON organization_members FOR SELECT USING (user_id = auth.uid()); CREATE POLICY "Owners manage members" ON organization_members FOR ALL USING ( organization_id IN ( SELECT organization_id FROM organization_members WHERE user_id = auth.uid() AND role = 'owner' ) ); -- ==================================================================== -- 3. hostaway_credentials - RLS Policies -- ==================================================================== ALTER TABLE hostaway_credentials ENABLE ROW LEVEL SECURITY; CREATE POLICY "Users access own org credentials" ON hostaway_credentials FOR SELECT USING ( organization_id IN ( SELECT organization_id FROM organization_members WHERE user_id = auth.uid() ) ); CREATE POLICY "Owners manage credentials" ON hostaway_credentials FOR INSERT WITH CHECK ( organization_id IN ( SELECT organization_id FROM organization_members WHERE user_id = auth.uid() AND role IN ('owner', 'admin') ) ); CREATE POLICY "Owners update credentials" ON hostaway_credentials FOR UPDATE USING ( organization_id IN ( SELECT organization_id FROM organization_members WHERE user_id = auth.uid() AND role IN ('owner', 'admin') ) ); -- ==================================================================== -- 4. api_keys - RLS Policies -- ==================================================================== ALTER TABLE api_keys ENABLE ROW LEVEL SECURITY; CREATE POLICY "Users manage own org API keys" ON api_keys FOR ALL USING ( organization_id IN ( SELECT organization_id FROM organization_members WHERE user_id = auth.uid() ) ); -- ==================================================================== -- 5. subscriptions - RLS Policies -- ==================================================================== ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY; CREATE POLICY "Users view own org subscription" ON subscriptions FOR SELECT USING ( organization_id IN ( SELECT organization_id FROM organization_members WHERE user_id = auth.uid() ) ); CREATE POLICY "System updates subscriptions" ON subscriptions FOR UPDATE USING (true) WITH CHECK (true); CREATE POLICY "System inserts subscriptions" ON subscriptions FOR INSERT WITH CHECK (true); -- ==================================================================== -- 6. usage_metrics - RLS Policies -- ==================================================================== ALTER TABLE usage_metrics ENABLE ROW LEVEL SECURITY; CREATE POLICY "Users view own org usage" ON usage_metrics FOR SELECT USING ( organization_id IN ( SELECT organization_id FROM organization_members WHERE user_id = auth.uid() ) ); CREATE POLICY "System updates usage metrics" ON usage_metrics FOR INSERT WITH CHECK (true); CREATE POLICY "System modifies usage metrics" ON usage_metrics FOR UPDATE USING (true) WITH CHECK (true); -- ==================================================================== -- 7. audit_logs - RLS Policies -- ==================================================================== ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY; CREATE POLICY "Users view own org audit logs" ON audit_logs FOR SELECT USING ( organization_id IN ( SELECT organization_id FROM organization_members WHERE user_id = auth.uid() ) ); CREATE POLICY "System creates audit logs" ON audit_logs FOR INSERT WITH CHECK (true); -- ==================================================================== -- Migration Complete -- ==================================================================== -- All tables now have RLS enabled with organization-scoped access -- Service role bypass policies allow webhook/system operations -- User policies enforce multi-tenant isolation via organization_members join