SQL-Based API Key Issuance

KeyHippo provides a mechanism for generating and managing API keys directly within your Supabase database using SQL functions.

​

Core Concepts

1. Database-Centric: API keys are issued and managed directly within Postgres, utilizing its built-in security and performance.
2. Cryptographic Security: Keys are generated using secure cryptographic methods to ensure uniqueness and resistance to tampering.
3. Stateless Validation: API keys can be validated without requiring frequent database queries, improving performance and scalability.

​

Key Generation

API keys are generated using the keyhippo.create_api_key function, which issues a new key for a specified user. The function takes the user’s unique identifier and a description of the key’s intended use as parameters.

SELECT keyhippo.create_api_key(id_of_user TEXT, key_description TEXT) RETURNS TEXT

​

Parameters:

• id_of_user: The unique identifier (UUID) of the user for whom the API key is being generated.
• key_description: A human-readable description of the API key’s purpose.

​

Returns:

• The API key as a TEXT value.

​

Example Usage

The following query generates a new API key for a user:

SELECT keyhippo.create_api_key(
  '123e4567-e89b-12d3-a456-426614174000',
  'Primary API Key for Project X'
) AS new_api_key;

​

Key Generation Process

When an API key is generated, several steps are performed:

1. Permission Verification: The function checks that the caller has the necessary permissions to create API keys.
2. Unique Key Creation: A UUID is generated as a unique identifier for the key.
3. JWT Creation: A JSON Web Token (JWT) is created containing key metadata.
4. Signature: The JWT is signed to prevent tampering.
5. Cryptographic Hashing: The final API key is created by hashing the signed JWT.
6. Secure Storage: Key metadata is securely stored in the database. The raw API key is never saved.

​

Additional Key Management Functions

Several additional SQL functions are provided to manage the lifecycle of API keys:

​

Revoking a Key

An API key can be revoked using the keyhippo.revoke_api_key function. Once revoked, the key can no longer be used.

SELECT keyhippo.revoke_api_key(id_of_user TEXT, secret_id TEXT) RETURNS VOID

​

Retrieving Key Information

Key metadata for a user’s API keys can be retrieved using the keyhippo.load_api_key_info function.

SELECT keyhippo.load_api_key_info(id_of_user TEXT) RETURNS JSONB

​

Fetching Key Metadata

Detailed metadata for all API keys associated with a user can be fetched using the keyhippo.get_api_key_metadata function.

SELECT keyhippo.get_api_key_metadata(id_of_user UUID) RETURNS TABLE (...)

​

Best Practices

1. Principle of Least Privilege: Generate API keys with only the permissions needed for their intended use.
2. Key Rotation: Implement policies to regularly rotate API keys and revoke those no longer in use.
3. Audit Logging: Maintain logs of all key generation and management activities for security auditing purposes.
4. Error Handling: Ensure error handling when working with key management functions to prevent unintended access issues.

​

Performance Considerations

1. Efficient Validation: Key validation can be performed without requiring frequent database lookups, enhancing system performance.
2. Scalability: Stateless validation allows the system to scale efficiently without bottlenecks.
3. Indexing: Proper indexing on key management tables is recommended to improve query performance.

​

Security Considerations

1. Access Control: Restrict access to key management functions to trusted users and roles.
2. Secure Transmission: Always use encrypted connections (e.g., HTTPS) when transmitting API keys.
3. Sensitive Data Handling: Treat API keys as sensitive data. Never log or display full key values in insecure environments.