PostgREST

​

Overview

KeyHippo integrates with PostgREST, enabling API key authentication for RESTful API endpoints. This integration allows you to authenticate requests using API keys, while enforcing Row Level Security (RLS) policies in Postgres.

​

Configuration

When you install KeyHippo, it automatically configures PostgREST to use KeyHippo’s check_request function for API key validation. Specifically, the role pgrst.db_pre_request is set to invoke this function before handling requests:

ALTER ROLE authenticator SET pgrst.db_pre_request = 'keyhippo.check_request';

While this setup happens automatically, it can be useful to understand the underlying mechanism. Here’s an outline of the keyhippo.check_request function:

CREATE OR REPLACE FUNCTION keyhippo.check_request()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_temp
AS $$
DECLARE
    req_app_api_key text := current_setting('request.headers', TRUE)::json ->> 'x-kh-api-key';
    result_user_id uuid;
BEGIN
    IF CURRENT_ROLE <> 'anon' THEN
        RETURN;
    END IF;
    result_user_id := keyhippo.get_uid_for_key(req_app_api_key);
    IF result_user_id IS NULL THEN
        RAISE EXCEPTION 'No registered API key found in x-kh-api-key header.';
    END IF;
END;
$$;

This function checks for a valid API key in the x-kh-api-key header and sets the appropriate user context for further request handling.

​

Example: Protecting Private Functions in RLS Policies

When using PostgREST with KeyHippo, your RLS policies might require anon access to functions that are critical for business logic. Granting direct access to private functions can pose a security risk, but you can avoid this by using public wrapper functions that enforce access control before calling the private function.

Here’s how to handle this securely while keeping sensitive functions protected:

Suppose you have an RLS policy that checks whether a user or API key holder can access certain records using the private.can_access_record function. To avoid exposing this function directly, you can use a public wrapper to manage access control.

​

The Issue

If anon doesn’t have access to the private function, PostgREST will return an error when trying to evaluate the policy:

{"message":"Failed to retrieve record. Error: permission denied for function can_access_record","error":"Internal Server Error","statusCode":500}

Rather than granting anon or authenticator execute permissions on private.can_access_record:

GRANT EXECUTE ON FUNCTION private.can_access_record(uuid, uuid) TO anon;

You can define a public wrapper function to handle this more securely.

​

Step 1: Revoke Direct Access to the Private Function

Ensure that anon and authenticator do not have direct access to the private function:

REVOKE EXECUTE ON FUNCTION private.can_access_record(uuid, uuid) FROM anon;
REVOKE EXECUTE ON FUNCTION private.can_access_record(uuid, uuid) FROM authenticator;

​

Step 2: Create a Public Wrapper

Define a public function that resolves the user’s identity (either session-based or API key-based) and then calls the private function securely:

CREATE OR REPLACE FUNCTION public.can_access_record(record_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    resolved_user_id uuid;
BEGIN
    -- Resolve the user ID based on either session-based or API key authentication
    resolved_user_id := COALESCE(auth.uid(), keyhippo.key_uid());

    -- Call the private function to perform the actual access check
    RETURN private.can_access_record(record_id, resolved_user_id);
END;
$$;

This ensures that the private function is only called after proper authentication has been verified.

​

Step 3: Update the RLS Policy

Update the RLS policy to use the public wrapper function instead of directly calling the private function:

CREATE POLICY "can_access_record_policy"
ON public.records
FOR SELECT
USING (
  (SELECT public.can_access_record(records.record_id)) = true
);

This ensures that all access checks are routed through the public wrapper, which safely handles user authentication.

​

Benefits

• Security: The private function remains protected and inaccessible to anon users.
• Flexibility: The public wrapper provides a clean interface for RLS policies to check access without exposing sensitive logic.
• Consistency: The same logic applies to both session-based and API key-based authentication.

​

Performance Considerations

KeyHippo’s PostgREST integration ensures efficient API key validation:

• API key validation does not require additional database queries.
• The keyhippo.check_request function runs only for anonymous requests, minimizing overhead for authenticated users.

However, complex RLS policies can affect performance. It’s important to test and optimize your policies for production use, ensuring database indexes are applied to frequently queried columns.

​

Troubleshooting

Common issues and solutions:

For detailed troubleshooting, refer to the KeyHippo GitHub repository or consult the documentation.