Custom RLS Policies

KeyHippo extends Supabase’s Row Level Security (RLS) framework, allowing for sophisticated access control policies that incorporate both session-based and API key authentication. This guide explores advanced RLS policy creation using KeyHippo functions.

​

KeyHippo RLS Basics

At its core, KeyHippo introduces the auth.keyhippo_check() function, which can be used in RLS policies alongside Supabase’s auth.uid(). Here’s a basic example:

CREATE POLICY "owner_access"
ON "public"."resource_table"
USING (
  auth.uid() = resource_table.owner_id
  OR auth.keyhippo_check(resource_table.owner_id)
);

This policy grants access when the user is authenticated via a session token (auth.uid()) or a valid API key associated with the resource owner (auth.keyhippo_check()).

​

Advanced Policy Patterns

​

Role-Based Access Control (RBAC)

Combine KeyHippo with role checks for granular access control:

CREATE POLICY "admin_access"
ON "public"."sensitive_data"
USING (
  (auth.uid() IN (SELECT user_id FROM user_roles WHERE role = 'admin'))
  OR (auth.keyhippo_check(auth.uid()) AND
      auth.uid() IN (SELECT user_id FROM user_roles WHERE role = 'admin'))
);

This policy allows access only if the user (authenticated via session or API key) has an ‘admin’ role.

​

Time-Based Policies

Implement time-sensitive access control:

CREATE POLICY "business_hours_access"
ON "public"."business_data"
USING (
  (auth.uid() IS NOT NULL OR auth.keyhippo_check(auth.uid()))
  AND EXTRACT(HOUR FROM CURRENT_TIME) BETWEEN 9 AND 17
);

This policy restricts access to business hours, regardless of the authentication method.

​

Resource-Specific Permissions

Create policies that check for specific permissions on resources:

CREATE POLICY "project_access"
ON "public"."projects"
USING (
  EXISTS (
    SELECT 1
    FROM project_permissions
    WHERE project_id = projects.id
      AND user_id = auth.uid()
      AND permission = 'read'
  )
  OR (
    auth.keyhippo_check(auth.uid())
    AND EXISTS (
      SELECT 1
      FROM project_permissions
      WHERE project_id = projects.id
        AND user_id = auth.uid()
        AND permission = 'read'
    )
  )
);

This policy checks a project_permissions table to determine if the user has the required permission for a specific project.

​

KeyHippo-Specific Functions

KeyHippo provides additional functions for advanced policy creation:

​

keyhippo.key_uid()

Retrieves the user ID associated with the current API key:

CREATE POLICY "api_key_specific_access"
ON "public"."api_resources"
USING (
  auth.uid() IS NOT NULL
  OR (keyhippo.key_uid() IS NOT NULL AND api_resources.allowed_key_id = keyhippo.key_uid())
);

This policy allows access if the user is authenticated or if the API key used is specifically allowed for the resource.

​

keyhippo.get_api_key_metadata()

Fetches metadata about API keys, useful for policies based on key properties:

CREATE POLICY "premium_api_access"
ON "public"."premium_resources"
USING (
  auth.uid() IS NOT NULL
  OR (
    keyhippo.key_uid() IS NOT NULL
    AND EXISTS (
      SELECT 1
      FROM keyhippo.get_api_key_metadata(keyhippo.key_uid()) AS key_meta
      WHERE key_meta.permission = 'premium'
    )
  )
);

This policy grants access to premium resources only if the API key has ‘premium’ permission.

​

Best Practices

1. Principle of Least Privilege: Always grant the minimum necessary permissions in your policies.

2. Consistent Authentication Checks: Include both auth.uid() and KeyHippo checks in policies for comprehensive coverage.

3. Performance Considerations: Complex policies can impact query performance. Test and optimize as necessary.

4. Regular Audits: Periodically review and update your RLS policies to ensure they align with your security requirements.

5. Documentation: Maintain clear documentation of your RLS policies, including the reasoning behind each policy.