user access

The Tantor Postgres 17.5.0 DBMS introduces OAuth 2.0 Device Authorization Flow for secure access to PostgreSQL via external identification providers like Keycloak. This feature will also be available in PostgreSQL 18. The setup involves configuring Keycloak, preparing PostgreSQL, writing an OAuth token validator, and verifying authorization through psql. Keycloak is an open-source access control system that simplifies user management and provides a single sign-on experience. To launch Keycloak, a Docker image is used, and an admin user is created with the username and password set to "admin." The process includes creating a realm named "postgres-realm," adding users (e.g., a user named "alice"), establishing client scopes, and creating a client named "postgres-client" with OAuth 2.0 Device Authorization Grant enabled. PostgreSQL requires specific configurations for OAuth operations, including creating a user, adjusting parameters in the postgresql.conf file, and setting up user mapping in pg_ident.conf and pg_hba.conf files. A role named "alice" is created with login rights. The postgresql.conf file specifies the OAuth validator library, and user mapping can be done via pg_ident.conf or a custom validator. The pg_hba.conf file is configured to allow client login to the database using OAuth, specifying the issuer parameter pointing to the Keycloak discovery service URL. A custom validator is implemented to handle token validation, ensuring that the required scopes are present in the received token. The validator includes functions for startup, shutdown, and token validation, which processes the token content and checks permissions against the HBA configuration.