API Key User Association

TL;DR

This guide shows you how to securely link API keys to specific users in your system. This improves accountability and allows for easier key management (like revoking access). We’ll cover database changes, code updates, and testing.

Solution Guide

1. Database Setup: Add a User ID Field
   • You’ll need to modify your API keys table. Add a column (e.g., user_id) that stores the ID of the user associated with each key. The data type should match your users table’s primary key (usually an integer).
   • Example SQL for PostgreSQL:

     ALTER TABLE api_keys ADD COLUMN user_id INTEGER;

   • Consider adding a foreign key constraint to ensure the user_id exists in your users table. This prevents orphaned keys.

     ALTER TABLE api_keys ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

2. Code Changes: API Key Creation
   • When a new API key is generated, you *must* associate it with the currently logged-in user.
   • Retrieve the current user’s ID after successful authentication.

     user_id = session['user_id'] # Example using a session variable

   • Include this user_id when inserting the new API key into the database. For example, in Python with SQLAlchemy:

     new_key = ApiKey(api_key=generated_key, user_id=user_id) 
     db.session.add(new_key) 
     db.session.commit()

3. Code Changes: API Key Usage Validation
   • Before allowing an API key to access resources, verify that the associated user has permission.
   • Retrieve the user_id from the database based on the provided API key.
   • Check if the retrieved user_id is allowed to perform the requested action. This might involve checking roles or permissions in your users table.

     key = db.session.query(ApiKey).filter_by(api_key=api_key_from_request).first() 
     if key:
       user_id = key.user_id
       # Check user's permissions here...

4. Code Changes: API Key Revocation
   • Implement a function to revoke an API key. This should involve either deleting the key from the database or marking it as inactive.

     def revoke_api_key(api_key):
       key = db.session.query(ApiKey).filter_by(api_key=api_key).first()
       if key:
         key.active = False # Or delete the record
         db.session.commit()

   • Ensure that revoking a key prevents further access.
5. Testing
   • Create API keys for multiple users and verify they can only access resources they are permitted to.
   • Test revocation: Create a key, use it successfully, revoke it, then confirm access is denied.
   • Test edge cases: Invalid API keys should be rejected. Keys associated with non-existent users should also be handled gracefully (especially if you don’t have a foreign key constraint).