Skip to content

PostgreSQL and Keycloak Integration Implementation

Date: 2025-04-24 Participants: Rakesh Gangwar, Cascade AI

Context

As part of the Meta Agent Platform development, we needed to implement PostgreSQL database integration with proper Keycloak authentication. The goal was to create a robust backend that stores user data in PostgreSQL while authenticating users through Keycloak.

Discussion

Database Integration

We implemented PostgreSQL integration using SQLAlchemy as the ORM and Alembic for database migrations. The key components included:

  1. Database Models: Created a User model that integrates with Keycloak authentication
  2. Database Session Management: Implemented proper session handling and dependency injection
  3. Database Migrations: Set up Alembic for schema migrations and created an initial migration for the users table
  4. Environment Configuration: Configured database connection settings through environment variables

Keycloak Integration

We initially attempted to use the FastAPIKeycloak library but encountered issues with admin features. The library was trying to use admin features that required specific permissions in Keycloak. After troubleshooting, we decided to implement a custom Keycloak integration using FastAPI's built-in security utilities:

  1. Custom Authentication: Implemented a direct OAuth2 integration with Keycloak
  2. Token Handling: Created functions for token validation, decoding, and role checking
  3. User Synchronization: Implemented a system to sync users between Keycloak and the database
  4. Authentication Flow: Set up login, callback, and logout routes for the authentication flow

API Endpoints

We implemented several API endpoints to test the integration:

  1. /api/v1/users/me - Get current user information (syncs with Keycloak)
  2. /api/v1/users/{user_id} - Get a specific user by ID (admin only)
  3. /api/v1/users - Get all users with pagination (admin only)
  4. /protected - A protected endpoint that requires authentication
  5. /token - Get a token using client credentials

Challenges

  1. FastAPIKeycloak Issues: We encountered a 403 Forbidden error with the message "Public client not allowed to retrieve service account" when using the FastAPIKeycloak library. This was despite having configured the Keycloak client as confidential with service accounts enabled.

  2. JWT Decoding: When implementing our custom solution, we faced an issue with the python-jose library requiring a key parameter even when not verifying the signature.

  3. Swagger UI Authentication: Although we could authenticate through Swagger UI, the authorization wasn't being properly applied to API requests.

Decision

  1. Custom Keycloak Integration: We decided to implement a custom Keycloak integration instead of using the FastAPIKeycloak library. This gave us more control and allowed us to work around the issues we were facing.

  2. Database Structure: We designed the User model to store Keycloak IDs and sync user data between Keycloak and our database.

  3. Authentication Flow: We implemented a standard OAuth2 flow with Keycloak, including login, callback, and token validation.

Next Steps

  1. Fix Swagger UI Authentication: Investigate and fix the issue with Swagger UI authentication not being properly applied to API requests.

  2. Implement Additional Models: Create additional database models for the Meta Agent Platform as needed.

  3. Enhance Security: Improve token validation by properly verifying token signatures in production.

  4. User Management: Implement additional user management features, such as user creation, updating, and deletion through the API.

References