In the latest installment of our exploration of the Tantor Postgres 17.5.0 DBMS innovations, we delve into the realm of authorization support through OAuth 2.0 Device Authorization Flow. This contemporary and secure access method empowers applications to request access to PostgreSQL on behalf of users via an external identification and access control provider, such as Keycloak. This feature is particularly beneficial in cloud environments and microservice architectures, and it will also be available in PostgreSQL 18.
We will guide you through the process of configuring OAuth authorization in PostgreSQL using Keycloak. Our journey will encompass the following steps: configuring Keycloak, preparing PostgreSQL, writing an OAuth token validator, and verifying successful authorization via psql using Device Flow.
Keycloak Setup by a Security Engineer
Keycloak serves as an open-source identification and access control system, streamlining user management, access control to applications and data, and providing a single sign-on (SSO) experience. By utilizing Keycloak, developers can simplify access setup, password recovery, profile editing, and one-time password distribution, all without the need for additional login forms.
Keycloak Launch
To initiate Keycloak, we will utilize a Docker image, opening port 8080 and creating an initial admin user with the username and password set to “admin.” The command to launch the container is as follows:
docker run --name keycloak -p 8080:8080 -e KC_BOOTSTRAP_ADMIN_USERNAME=admin -e KC_BOOTSTRAP_ADMIN_PASSWORD=admin quay.io/keycloak/keycloak:26.2.1 start-devAfter executing the command, navigate to http://localhost:8080 in your browser. The Keycloak admin panel will prompt for a username and password—enter “admin” for both.
The security engineer will configure user access rights in Keycloak following these steps:
Creating a Realm
A realm acts as a security configuration area encompassing user accounts, roles, groups, and authorization settings. To create a realm, click on “Manage realms” in the upper-left corner of the interface. On the “Manage realms” page, select “Create realm” and enter “postgres-realm” in the “Realm name” field. After clicking “Create,” the realm will be established and activated as the current one.
Creating Users
Users are entities permitted to log into the system, each potentially possessing attributes such as email, username, address, phone number, and birthday. To create a new user, navigate to the “Users” tab in the left panel and click “Create new user.” Fill in the “Username” field with “alice” and complete the remaining fields for email, first name, and last name. After clicking “Create,” switch to the “Credentials” tab to set the password to “alice,” ensuring the temporary switch for password reset is turned off. Click “Save” to finalize the password setup.
Creating Client Scopes
Client scopes allow for the limitation of access rights declared in tokens, enabling clients to request only the necessary roles, enhancing both security and manageability. Switch to the “Client scopes” tab and click “Create client scope.” Enter “postgres” in the “Name” field, select “Default type,” activate “Include in token scope,” and save the changes.
Creating a Client
Clients represent applications and services that can request user authorization. To create a client, go to the “Clients” tab and click “Create client.” In the “General settings” window, enter “postgres-client” in the Client ID field and proceed by clicking “Next.” In the “Capability config” window, enable “Client authentication,” disable “Standard flow,” and enable “OAuth 2.0 Device Authorization Grant.” Click “Next” and then “Save” without making changes in the “Login settings” window.
After creating the client, verify the settings in the “Client scopes” tab. The “Credentials” tab will contain the “Client Secret,” which will be required for logging into PostgreSQL.
Configuring PostgreSQL by a Database Administrator
To facilitate OAuth operations, PostgreSQL requires specific configurations:
- Creating a user in PostgreSQL.
- Configuring parameters in the
postgresql.conffile. - Setting up parameters in the
pg_ident.conffile for user mapping between Keycloak and PostgreSQL, if applicable. - Configuring parameters in the
pg_hba.conffile.
Creating Roles
A role represents an entity that can own objects and possess specific rights within the database. To create a role and grant it connection rights, execute the following commands:
CREATE ROLE alice;
ALTER ROLE alice WITH LOGIN;Configuring the postgresql.conf File
In the oauth_validator_libraries parameter, specify the name of the validator responsible for token verification:
oauth_validator_libraries = 'oauth_validator'If only one verification library is provided, it will be the default for all OAuth connections. If an empty string is set, OAuth connections will be denied.
User Mapping Between Keycloak and PostgreSQL
User mapping can be achieved through two methods:
Mapping Users via the pg_ident.conf File
Configure the display of Keycloak user IDs and databases as follows:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
oauthmap "0fc72b6f-6221-4ed8-a916-069e7a081d14" "alice"Mapping Users through a Validator
This method is detailed in the section on writing a validator.
Configuring the pg_hba.conf File
Set up client login to the database with the following configuration:
# TYPE DATABASE USER ADDRESS METHOD
local all all oauth issuer="http://192.168.0.156:8080/realms/postgres-realm/.well-known/openid-configuration" scope="openid postgres" map="oauthmap"In the fourth field, specify “oauth” along with its parameters. The issuer parameter should point to the discovery service URL. Additionally, set the algorithm for user mapping between Keycloak and PostgreSQL.
Mapping Users via pg_ident.conf
Add the map parameter to indicate the mapping ID from the pg_ident.conf file:
# TYPE DATABASE USER ADDRESS METHOD
local all all oauthMapping Users through a Validator
In this case, the delegate_ident_mapping=1 parameter should be specified instead of the map parameter:
# TYPE DATABASE USER ADDRESS METHOD
local all all oauthWriting a Validator by the Developer
OAuth authentication modules define a set of callbacks to implement their functionality. The server invokes these callbacks as needed to process user authorization requests.
Implementation of the Token Validator
When mapping users between Keycloak and PostgreSQL, the implementation differs based on whether pg_ident.conf or a validator is used. Token verification involves ensuring the required scope specified in pg_hba.conf is present in the token received from the server. Upon successful verification, the user ID from the sub token field is assigned to res->authn_id.
The main verification logic is encapsulated in the validate_token function, which follows this general workflow:
- Analyze the token content.
- Extract the
subandscopefields from the JWT token. - Assign the
subvalue tores->authn_id. - Compare the permissions granted by the token with those required by the corresponding entry in
pg_hba.conf. - Set the authorization result.
- Map the
subvalue to the entries inpg_ident.conf.
We will now begin writing our training validator, with the source code available on GitHub. First, create the oauth_validator.c file within the oauth_validator folder.
#include
#include "postgres.h"
#include "token_utils.h"
#include "fmgr.h"
#include "libpq/oauth.h"
#include "miscadmin.h"
#include "nodes/pg_list.h"
#include "utils/builtins.h"
PG_MODULE_MAGIC;
/*
* Declarations of internal module functions.
*/
static void validator_startup(ValidatorModuleState *state);
static void validator_shutdown(ValidatorModuleState *state);
static bool validate_token(const ValidatorModuleState *state,
const char *token,
const char *role,
ValidatorModuleResult *result);
/*
* Structure with pointers to OAuth token validator callback functions.
* PostgreSQL calls these functions during certain phases of the module's lifecycle.
*/
static const OAuthValidatorCallbacks validator_callbacks = {
PG_OAUTH_VALIDATOR_MAGIC, /* Magic number for API version check */
.startup_cb = validator_startup, /* Validator initialization function */
.shutdown_cb = validator_shutdown, /* Validator shutdown function */
.validate_cb = validate_token /* Token validation function */
};
/*
* Entry point for the OAuth validator module.
* PostgreSQL calls this function when loading the module.
*/
const OAuthValidatorCallbacks *
_PG_oauth_validator_module_init(void)
{
return &validator_callbacks;
}
/*
* Validator initialization function.
* Called once when the module is loaded.
*/
static void
validator_startup(ValidatorModuleState *state)
{
/*
* Check if the server version matches the one the module was built with.
* (Real production modules shouldn't do this, as it breaks upgrade compatibility.)
*/
if (state->sversion != PG_VERSION_NUM)
elog(ERROR, "oauth_validator: server version mismatch: sversion=%d", state->sversion);
}
/*
* Validator shutdown function.
* Called when the module is unloaded or the server shuts down.
*/
static void
validator_shutdown(ValidatorModuleState *state)
{
/* Nothing to do for now, but resource cleanup could be added here if necessary. */
}
/*
* Main OAuth token validation function.
*
* Parameters:
* - state: validator module state (may contain configuration etc.);
* - token: string containing the token to validate;
* - role: PostgreSQL role the client is trying to connect as;
* - res: structure to store the validation result.
*
* Returns true if the token is valid, false otherwise.
*/
static bool
validate_token(const ValidatorModuleState *state,
const char *token, const char *role,
ValidatorModuleResult *res)
{
char *sub = NULL; /* Value of the "sub" field from the token (user identifier) */
char *scope = NULL; /* Value of the "scope" field from the token (allowed scopes) */
const char *token_payload = NULL; /* Token payload as JSON string */
List *granted_scopes = NIL; /* List of scopes granted by the token */
List *required_scopes = NIL; /* List of required scopes from HBA configuration */
bool matched = false; /* Flag indicating whether required scopes are satisfied */
/* Initialize result */
res->authn_id = NULL; /* Authentication ID (sub) */
res->authorized = false; /* Authorization flag */
/* Extract payload from the token */
token_payload = parse_token_payload(token);
if (token_payload == NULL)
{
elog(LOG, "Invalid token: missing payload: %s", token);
return false;
}
/* Extract 'sub' and 'scope' fields from the payload */
extract_sub_scope_fields(token_payload, &sub, &scope);
if (!sub || !scope)
{
elog(LOG, "Invalid token: missing sub and/or scope fields: %s", token);
return false;
}
/* Set authentication ID (sub) in the result */
res->authn_id = pstrdup(sub);
/* Split the token's scope field into a list */
granted_scopes = split_scopes(scope);
/* Split the required scopes from HBA file into a list */
required_scopes = split_scopes(MyProcPort->hba->oauth_scope);
if (!granted_scopes || !required_scopes)
return false;
/* Check if the granted scopes satisfy the required scopes */
matched = check_scopes(granted_scopes, required_scopes);
/* Set authorization result flag */
res->authorized = matched;
return true;
}With this comprehensive overview, we have laid the groundwork for implementing OAuth 2.0 Device Authorization Flow in PostgreSQL, enhancing security and streamlining access management in modern applications.
