Оффлайн
- Регистрация
- 9 Май 2015
- Сообщения
- 1,605
- Баллы
- 155
Hello! Today we will talk about authorization support via OAuth 2.0 Device Authorization Flow implemented in Tantor Postgres 17.5.0 DBMS. This is a modern and secure access method that allows applications to request access to PostgreSQL on behalf of the user through an external identification and access control provider, such as Keycloak, which is especially convenient for cloud environments and microservice architectures (the feature will also be available in PostgreSQL 18). In this article, we'll take a step-by-step look at configuring OAuth authorization in PostgreSQL using Keycloak: configure Keycloak, prepare PostgreSQL, write an OAuth token validator in PostgreSQL, and verify successful authorization via psql using Device Flow.
Introduction
Support for authorization via is a modern and secure way of providing access, introduced in PostgreSQL 18. This authorization method allows applications to request access to PostgreSQL on behalf of the user through an external identification and access control provider, such as , which is especially convenient for cloud environments and microservices architecture. Support for this functionality in Tantor Postgres has been implemented since version 17.5.0, introduced in June 2025.
Unlike password authentication (password, md5, SCRAM), OAuth allows you to centralize account management and security policies in a single identity and access control provider. Device Authorization Flow is ideal for scenarios where the client side is limited or absent (for example, terminal applications, automated services). The user confirms access on a separate device via a browser or a mobile application. This makes the authorization procedure more secure, since it is impossible to intercept the password on the client side.
In this article, we will take a step-by-step look at configuring OAuth authorization in PostgreSQL using Keycloak: configure Keycloak, prepare PostgreSQL, write an OAuth token validator in PostgreSQL, and verify successful authorization via psql using Device Flow. In other words, let's go through the following scheme for configuring an OAuth connection in PostgreSQL:
Keycloak setup by a security engineer
Keycloak is an open source identification and access control system that allows you to manage user identification, control access to applications and data, providing a single point of entry (SSO). Keycloak makes it easier to set up access, restore passwords, edit profiles, and send out one-time passwords, eliminating the need for developers to create additional login forms. With Keycloak, these processes can be integrated with just a few mouse clicks.
Keycloak launch
Let's launch Keycloak using a Docker image. We will also open port 8080 and create an initial admin user with the username admin and password admin. Using the --name option, we will set the name of the container to be created as keycloak.
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-dev
Next, enter the address in the browser , the Keycloak admin panel will open and ask for a username and password. Let's enter admin, admin.
The security engineer sets up user access rights in Keycloak in the following order:
Let's take a closer look at each stage.
Creating a Realm
Realm is a security configuration area that includes user accounts, roles, groups, and authorization settings. To create it, click Manage realms in the upper-left corner.
You will see the Manage realms page:
Next, click on Create realm, and in the dialog box that appears, enter "postgres-realm" in the Realm name field.
After clicking on Create, our realm will be created and will become the current one.
Creating Users
Users are entities that can log in to the system. They can have associated attributes such as email, username, address, phone number, and birthday. To open the User creation window, click on the Users tab in the left panel:
Next, click Create new user, a window will appear with the data entry for the new user. In the Username field, enter the name "alice" and fill in the fields Email, First name, and Last name.
Click the Create button, a new user window will appear. You will need an ID to the DB administrator for mapping the Keycloak and PostgreSQL user in the pg_ident.conf file.
Switch to the Credentials tab and click on Set password to set the password:
Enter the password "alice". Temporary switch (temporary password) is set to the off position, otherwise, at the first login, the system will require the user to set a new password.
Click Save, then in the dialog box that appears click Save password:
The password is set:
Creating Client scopes
The Client scope is a way to limit the access rights that are declared in tokens. It allows the client to request only the roles they need, which makes tokens more secure and manageable.
Switch to the Client scopes tab:
Click on the Create client scope button, a window for creating a scope will open. Enter the "postgres" value in the Name field, select the Default type, activate Include in token scope, and save by clicking Save.
Creating a Client
Clients are 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 that appears, enter "postgres-client" in the Client ID field. Then click Next.
In the Capability config window that appears:
We don't change anything in the Login settings window, just click Save.
The client we've created opens:
Next, go to the Client scopes tab and check that:
The rest of the scopes are not important for our example, you can leave everything as it is.
The Credentials tab contains the Client Secret, which we will need to enter in the terminal when logging into PostgreSQL (see below in the "Authorization via psql" section)
Configuring PostgreSQL by a database administrator
The possibility of OAuth operation presupposes the appropriate configuration of PostgreSQL:
A role is an entity that can own objects and have certain rights in the database. A role can represent a user, a group, or both, depending on the use case.
Create a role and give it the right to connect to the database:
CREATE ROLE alice;
ALTER ROLE alice WITH LOGIN;
Configuring the postgresql.conf file
In the oauth_validator_libraries parameter we will set the name of the validator that will verify the token (see the section ).
oauth_validator_libraries = 'oauth_validator'
If only one verification library is provided, it will be used by default for all OAuth connections; otherwise, all entries of oauth HBA must explicitly set the verification tool selected from this list. If an empty string value is set (by default), OAuth connections will be denied.
User mapping between Keycloak and PostgreSQL
There are two ways to map users:
Configure the display of Keycloak user IDs and databases:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
oauthmap "0fc72b6f-6221-4ed8-a916-069e7a081d14" "alice"
The name of the mapping is indicated in the first column, and the user ID from Keycloak is indicated in the second column (see ), in the third is the name of the role in PostgreSQL.
Mapping users through a validator
It is described below in the section .
Configuring the pg_hba.conf file
Setting up the client's login to the database:
# TYPE DATABASE USER ADDRESS METHOD local all all oauth issuer="" scope="openid postgres" map="oauthmap"
In the fourth field, set oauth and then its parameters. In the issuer parameter, set the URL of the discovery service . In the Scope parameter, we set the access areas that will be requested from Keycloak for the client.
Next, you need to set the algorithm for mapping users between Keycloak and PostgreSQL (see "").
Mapping users via pg_ident.conf
We add the map parameter, in which we set the map id from the pg_ident.conf file, we have this "oauthmap":
# TYPE DATABASE USER ADDRESS METHOD local all all oauth
issuer="" scope="openid postgres" map="oauthmap"
Mapping users through a validator
In this case, the delegate_ident_mapping=1 parameter should be set instead of the map parameter.
# TYPE DATABASE USER ADDRESS METHOD local all all oauth
issuer="" scope="openid postgres" delegate_ident_mapping=1
The delegate_ident_mapping parameter has a higher priority than map, so if the map parameter is also specified with delegate_ident_mapping=1, it will be ignored and user mapping will go through the validator.
Writing a validator by the developer
OAuth authentication modules implement their functionality by defining a set of callbacks. The server will call them as needed to process the authorization request from the user.
Implementation of the token validator
When mapping users between Keycloak and PostgreSQL, the implementation via pg_ident.conf differs from the mapping via the validator only by the implementation of the get_user function. In the example below, the mapping is implemented using pg_ident.conf. Token verification It consists in verifying that the required scope specified in pg_hba.conf is present in the scope field in the token received from the server. Upon successful verification, the user ID from the sub token field is assigned res->authn_id.
The main verification logic is implemented in the validate_token function. The general scheme of its work:
The source string of the token is analyzed to extract its contents (payload). If the token has an incorrect format or the contents cannot be extracted, the verification fails.
The token content must include both fields:
If any of these fields are missing, the verification is considered failed.
The sub value is assigned to the res->authn_id field, which PostgreSQL uses to identify the user. This value is then compared with the entries in pg_ident.conf to determine the actual role in the database that the user can use.
The permissions granted by the token are compared with those required by the corresponding entry in pg_hba.conf (from oauth_scope). If all required permissions are present in the token, the verification is considered successful.
The res->authorized flag is set to true if the permissions match, otherwise false.
The sub value is then mapped (outside of this module) to the entries in pg_ident.conf to determine the actual role in the database that the user can use.
Let's start writing our training validator (its sources are also posted on ). First, let's create the oauth_validator folder, and in it we'll create the oauth_validator.c file
#include <string.h>
#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;
}
token_utils.h/.c -- utility functions
#ifndef TOKEN_UTILS_H
#define TOKEN_UTILS_H
#include <stdbool.h>
#include "common/jsonapi.h"
#include "nodes/pg_list.h"
const char* parse_token_payload(const char *token);
void extract_sub_scope_fields(const char *json, char **sub_field, char **scope_field);
const char *decode_base64(const char *b64);
char *base64url_to_base64(const char *b64url);
List *split_scopes(const char *raw);
bool check_scopes(List *granted, List *required);
#endif
#include "postgres.h"
#include "token_utils.h"
#include "common/base64.h"
#include "mb/pg_wchar.h"
#define SUB_FIELD 0 /* Index for 'sub' field */
#define SCOPE_FIELD 1 /* Index for 'scope' field */
/*
* JSON object field handler.
* Marks that the currently processed field is 'sub' or 'scope'
* to store its value at the next processing stage.
*/
static JsonParseErrorType
token_field_start(void *state, char *fname, bool isnull)
{
char **fields = (char **) state;
if (strcmp(fname, "sub") == 0)
fields[SUB_FIELD] = (char *) 1; /* Mark that we are processing 'sub' field */
else if (strcmp(fname, "scope") == 0)
fields[SCOPE_FIELD] = (char *) 1; /* Mark that we are processing 'scope' field */
return JSON_SUCCESS;
}
/*
* JSON scalar value handler.
* Stores the value of 'sub' or 'scope' if it was marked earlier.
*/
static JsonParseErrorType
token_scalar(void *state, char *token, JsonTokenType tokentype)
{
char **fields = (char **) state;
if (fields[SUB_FIELD] == (char *) 1)
fields[SUB_FIELD] = pstrdup(token); /* Save the value of 'sub' */
else if (fields[SCOPE_FIELD] == (char *) 1)
fields[SCOPE_FIELD] = pstrdup(token); /* Save the value of 'scope' */
return JSON_SUCCESS;
}
/*
* Extracts 'sub' and 'scope' fields from a JSON string.
*
* Parameters:
* - json: JSON string
* - sub_field: returns the value of 'sub' field
* - scope_field: returns the value of 'scope' field
*/
void
extract_sub_scope_fields(const char *json, char **sub_field, char **scope_field)
{
JsonLexContext lex;
JsonSemAction sem;
char **fields = palloc0(sizeof(char *) * 2); /* Allocate memory for 2 strings ('sub', 'scope') */
*sub_field = NULL;
*scope_field = NULL;
/* Create a lexical context for JSON parsing */
makeJsonLexContextCstringLen(&lex, json, strlen(json), GetDatabaseEncoding(), true);
/* Set up JSON parser handlers */
memset(&sem, 0, sizeof(sem));
sem.semstate = (void *) fields;
sem.object_field_start = token_field_start;
sem.scalar = token_scalar;
/* Start JSON parsing */
pg_parse_json(&lex, &sem);
/* Return the found values */
*sub_field = fields[SUB_FIELD];
*scope_field = fields[SCOPE_FIELD];
}
/*
* Extracts the payload from a JWT token.
* Returns the decoded payload string in JSON format.
*/
const char*
parse_token_payload(const char *token)
{
char *dot1 = NULL;
char *dot2 = NULL;
int payload_len = 0;
char *payload_b64url = NULL;
char *b64 = NULL;
if(!token)
return NULL;
/* Find the first and second dots in JWT (separators for header.payload.signature) */
dot1 = strchr(token, '.');
dot2 = dot1 ? strchr(dot1 + 1, '.') : NULL;
if (!dot1 || !dot2)
{
elog(LOG, "Invalid token format, two dots required: %s", token);
return NULL;
}
/* Extract the encoded payload between the dots */
payload_len = dot2 - (dot1 + 1);
payload_b64url = pnstrdup(dot1 + 1, payload_len);
/* Convert base64url to regular base64 */
b64 = base64url_to_base64(payload_b64url);
/* Decode base64 to JSON string */
return decode_base64(b64);
}
/*
* Converts a base64url string to base64 format.
* Replaces '-' with '+', '_' with '/' and adds padding '=' if necessary.
*/
char *
base64url_to_base64(const char *b64url)
{
int len = strlen(b64url);
int pad = (4 - (len % 4)) % 4; /* Determine the number of '=' padding characters */
char *b64 = palloc(len + pad + 1);
for (int i = 0; i < len; i++)
{
if (b64url == '-')
b64 = '+';
else if (b64url == '_')
b64 = '/';
else
b64 = b64url;
}
/* Add padding '=' */
for (int i = 0; i < pad; i++)
b64[len + i] = '=';
b64[len + pad] = '\0';
return b64;
}
/*
* Decodes a base64 string into a regular string.
* Returns the decoded string or NULL in case of error.
*/
const char *
decode_base64(const char *b64)
{
int encoded_len = strlen(b64);
int max_decoded_len = pg_b64_dec_len(encoded_len); /* Calculate required buffer length */
char *decoded = palloc(max_decoded_len + 1);
int decoded_len = pg_b64_decode(b64, encoded_len, decoded, max_decoded_len);
if (decoded_len <= 0)
{
elog(LOG, "Invalid token format: base64 decoding error");
return NULL;
}
decoded[decoded_len] = '\0';
return decoded;
}
/*
* Splits a space-separated string (e.g., scope list from token) into a List of strings.
*/
List *
split_scopes(const char *raw)
{
List *result = NIL;
char *str = pstrdup(raw); /* Make a copy of the string because strtok modifies it */
char *tok = strtok(str, " ");
while (tok)
{
result = lappend(result, pstrdup(tok));
tok = strtok(NULL, " ");
}
return result;
}
/*
* String comparison function for list sorting.
*/
static int
list_string_cmp(const ListCell *a, const ListCell *b)
{
const char *sa = (const char *) lfirst(a);
const char *sb = (const char *) lfirst(b);
return strcmp(sa, sb);
}
/*
* Checks whether all required scopes are present in the granted scopes.
* Lists are sorted beforehand for easier comparison.
*
* Returns true if all required scopes are found in granted scopes.
*/
bool
check_scopes(List *granted, List *required)
{
ListCell *gcell;
ListCell *rcell;
/* Sort both lists to simplify comparison */
list_sort(granted, list_string_cmp);
list_sort(required, list_string_cmp);
gcell = list_head(granted);
rcell = list_head(required);
while (rcell != NULL && gcell != NULL)
{
char *r = (char *) lfirst(rcell);
char *g = (char *) lfirst(gcell);
int cmp = strcmp(r, g);
if (cmp == 0)
{
/* Match found --- move to the next required element */
rcell = lnext(required, rcell);
gcell = lnext(granted, gcell);
}
else if (cmp > 0)
{
/* granted is behind --- move to the next granted element */
gcell = lnext(granted, gcell);
}
else
{
/* required element not found in granted --- return false */
return false;
}
}
/* If not all required elements were found --- error */
if (rcell != NULL)
return false;
return true;
}
Makefile
# contrib/oauth_validator/Makefile
PGFILEDESC = "oauth_validator - OAuth validator"
MODULE_big = oauth_validator
OBJS =\
$(WIN32RES)\
oauth_validator.o\
token_utils.o
PG_CPPFLAGS += -I$(top_srcdir)/src/common
PG_CPPFLAGS += -I$(libpq_srcdir)
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
Callbacks
startup_cb callback
The startup_cb callback is executed immediately after the module is loaded. It can be used to configure the local state and perform additional initialization if required. If the validator has a state, it can use the state->private_data field to store it.
typedef void (*ValidatorStartupCB) (ValidatorModuleState *state);
ValidatorStartupCB startup_cb;
validate_cb callback
The validate_cb callback is executed when the user tries to pass authorization using OAuth. Any state set in previous calls will be available in state->private_data.
typedef bool (*ValidatorValidateCB) (const ValidatorModuleState *state,
const char *token, const char *role,
ValidatorModuleResult *result);
ValidatorValidateCB validate_cb;
The argument token will contain a carrier token for verification. PostgreSQL made sure that the token was formed syntactically correct, but no other check was carried out. The role parameter contains the role on behalf of which the user requested login. The callback should set the output parameters in the resulting structure, which is defined as:
typedef struct ValidatorModuleResult
{
bool authorized;
char *authn_id;
} ValidatorModuleResult;
The connection will be established only if the validator sets the result->authorized parameter to true. To authenticate a user under an authenticated username (i.e. a specific one using a token), memory must be allocated (using the palloc function ), and a pointer to this memory region must be assigned to the result->authn_id field. Alternatively, the result->authn_id parameter can be set to NULL if the token is valid but the associated user ID cannot be determined.
If the token verification fails , the validator must return false due to incorrect token format, lack of necessary user rights, or other error, then any parameters from the result argument are ignored and the connection is interrupted. In case of successful verification of the token, the validator must return true.
The behavior after returning from validate_cb depends on the specific HBA setting. Usually, the user name result->authn_id must correspond exactly to the role under which the user logs in (this behavior can be changed using the user's card). But when authenticating according to the HBA rule with delegate_ident_mapping enabled, PostgreSQL will not perform any checks on the result->authn_id value at all; in this case, the validator must ensure that the token has sufficient privileges so that the user can log in under the specified role.
Shutdown_cb callback
The shutdown_cb callback is executed when there is a server process associated with the connection. If the validator has any allocated state, this callback should release it to avoid resource leakage.
typedef void (*ValidatorShutdownCB) (ValidatorModuleState *state);
ValidatorShutdownCB shutdown_cb;
Authorization process
Logging
Let's start by configuring logging. To see what requests PostgreSQL sends and what responses it receives, query logging can be enabled in postgresql.conf:
log_connections = on
Examples of logs will be presented below, prefixes will appear at the beginning of the lines, meaning the following:
[libcurl] * Trying 192.168.0.156:8080...
[libcurl] * Connected to 192.168.0.156 (192.168.0.156) port 8080 (#0)
[libcurl] > GET /realms/postgres-realm/.well-known/openid-configuration HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] >
[libcurl] < HTTP/1.1 200 OK
[libcurl] < content-length: 6638
[libcurl] < Cache-Control: no-cache, must-revalidate, no-transform, no-store
[libcurl] < Content-Type: application/json;charset=UTF-8
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"issuer":"","authorization_endpoint":"","token_endpoint":"","introspection_endpoint":"","userinfo_endpoint":"","end_session_endpoint":"","frontchannel_logout_session_supported":true,"frontchannel_logout_supported":true,"jwks_uri":"","check_session_iframe":"","grant\_types\_supported":["authorization\_code","client\_credentials","implicit","password","refresh\_token","urn:ietf:params:oauth:grant-type:device\_code","urn:ietf:params:oauth:grant-type:token-exchange","urn:ietf:params:oauth:grant-type:uma-ticket","urn:openid:params:grant-type:ciba"],"acr\_values\_supported":["0","1"],"response\_types\_supported":["code","none","id\_token","token","id\_token token","code id_token","code token","code id_token token"],"subject_types_supported":["public","pairwise"],"prompt_values_supported":["none","login","consent"],"id_token_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"id_token_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"id_token_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"userinfo_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512","none"],"userinfo_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"userinfo_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"request_object_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512","none"],"request_object_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"request_object_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"response_modes_supported":["query","fragment","form_post","query.jwt","fragment.jwt","form_post.jwt","jwt"],"registration_endpoint":"","token_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"token_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"introspection_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"introspection_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"authorization_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"authorization_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"authorization_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"claims_supported":["aud","sub","iss","auth_time","name","given_name","family_name","preferred_username","email","acr"],"claim_types_supported":["normal"],"claims_parameter_supported":true,"scopes_supported":["openid","offline_access","organization","service_account","postgres","address","phone","acr","profile","microprofile-jwt","web-origins","roles","basic","email"],"request_parameter_supported":true,"request_uri_parameter_supported":true,"require_request_uri_registration":true,"code_challenge_methods_supported":["plain","S256"],"tls_client_certificate_bound_access_tokens":true,"revocation_endpoint":"","revocation_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"revocation_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"backchannel_logout_supported":true,"backchannel_logout_session_supported":true,"device_authorization_endpoint":"","backchannel_token_delivery_modes_supported":["poll","ping"],"backchannel_authentication_endpoint":"","backchannel_authentication_request_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","ES256","RS256","ES512","PS256","PS512","RS512"],"require_pushed_authorization_requests":false,"pushed_authorization_request_endpoint":"","mtls_endpoint_aliases":{"token_endpoint":"","revocation_endpoint":"","introspection_endpoint":"","device_authorization_endpoint":"","registration_endpoint":"","userinfo_endpoint":"","pushed_authorization_request_endpoint":"","backchannel_authentication_endpoint":""},"authorization\_response\_iss\_parameter\_supported":true}
auth device
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/auth/device HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 47
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > scope=openid+postgres&client_id=postgres-client
[libcurl] < HTTP/1.1 200 OK
[libcurl] < Cache-Control: no-store, must-revalidate, max-age=0
[libcurl] < content-length: 296
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"device_code":"tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY","user_code":"WXAI-ZNVY","verification_uri":"","verification_uri_complete":"","expires\_in":600,"interval":5}
token
The client is awaiting approval of the user's authorization request:
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/token HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 147
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > device_code=tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY&grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Adevice_code&client_id=postgres-client
[libcurl] < HTTP/1.1 400 Bad Request
[libcurl] < Cache-Control: no-store
[libcurl] < Pragma: no-cache
[libcurl] < content-length: 98
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"error":"authorization_pending","error_description":"The authorization request is still pending"}
The user is approved:
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/token HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 147
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > device_code=tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY&grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Adevice_code&client_id=postgres-client
[libcurl] < HTTP/1.1 200 OK
[libcurl] < Cache-Control: no-store
[libcurl] < Pragma: no-cache
[libcurl] < content-length: 3307
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"access_token":"eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICIwb1RQSV85LXVIQWJ5Q0t3M2luTm5MTW5hU21hc05nWS1OaDVkTzJ4X0lNIn0.eyJleHAiOjE3NDYwMzEyNTMsImlhdCI6MTc0NjAzMDk1MywiYXV0aF90aW1lIjoxNzQ2MDMwOTUyLCJqdGkiOiJvbnJ0ZGc6MWU3MmRlNGUtNTRhYi00OTZjLWIxYWYtY2FhYmRiYzJlYzExIiwiaXNzIjoiaHR0cDovLzE5Mi4xNjguMC4xNTY6ODA4MC9yZWFsbXMvcG9zdGdyZXMtcmVhbG0iLCJhdWQiOiJhY2NvdW50Iiwic3ViIjoiMGZjNzJiNmYtNjIyMS00ZWQ4LWE5MTYtMDY5ZTdhMDgxZDE0IiwidHlwIjoiQmVhcmVyIiwiYXpwIjoicG9zdGdyZXMtY2xpZW50Iiwic2lkIjoiNjRlMDUzMTMtM2UyMi00MmNjLWE0YmItOTAxODU2YTFhYjMzIiwiYWxsb3dlZC1vcmlnaW5zIjpbIi8qIl0sInJlYWxtX2FjY2VzcyI6eyJyb2xlcyI6WyJvZmZsaW5lX2FjY2VzcyIsImRlZmF1bHQtcm9sZXMtcG9zdGdyZXMtcmVhbG0iLCJ1bWFfYXV0aG9yaXphdGlvbiJdfSwicmVzb3VyY2VfYWNjZXNzIjp7ImFjY291bnQiOnsicm9sZXMiOlsibWFuYWdlLWFjY291bnQiLCJtYW5hZ2UtYWNjb3VudC1saW5rcyIsInZpZXctcHJvZmlsZSJdfX0sInNjb3BlIjoib3BlbmlkIHByb2ZpbGUgcG9zdGdyZXMiLCJuYW1lIjoiYWxpY2UgcG9zdGdyZXMiLCJwcmVmZXJyZWRfdXNlcm5hbWUiOiJhbGljZSIsImdpdmVuX25hbWUiOiJhbGljZSIsImZhbWlseV9uYW1lIjoicG9zdGdyZXMifQ.RXMszI-snIdrXyyTw74U8QXQeDG3zpfV4OvxYuJQvsb86eauXkKHAH35GfEm3XvQbtmpdSdfs1S4i11d69dUjpVTgPpzx6G7IXCXj2NTowzZuyuvdnLxPi1aXdxXqOKNSLSj5PXhGIaZhWsn2sR8dAJ0jjWTUO_lh8qJuJYaDcFulWn_flHVGQYzMZ5PTneRadg8h_1dWp4HSr6yC74NmF94dnOBmytivM4a__Wcq6TkZ3KLn_gafqnn72HpWY0WRwyZdQuzc5o8mE3UUAoKukxMnwDG7Yhxif2Y
a5aCloMbL9aDghbMypahl3MiJHHx3j50FavSRm0FJa3zK9w","expires_in":300,"refresh_expires_in":1800,"refresh_token":"eyJhbGciOiJIUzUxMiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICJjOGNiNjY3Ni00OTAxLTRmNjItOTI0OS1kMzY2MWI5Mjg3OTIifQ.eyJleHAiOjE3NDYwMzI3NTMsImlhdCI6MTc0NjAzMDk1MywianRpIjoiZTJkNzkzODUtNjBhZS00MTIwLWIwODAtNjVmYWU4ZmNhYzIzIiwiaXNzIjoiaHR0cDovLzE5Mi4xNjguMC4xNTY6ODA4MC9yZWFsbXMvcG9zdGdyZXMtcmVhbG0iLCJhdWQiOiJodHRwOi8vMTkyLjE2OC4wLjE1Njo4MDgwL3JlYWxtcy9wb3N0Z3Jlcy1yZWFsbSIsInN1YiI6IjBmYzcyYjZmLTYyMjEtNGVkOC1hOTE2LTA2OWU3YTA4MWQxNCIsInR5cCI6IlJlZnJlc2giLCJhenAiOiJwb3N0Z3Jlcy1jbGllbnQiLCJzaWQiOiI2NGUwNTMxMy0zZTIyLTQyY2MtYTRiYi05MDE4NTZhMWFiMzMiLCJzY29wZSI6Im9wZW5pZCBwcm9maWxlIHdlYi1vcmlnaW5zIHBvc3RncmVzIHJvbGVzIGJhc2ljIn0.43pRSq4PBO7ZY86jt8dIL7xZJylntY_CZXllcRfwfh41IRCOft6iqIWdJQp7TJv_JIDI-_-QeOSf1EC_wzABNg","token_type":"Bearer","id_token":"eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICIwb1RQSV85LXVIQWJ5Q0t3M2luTm5MTW5hU21hc05nWS1OaDVkTzJ4X0lNIn0.eyJleHAiOjE3NDYwMzEyNTMsImlhdCI6MTc0NjAzMDk1MywiYXV0aF90aW1lIjoxNzQ2MDMwOTUyLCJqdGkiOiIzNWM3ZDAyZC05OGFjLTQzMTgtOTg3NC0zYzY0Mjg5NjFhMjgiLCJpc3MiOiJodHRwOi8vMTkyLjE2OC4wLjE1Njo4MDgwL3JlYWxtcy9wb3N0Z3Jlcy1yZWFsbSIsImF1ZCI6InBvc3RncmVzLWNsaWVudCIsInN1YiI6IjBmYzcyYjZmLTYyMjEtNGVkOC1hOTE2LTA2OWU3YTA4MWQxNCIsInR5cCI6IklEIiwiYXpwIjoicG9zdGdyZXMtY2xpZW50Iiwic2lkIjoiNjRlMDUzMTMtM2UyMi00MmNjLWE0YmItOTAxODU2YTFhYjMzIiwiYXRfaGFzaCI6ImphOXRKZ1E0VkVPTTNBZGc0VWJBVGciLCJuYW1lIjoiYWxpY2UgcG9zdGdyZXMiLCJwcmVmZXJyZWRfdXNlcm5hbWUiOiJhbGljZSIsImdpdmVuX25hbWUiOiJhbGljZSIsImZhbWlseV9uYW1lIjoicG9zdGdyZXMifQ.dH5hM21-ygBiCXoA9NVOou-L3esUVJUFFUmt_1fU0jc9al4Lk7EUN-cqHicHZPD48HhkIWMJK7WZjxnZLDlkG7ORGdDKPGccMU4-sGsRuVu-GDuN
5kHAh_NJZsLBXz9UkpNBkd8ROxK3-fbmyYdwsuwNeg6KNhSOj0FxEnxLc0-HrjEE92P7hzq0PD29oY2jhRKcqpbtknMwxFkkMBi8xPgdpuyTmLtJD3-xxYuwMKP7WUGzwzVAFqfrhFm5O5dJxeld5fTFE4Kyl9fR24JcjtfxBeIHVJqLiQkl9Et_KNGiFoXDG4Xwcc7eIUaBnauhY5_froYvKS8NbQxCOUg","not-before-policy":0,"session_state":"64e05313-3e22-42cc-a4bb-901856a1ab33","scope":"openid profile postgres"}
Authorization via psql
For testing purposes, we will allow authorization over the unsecured http protocol (when using https, you will need to configure certificate chains):
export PGOAUTHDEBUG="UNSAFE"
Let's launch psql and set the connection details:
psql "user=alice dbname=postgres oauth_issuer= oauth_client_id=postgres-client oauth_client_secret=YYi8LqfzHRMnqUUlptpWVC2k7eWNrqjX"
We will see the URL and the code that you will need to enter after clicking on this URL:
Visit and enter the code: LJDW-RURX
We enter it in the browser [
Enter the code and click Submit.
Next, we see the user login window:
Enter the username and password (see ) and click on Sign In.
Next, he asks if we are ready to provide (send) such information to PostgreSQL. Click
.
Successfully logged in...
... and we can enter commands in the terminal:
psql (18devel) Type "help" for help.
postgres=>
As a result, we successfully logged in to PostgreSQL.
Conclusion
Integration OAuth 2.0 Device Authorization Flow, introduced in the PostgreSQL database management system 18 and Tantor Postgres 17.5.0, allows the use of the SSO (single sign-on) mechanism. Implementation of centralized access control via providers like Keycloak increase the level of protection, eliminating the risks of password interception, and optimize administration in distributed environments. The article provides a step-by-step guide from setting up Keycloak and PostgreSQL configurations before implementing the token validator and successful authorization via psql.
You will see the Manage realms page:
Next, click on Create realm, and in the dialog box that appears, enter "postgres-realm" in the Realm name field.
After clicking on Create, our realm will be created and will become the current one.
Creating Users
Users are entities that can log in to the system. They can have associated attributes such as email, username, address, phone number, and birthday. To open the User creation window, click on the Users tab in the left panel:
Next, click Create new user, a window will appear with the data entry for the new user. In the Username field, enter the name "alice" and fill in the fields Email, First name, and Last name.
Click the Create button, a new user window will appear. You will need an ID to the DB administrator for mapping the Keycloak and PostgreSQL user in the pg_ident.conf file.
Switch to the Credentials tab and click on Set password to set the password:
Enter the password "alice". Temporary switch (temporary password) is set to the off position, otherwise, at the first login, the system will require the user to set a new password.
Click Save, then in the dialog box that appears click Save password:
The password is set:
Creating Client scopes
The Client scope is a way to limit the access rights that are declared in tokens. It allows the client to request only the roles they need, which makes tokens more secure and manageable.
Switch to the Client scopes tab:
Click on the Create client scope button, a window for creating a scope will open. Enter the "postgres" value in the Name field, select the Default type, activate Include in token scope, and save by clicking Save.
Creating a Client
Clients are 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 that appears, enter "postgres-client" in the Client ID field. Then click Next.
In the Capability config window that appears:
We don't change anything in the Login settings window, just click Save.
The client we've created opens:
Next, go to the Client scopes tab and check that:
The rest of the scopes are not important for our example, you can leave everything as it is.
The Credentials tab contains the Client Secret, which we will need to enter in the terminal when logging into PostgreSQL (see in the "Authorization via psql" section).
Configuring PostgreSQL by a database administrator
The possibility of OAuth operation presupposes the appropriate configuration of PostgreSQL:
A role is an entity that can own objects and have certain rights in the database. A role can represent a user, a group, or both, depending on the use case.
Create a role and give it the right to connect to the database:
CREATE ROLE alice;
ALTER ROLE alice WITH LOGIN;
Configuring the postgresql.conf file
In the oauth_validator_libraries parameter we will set the name of the validator that will verify the token (see the section ).
oauth_validator_libraries = 'oauth_validator'
If only one verification library is provided, it will be used by default for all OAuth connections; otherwise, all entries of oauth HBA must explicitly set the verification tool selected from this list. If an empty string value is set (by default), OAuth connections will be denied.
User mapping between Keycloak and PostgreSQL
There are two ways to map users:
Configure the display of Keycloak user IDs and databases:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
oauthmap "0fc72b6f-6221-4ed8-a916-069e7a081d14" "alice"
The name of the mapping is indicated in the first column, and the user ID from Keycloak is indicated in the second column (see ), in the third is the name of the role in PostgreSQL.
Mapping users through a validator
It is described below in the section .
Configuring the pg_hba.conf file
Setting up the client's login to the database:
# TYPE DATABASE USER ADDRESS METHOD local all all oauth issuer="" scope="openid postgres" map="oauthmap"
In the fourth field, set oauth and then its parameters. In the issuer parameter, set the URL of the discovery service . In the Scope parameter, we set the access areas that will be requested from Keycloak for the client.
Next, you need to set the algorithm for mapping users between Keycloak and PostgreSQL (see "").
Mapping users via pg_ident.conf
We add the map parameter, in which we set the map id from the pg_ident.conf file, we have this "oauthmap":
# TYPE DATABASE USER ADDRESS METHOD local all all oauth
issuer="" scope="openid postgres" map="oauthmap"
Mapping users through a validator
In this case, the delegate_ident_mapping=1 parameter should be set instead of the map parameter.
# TYPE DATABASE USER ADDRESS METHOD local all all oauth
issuer="" scope="openid postgres" delegate_ident_mapping=1
The delegate_ident_mapping parameter has a higher priority than map, so if the map parameter is also specified with delegate_ident_mapping=1, it will be ignored and user mapping will go through the validator.
Writing a validator by the developer
OAuth authentication modules implement their functionality by defining a set of callbacks. The server will call them as needed to process the authorization request from the user.
Implementation of the token validator
When mapping users between Keycloak and PostgreSQL, the implementation via pg_ident.conf differs from the mapping via the validator only by the implementation of the get_user function. In the example below, the mapping is implemented using pg_ident.conf. Token verification It consists in verifying that the required scope specified in pg_hba.conf is present in the scope field in the token received from the server. Upon successful verification, the user ID from the sub token field is assigned res->authn_id.
The main verification logic is implemented in the validate_token function. The general scheme of its work:
The source string of the token is analyzed to extract its contents (payload). If the token has an incorrect format or the contents cannot be extracted, the verification fails.
If any of these fields are missing, the verification is considered failed.
Let's start writing our training validator (its sources are also posted on ). First, let's create the oauth_validator folder, and in it we'll create the oauth_validator.c file
#include <string.h>
#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;
}
token_utils.h/.c -- utility functions
#ifndef TOKEN_UTILS_H
#define TOKEN_UTILS_H
#include <stdbool.h>
#include "common/jsonapi.h"
#include "nodes/pg_list.h"
const char* parse_token_payload(const char *token);
void extract_sub_scope_fields(const char *json, char **sub_field, char **scope_field);
const char *decode_base64(const char *b64);
char *base64url_to_base64(const char *b64url);
List *split_scopes(const char *raw);
bool check_scopes(List *granted, List *required);
#endif
#include "postgres.h"
#include "token_utils.h"
#include "common/base64.h"
#include "mb/pg_wchar.h"
#define SUB_FIELD 0 /* Index for 'sub' field */
#define SCOPE_FIELD 1 /* Index for 'scope' field */
/*
* JSON object field handler.
* Marks that the currently processed field is 'sub' or 'scope'
* to store its value at the next processing stage.
*/
static JsonParseErrorType
token_field_start(void *state, char *fname, bool isnull)
{
char **fields = (char **) state;
if (strcmp(fname, "sub") == 0)
fields[SUB_FIELD] = (char *) 1; /* Mark that we are processing 'sub' field */
else if (strcmp(fname, "scope") == 0)
fields[SCOPE_FIELD] = (char *) 1; /* Mark that we are processing 'scope' field */
return JSON_SUCCESS;
}
/*
* JSON scalar value handler.
* Stores the value of 'sub' or 'scope' if it was marked earlier.
*/
static JsonParseErrorType
token_scalar(void *state, char *token, JsonTokenType tokentype)
{
char **fields = (char **) state;
if (fields[SUB_FIELD] == (char *) 1)
fields[SUB_FIELD] = pstrdup(token); /* Save the value of 'sub' */
else if (fields[SCOPE_FIELD] == (char *) 1)
fields[SCOPE_FIELD] = pstrdup(token); /* Save the value of 'scope' */
return JSON_SUCCESS;
}
/*
* Extracts 'sub' and 'scope' fields from a JSON string.
*
* Parameters:
* - json: JSON string
* - sub_field: returns the value of 'sub' field
* - scope_field: returns the value of 'scope' field
*/
void
extract_sub_scope_fields(const char *json, char **sub_field, char **scope_field)
{
JsonLexContext lex;
JsonSemAction sem;
char **fields = palloc0(sizeof(char *) * 2); /* Allocate memory for 2 strings ('sub', 'scope') */
*sub_field = NULL;
*scope_field = NULL;
/* Create a lexical context for JSON parsing */
makeJsonLexContextCstringLen(&lex, json, strlen(json), GetDatabaseEncoding(), true);
/* Set up JSON parser handlers */
memset(&sem, 0, sizeof(sem));
sem.semstate = (void *) fields;
sem.object_field_start = token_field_start;
sem.scalar = token_scalar;
/* Start JSON parsing */
pg_parse_json(&lex, &sem);
/* Return the found values */
*sub_field = fields[SUB_FIELD];
*scope_field = fields[SCOPE_FIELD];
}
/*
* Extracts the payload from a JWT token.
* Returns the decoded payload string in JSON format.
*/
const char*
parse_token_payload(const char *token)
{
char *dot1 = NULL;
char *dot2 = NULL;
int payload_len = 0;
char *payload_b64url = NULL;
char *b64 = NULL;
if(!token)
return NULL;
/* Find the first and second dots in JWT (separators for header.payload.signature) */
dot1 = strchr(token, '.');
dot2 = dot1 ? strchr(dot1 + 1, '.') : NULL;
if (!dot1 || !dot2)
{
elog(LOG, "Invalid token format, two dots required: %s", token);
return NULL;
}
/* Extract the encoded payload between the dots */
payload_len = dot2 - (dot1 + 1);
payload_b64url = pnstrdup(dot1 + 1, payload_len);
/* Convert base64url to regular base64 */
b64 = base64url_to_base64(payload_b64url);
/* Decode base64 to JSON string */
return decode_base64(b64);
}
/*
* Converts a base64url string to base64 format.
* Replaces '-' with '+', '_' with '/' and adds padding '=' if necessary.
*/
char *
base64url_to_base64(const char *b64url)
{
int len = strlen(b64url);
int pad = (4 - (len % 4)) % 4; /* Determine the number of '=' padding characters */
char *b64 = palloc(len + pad + 1);
for (int i = 0; i < len; i++)
{
if (b64url == '-')
b64 = '+';
else if (b64url == '_')
b64 = '/';
else
b64 = b64url;
}
/* Add padding '=' */
for (int i = 0; i < pad; i++)
b64[len + i] = '=';
b64[len + pad] = '\0';
return b64;
}
/*
* Decodes a base64 string into a regular string.
* Returns the decoded string or NULL in case of error.
*/
const char *
decode_base64(const char *b64)
{
int encoded_len = strlen(b64);
int max_decoded_len = pg_b64_dec_len(encoded_len); /* Calculate required buffer length */
char *decoded = palloc(max_decoded_len + 1);
int decoded_len = pg_b64_decode(b64, encoded_len, decoded, max_decoded_len);
if (decoded_len <= 0)
{
elog(LOG, "Invalid token format: base64 decoding error");
return NULL;
}
decoded[decoded_len] = '\0';
return decoded;
}
/*
* Splits a space-separated string (e.g., scope list from token) into a List of strings.
*/
List *
split_scopes(const char *raw)
{
List *result = NIL;
char *str = pstrdup(raw); /* Make a copy of the string because strtok modifies it */
char *tok = strtok(str, " ");
while (tok)
{
result = lappend(result, pstrdup(tok));
tok = strtok(NULL, " ");
}
return result;
}
/*
* String comparison function for list sorting.
*/
static int
list_string_cmp(const ListCell *a, const ListCell *b)
{
const char *sa = (const char *) lfirst(a);
const char *sb = (const char *) lfirst(b);
return strcmp(sa, sb);
}
/*
* Checks whether all required scopes are present in the granted scopes.
* Lists are sorted beforehand for easier comparison.
*
* Returns true if all required scopes are found in granted scopes.
*/
bool
check_scopes(List *granted, List *required)
{
ListCell *gcell;
ListCell *rcell;
/* Sort both lists to simplify comparison */
list_sort(granted, list_string_cmp);
list_sort(required, list_string_cmp);
gcell = list_head(granted);
rcell = list_head(required);
while (rcell != NULL && gcell != NULL)
{
char *r = (char *) lfirst(rcell);
char *g = (char *) lfirst(gcell);
int cmp = strcmp(r, g);
if (cmp == 0)
{
/* Match found --- move to the next required element */
rcell = lnext(required, rcell);
gcell = lnext(granted, gcell);
}
else if (cmp > 0)
{
/* granted is behind --- move to the next granted element */
gcell = lnext(granted, gcell);
}
else
{
/* required element not found in granted --- return false */
return false;
}
}
/* If not all required elements were found --- error */
if (rcell != NULL)
return false;
return true;
}
Makefile
# contrib/oauth_validator/Makefile
PGFILEDESC = "oauth_validator - OAuth validator"
MODULE_big = oauth_validator
OBJS =\
$(WIN32RES)\
oauth_validator.o\
token_utils.o
PG_CPPFLAGS += -I$(top_srcdir)/src/common
PG_CPPFLAGS += -I$(libpq_srcdir)
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
Callbacks
startup_cb callback
The startup_cb callback is executed immediately after the module is loaded. It can be used to configure the local state and perform additional initialization if required. If the validator has a state, it can use the state->private_data field to store it.
typedef void (*ValidatorStartupCB) (ValidatorModuleState *state);
ValidatorStartupCB startup_cb;
validate_cb callback
The validate_cb callback is executed when the user tries to pass authorization using OAuth. Any state set in previous calls will be available in state->private_data.
typedef bool (*ValidatorValidateCB) (const ValidatorModuleState *state,
const char *token, const char *role,
ValidatorModuleResult *result);
ValidatorValidateCB validate_cb;
The argument token will contain a carrier token for verification. PostgreSQL made sure that the token was formed syntactically correct, but no other check was carried out. The role parameter contains the role on behalf of which the user requested login. The callback should set the output parameters in the resulting structure, which is defined as:
typedef struct ValidatorModuleResult
{
bool authorized;
char *authn_id;
} ValidatorModuleResult;
The connection will be established only if the validator sets the result->authorized parameter to true. To authenticate a user under an authenticated username (i.e. a specific one using a token), memory must be allocated (using the palloc function ), and a pointer to this memory region must be assigned to the result->authn_id field. Alternatively, the result->authn_id parameter can be set to NULL if the token is valid but the associated user ID cannot be determined.
If the token verification fails , the validator must return false due to incorrect token format, lack of necessary user rights, or other error, then any parameters from the result argument are ignored and the connection is interrupted. In case of successful verification of the token, the validator must return true.
The behavior after returning from validate_cb depends on the specific HBA setting. Usually, the user name result->authn_id must correspond exactly to the role under which the user logs in (this behavior can be changed using the user's card). But when authenticating according to the HBA rule with delegate_ident_mapping enabled, PostgreSQL will not perform any checks on the result->authn_id value at all; in this case, the validator must ensure that the token has sufficient privileges so that the user can log in under the specified role.
Shutdown_cb callback
The shutdown_cb callback is executed when there is a server process associated with the connection. If the validator has any allocated state, this callback should release it to avoid resource leakage.
typedef void (*ValidatorShutdownCB) (ValidatorModuleState *state);
ValidatorShutdownCB shutdown_cb;
Authorization process
Logging
Let's start by configuring logging. To see what requests PostgreSQL sends and what responses it receives, query logging can be enabled in postgresql.conf:
log_connections = on
Examples of logs will be presented below, prefixes will appear at the beginning of the lines, meaning the following:
[libcurl] * Trying 192.168.0.156:8080...
[libcurl] * Connected to 192.168.0.156 (192.168.0.156) port 8080 (#0)
[libcurl] > GET /realms/postgres-realm/.well-known/openid-configuration HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] >
[libcurl] < HTTP/1.1 200 OK
[libcurl] < content-length: 6638
[libcurl] < Cache-Control: no-cache, must-revalidate, no-transform, no-store
[libcurl] < Content-Type: application/json;charset=UTF-8
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"issuer":"","authorization_endpoint":"","token_endpoint":"","introspection_endpoint":"","userinfo_endpoint":"","end_session_endpoint":"","frontchannel_logout_session_supported":true,"frontchannel_logout_supported":true,"jwks_uri":"","check_session_iframe":"","grant\_types\_supported":["authorization\_code","client\_credentials","implicit","password","refresh\_token","urn:ietf:params:oauth:grant-type:device\_code","urn:ietf:params:oauth:grant-type:token-exchange","urn:ietf:params:oauth:grant-type:uma-ticket","urn:openid:params:grant-type:ciba"],"acr\_values\_supported":["0","1"],"response\_types\_supported":["code","none","id\_token","token","id\_token token","code id_token","code token","code id_token token"],"subject_types_supported":["public","pairwise"],"prompt_values_supported":["none","login","consent"],"id_token_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"id_token_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"id_token_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"userinfo_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512","none"],"userinfo_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"userinfo_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"request_object_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512","none"],"request_object_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"request_object_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"response_modes_supported":["query","fragment","form_post","query.jwt","fragment.jwt","form_post.jwt","jwt"],"registration_endpoint":"","token_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"token_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"introspection_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"introspection_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"authorization_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"authorization_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"authorization_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"claims_supported":["aud","sub","iss","auth_time","name","given_name","family_name","preferred_username","email","acr"],"claim_types_supported":["normal"],"claims_parameter_supported":true,"scopes_supported":["openid","offline_access","organization","service_account","postgres","address","phone","acr","profile","microprofile-jwt","web-origins","roles","basic","email"],"request_parameter_supported":true,"request_uri_parameter_supported":true,"require_request_uri_registration":true,"code_challenge_methods_supported":["plain","S256"],"tls_client_certificate_bound_access_tokens":true,"revocation_endpoint":"","revocation_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"revocation_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"backchannel_logout_supported":true,"backchannel_logout_session_supported":true,"device_authorization_endpoint":"","backchannel_token_delivery_modes_supported":["poll","ping"],"backchannel_authentication_endpoint":"","backchannel_authentication_request_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","ES256","RS256","ES512","PS256","PS512","RS512"],"require_pushed_authorization_requests":false,"pushed_authorization_request_endpoint":"","mtls_endpoint_aliases":{"token_endpoint":"","revocation_endpoint":"","introspection_endpoint":"","device_authorization_endpoint":"","registration_endpoint":"","userinfo_endpoint":"","pushed_authorization_request_endpoint":"","backchannel_authentication_endpoint":""},"authorization\_response\_iss\_parameter\_supported":true}
auth device
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/auth/device HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 47
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > scope=openid+postgres&client_id=postgres-client
[libcurl] < HTTP/1.1 200 OK
[libcurl] < Cache-Control: no-store, must-revalidate, max-age=0
[libcurl] < content-length: 296
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"device_code":"tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY","user_code":"WXAI-ZNVY","verification_uri":"","verification_uri_complete":"","expires\_in":600,"interval":5}
token
The client is awaiting approval of the user's authorization request:
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/token HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 147
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > device_code=tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY&grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Adevice_code&client_id=postgres-client
[libcurl] < HTTP/1.1 400 Bad Request
[libcurl] < Cache-Control: no-store
[libcurl] < Pragma: no-cache
[libcurl] < content-length: 98
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"error":"authorization_pending","error_description":"The authorization request is still pending"}
The user is approved:
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/token HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 147
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > device_code=tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY&grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Adevice_code&client_id=postgres-client
[libcurl] < HTTP/1.1 200 OK
[libcurl] < Cache-Control: no-store
[libcurl] < Pragma: no-cache
[libcurl] < content-length: 3307
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"access_token":"eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICIwb1RQSV85LXVIQWJ5Q0t3M2luTm5MTW5hU21hc05nWS1OaDVkTzJ4X0lNIn0.eyJleHAiOjE3NDYwMzEyNTMsImlhdCI6MTc0NjAzMDk1MywiYXV0aF90aW1lIjoxNzQ2MDMwOTUyLCJqdGkiOiJvbnJ0ZGc6MWU3MmRlNGUtNTRhYi00OTZjLWIxYWYtY2FhYmRiYzJlYzExIiwiaXNzIjoiaHR0cDovLzE5Mi4xNjguMC4xNTY6ODA4MC9yZWFsbXMvcG9zdGdyZXMtcmVhbG0iLCJhdWQiOiJhY2NvdW50Iiwic3ViIjoiMGZjNzJiNmYtNjIyMS00ZWQ4LWE5MTYtMDY5ZTdhMDgxZDE0IiwidHlwIjoiQmVhcmVyIiwiYXpwIjoicG9zdGdyZXMtY2xpZW50Iiwic2lkIjoiNjRlMDUzMTMtM2UyMi00MmNjLWE0YmItOTAxODU2YTFhYjMzIiwiYWxsb3dlZC1vcmlnaW5zIjpbIi8qIl0sInJlYWxtX2FjY2VzcyI6eyJyb2xlcyI6WyJvZmZsaW5lX2FjY2VzcyIsImRlZmF1bHQtcm9sZXMtcG9zdGdyZXMtcmVhbG0iLCJ1bWFfYXV0aG9yaXphdGlvbiJdfSwicmVzb3VyY2VfYWNjZXNzIjp7ImFjY291bnQiOnsicm9sZXMiOlsibWFuYWdlLWFjY291bnQiLCJtYW5hZ2UtYWNjb3VudC1saW5rcyIsInZpZXctcHJvZmlsZSJdfX0sInNjb3BlIjoib3BlbmlkIHByb2ZpbGUgcG9zdGdyZXMiLCJuYW1lIjoiYWxpY2UgcG9zdGdyZXMiLCJwcmVmZXJyZWRfdXNlcm5hbWUiOiJhbGljZSIsImdpdmVuX25hbWUiOiJhbGljZSIsImZhbWlseV9uYW1lIjoicG9zdGdyZXMifQ.RXMszI-snIdrXyyTw74U8QXQeDG3zpfV4OvxYuJQvsb86eauXkKHAH35GfEm3XvQbtmpdSdfs1S4i11d69dUjpVTgPpzx6G7IXCXj2NTowzZuyuvdnLxPi1aXdxXqOKNSLSj5PXhGIaZhWsn2sR8dAJ0jjWTUO_lh8qJuJYaDcFulWn_flHVGQYzMZ5PTneRadg8h_1dWp4HSr6yC74NmF94dnOBmytivM4a__Wcq6TkZ3KLn_gafqnn72HpWY0WRwyZdQuzc5o8mE3UUAoKukxMnwDG7Yhxif2Ya5aCloMbL9aDghbMypahl3MiJHHx3j50FavSRm0FJa3zK9w","expires_in":300,"refresh_expires_in":1800,"refresh_token":"eyJhbGciOiJIUzUxMiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICJjOGNiNjY3Ni00OTAxLTRmNjItOTI0OS1kMzY2MWI5Mjg3OTIifQ.eyJleHAiOjE3NDYwMzI3NTMsImlhdCI6MTc0NjAzMDk1MywianRpIjoiZTJkNzkzODUtNjBhZS00MTIwLWIwODAtNjVmYWU4ZmNhYzIzIiwiaXNzIjoiaHR0cDovLzE5Mi4xNjguMC4xNTY6ODA4MC9yZWFsbXMvcG9zdGdyZXMtcmVhbG0iLCJhdWQiOiJodHRwOi8vMTkyLjE2OC4wLjE1Njo4MDgwL3JlYWxtcy9wb3N0Z3Jlcy1yZWFsbSIsInN1YiI6IjBmYzcyYjZmLTYyMjEtNGVkOC1hOTE2LTA2OWU3YTA4MWQxNCIsInR5cCI6IlJlZnJlc2giLCJhenAiOiJwb3N0Z3Jlcy1jbGllbnQiLCJzaWQiOiI2NGUwNTMxMy0zZTIyLTQyY2MtYTRiYi05MDE4NTZhMWFiMzMiLCJzY29wZSI6Im9wZW5pZCBwcm9maWxlIHdlYi1vcmlnaW5zIHBvc3RncmVzIHJvbGVzIGJhc2ljIn0.43pRSq4PBO7ZY86jt8dIL7xZJylntY_CZXllcRfwfh41IRCOft6iqIWdJQp7TJv_JIDI-_-QeOSf1EC_wzABNg","token_type":"Bearer","id_token":"eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICIwb1RQSV85LXVIQWJ5Q0t3M2luTm5MTW5hU21hc05nWS1OaDVkTzJ4X0lNIn0.eyJleHAiOjE3NDYwMzEyNTMsImlhdCI6MTc0NjAzMDk1MywiYXV0aF90aW1lIjoxNzQ2MDMwOTUyLCJqdGkiOiIzNWM3ZDAyZC05OGFjLTQzMTgtOTg3NC0zYzY0Mjg5NjFhMjgiLCJpc3MiOiJodHRwOi8vMTkyLjE2OC4wLjE1Njo4MDgwL3JlYWxtcy9wb3N0Z3Jlcy1yZWFsbSIsImF1ZCI6InBvc3RncmVzLWNsaWVudCIsInN1YiI6IjBmYzcyYjZmLTYyMjEtNGVkOC1hOTE2LTA2OWU3YTA4MWQxNCIsInR5cCI6IklEIiwiYXpwIjoicG9zdGdyZXMtY2xpZW50Iiwic2lkIjoiNjRlMDUzMTMtM2UyMi00MmNjLWE0YmItOTAxODU2YTFhYjMzIiwiYXRfaGFzaCI6ImphOXRKZ1E0VkVPTTNBZGc0VWJBVGciLCJuYW1lIjoiYWxpY2UgcG9zdGdyZXMiLCJwcmVmZXJyZWRfdXNlcm5hbWUiOiJhbGljZSIsImdpdmVuX25hbWUiOiJhbGljZSIsImZhbWlseV9uYW1lIjoicG9zdGdyZXMifQ.dH5hM21-ygBiCXoA9NVOou-L3esUVJUFFUmt_1fU0jc9al4Lk7EUN-cqHicHZPD48HhkIWMJK7WZjxnZLDlkG7ORGdDKPGccMU4-sGsRuVu-GDuN5kHAh_NJZsLBXz9UkpNBkd8ROxK3-fbmyYdwsuwNeg6KNhSOj0FxEnxLc0-HrjEE92P7hzq0PD29oY2jhRKcqpbtknMwxFkkMBi8xPgdpuyTmLtJD3-xxYuwMKP7WUGzwzVAFqfrhFm5O5dJxeld5fTFE4Kyl9fR24JcjtfxBeIHVJqLiQkl9Et_KNGiFoXDG4Xwcc7eIUaBnauhY5_froYvKS8NbQxCOUg","not-before-policy":0,"session_state":"64e05313-3e22-42cc-a4bb-901856a1ab33","scope":"openid profile postgres"}
Authorization via psql
For testing purposes, we will allow authorization over the unsecured http protocol (when using https, you will need to configure certificate chains):
export PGOAUTHDEBUG="UNSAFE"
Let's launch psql and set the connection details:
psql "user=alice dbname=postgres oauth_issuer= oauth_client_id=postgres-client oauth_client_secret=YYi8LqfzHRMnqUUlptpWVC2k7eWNrqjX"
We will see the URL and the code that you will need to enter after clicking on this URL:
Visit and enter the code: LJDW-RURX
We enter it in the browser [
Enter the code and click Submit.
Next, we see the user login window:
Enter the username and password (see ) and click on Sign In.
Next, he asks if we are ready to provide (send) such information to PostgreSQL. Click.
Successfully logged in...
... and we can enter commands in the terminal:
psql (18devel) Type "help" for help.
postgres=>
As a result, we successfully logged in to PostgreSQL.
Conclusion
Integration of OAuth 2.0 Device Authorization Flow, introduced in the PostgreSQL database management system 18 and Tantor Postgres 17.5.0, allows the use of the SSO (single sign-on) mechanism. Implementation of centralized access control via providers like Keycloak increase the level of protection, eliminating the risks of password interception, and optimize administration in distributed environments. The article provides a step-by-step guide from setting up Keycloak and PostgreSQL configurations before implementing the token validator and successful authorization via psql.
Introduction
Support for authorization via is a modern and secure way of providing access, introduced in PostgreSQL 18. This authorization method allows applications to request access to PostgreSQL on behalf of the user through an external identification and access control provider, such as , which is especially convenient for cloud environments and microservices architecture. Support for this functionality in Tantor Postgres has been implemented since version 17.5.0, introduced in June 2025.
Unlike password authentication (password, md5, SCRAM), OAuth allows you to centralize account management and security policies in a single identity and access control provider. Device Authorization Flow is ideal for scenarios where the client side is limited or absent (for example, terminal applications, automated services). The user confirms access on a separate device via a browser or a mobile application. This makes the authorization procedure more secure, since it is impossible to intercept the password on the client side.
In this article, we will take a step-by-step look at configuring OAuth authorization in PostgreSQL using Keycloak: configure Keycloak, prepare PostgreSQL, write an OAuth token validator in PostgreSQL, and verify successful authorization via psql using Device Flow. In other words, let's go through the following scheme for configuring an OAuth connection in PostgreSQL:
Keycloak setup by a security engineer
Keycloak is an open source identification and access control system that allows you to manage user identification, control access to applications and data, providing a single point of entry (SSO). Keycloak makes it easier to set up access, restore passwords, edit profiles, and send out one-time passwords, eliminating the need for developers to create additional login forms. With Keycloak, these processes can be integrated with just a few mouse clicks.
Keycloak launch
Let's launch Keycloak using a Docker image. We will also open port 8080 and create an initial admin user with the username admin and password admin. Using the --name option, we will set the name of the container to be created as keycloak.
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-dev
Next, enter the address in the browser , the Keycloak admin panel will open and ask for a username and password. Let's enter admin, admin.
The security engineer sets up user access rights in Keycloak in the following order:
- Creating a Realm
- Creating Users
- Creating a Client scope
- Creating Clients
Let's take a closer look at each stage.
Creating a Realm
Realm is a security configuration area that includes user accounts, roles, groups, and authorization settings. To create it, click Manage realms in the upper-left corner.
You will see the Manage realms page:
Next, click on Create realm, and in the dialog box that appears, enter "postgres-realm" in the Realm name field.
After clicking on Create, our realm will be created and will become the current one.
Creating Users
Users are entities that can log in to the system. They can have associated attributes such as email, username, address, phone number, and birthday. To open the User creation window, click on the Users tab in the left panel:
Next, click Create new user, a window will appear with the data entry for the new user. In the Username field, enter the name "alice" and fill in the fields Email, First name, and Last name.
Click the Create button, a new user window will appear. You will need an ID to the DB administrator for mapping the Keycloak and PostgreSQL user in the pg_ident.conf file.
Switch to the Credentials tab and click on Set password to set the password:
Enter the password "alice". Temporary switch (temporary password) is set to the off position, otherwise, at the first login, the system will require the user to set a new password.
Click Save, then in the dialog box that appears click Save password:
The password is set:
Creating Client scopes
The Client scope is a way to limit the access rights that are declared in tokens. It allows the client to request only the roles they need, which makes tokens more secure and manageable.
Switch to the Client scopes tab:
Click on the Create client scope button, a window for creating a scope will open. Enter the "postgres" value in the Name field, select the Default type, activate Include in token scope, and save by clicking Save.
Creating a Client
Clients are 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 that appears, enter "postgres-client" in the Client ID field. Then click Next.
In the Capability config window that appears:
Turn on Client authentication (On position);
Disable Standard flow, because we do not use Authorization Code Flow;
Enable the OAuth 2.0 Device Authorization Grant;
Click Next.
We don't change anything in the Login settings window, just click Save.
The client we've created opens:
Next, go to the Client scopes tab and check that:
"postgres" is present (in the picture at the very bottom) and the Default type is set for it;
"basic" also has the Default type.
The rest of the scopes are not important for our example, you can leave everything as it is.
The Credentials tab contains the Client Secret, which we will need to enter in the terminal when logging into PostgreSQL (see below in the "Authorization via psql" section)
Configuring PostgreSQL by a database administrator
The possibility of OAuth operation presupposes the appropriate configuration of PostgreSQL:
Creating a user in PostgreSQL;
Configure the parameters in the postgresql.conf file;
Configure the parameters in the pg_ident.conf file in the case of mapping users through it between Keycloak and PostgreSQL. If the mapping occurs in the validator that wrote developer, you don't need to configure it.;
Configure the parameters in the pg_hba.conf file.
A role is an entity that can own objects and have certain rights in the database. A role can represent a user, a group, or both, depending on the use case.
Create a role and give it the right to connect to the database:
CREATE ROLE alice;
ALTER ROLE alice WITH LOGIN;
Configuring the postgresql.conf file
In the oauth_validator_libraries parameter we will set the name of the validator that will verify the token (see the section ).
oauth_validator_libraries = 'oauth_validator'
If only one verification library is provided, it will be used by default for all OAuth connections; otherwise, all entries of oauth HBA must explicitly set the verification tool selected from this list. If an empty string value is set (by default), OAuth connections will be denied.
User mapping between Keycloak and PostgreSQL
There are two ways to map users:
via the pg_ident.conf file - this is configured by the database administrator;
using a validator -- the developer adds this mapping to the validator.
Configure the display of Keycloak user IDs and databases:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
oauthmap "0fc72b6f-6221-4ed8-a916-069e7a081d14" "alice"
The name of the mapping is indicated in the first column, and the user ID from Keycloak is indicated in the second column (see ), in the third is the name of the role in PostgreSQL.
Mapping users through a validator
It is described below in the section .
Configuring the pg_hba.conf file
Setting up the client's login to the database:
# TYPE DATABASE USER ADDRESS METHOD local all all oauth issuer="" scope="openid postgres" map="oauthmap"
In the fourth field, set oauth and then its parameters. In the issuer parameter, set the URL of the discovery service . In the Scope parameter, we set the access areas that will be requested from Keycloak for the client.
Next, you need to set the algorithm for mapping users between Keycloak and PostgreSQL (see "").
Mapping users via pg_ident.conf
We add the map parameter, in which we set the map id from the pg_ident.conf file, we have this "oauthmap":
# TYPE DATABASE USER ADDRESS METHOD local all all oauth
issuer="" scope="openid postgres" map="oauthmap"
Mapping users through a validator
In this case, the delegate_ident_mapping=1 parameter should be set instead of the map parameter.
# TYPE DATABASE USER ADDRESS METHOD local all all oauth
issuer="" scope="openid postgres" delegate_ident_mapping=1
The delegate_ident_mapping parameter has a higher priority than map, so if the map parameter is also specified with delegate_ident_mapping=1, it will be ignored and user mapping will go through the validator.
Writing a validator by the developer
OAuth authentication modules implement their functionality by defining a set of callbacks. The server will call them as needed to process the authorization request from the user.
Implementation of the token validator
When mapping users between Keycloak and PostgreSQL, the implementation via pg_ident.conf differs from the mapping via the validator only by the implementation of the get_user function. In the example below, the mapping is implemented using pg_ident.conf. Token verification It consists in verifying that the required scope specified in pg_hba.conf is present in the scope field in the token received from the server. Upon successful verification, the user ID from the sub token field is assigned res->authn_id.
The main verification logic is implemented in the validate_token function. The general scheme of its work:
- Token content analysis.
The source string of the token is analyzed to extract its contents (payload). If the token has an incorrect format or the contents cannot be extracted, the verification fails.
- Extracting the sub and scope fields from the JWT token.
The token content must include both fields:
sub (Subject) - user ID
scope -- a list of permissions (Scopes) provided by the token, separated by spaces
If any of these fields are missing, the verification is considered failed.
- Purpose of the identifier.
The sub value is assigned to the res->authn_id field, which PostgreSQL uses to identify the user. This value is then compared with the entries in pg_ident.conf to determine the actual role in the database that the user can use.
- Comparison of permissions (scopes).
The permissions granted by the token are compared with those required by the corresponding entry in pg_hba.conf (from oauth_scope). If all required permissions are present in the token, the verification is considered successful.
- Setting the authorization result.
The res->authorized flag is set to true if the permissions match, otherwise false.
- Identification mapping.
The sub value is then mapped (outside of this module) to the entries in pg_ident.conf to determine the actual role in the database that the user can use.
Let's start writing our training validator (its sources are also posted on ). First, let's create the oauth_validator folder, and in it we'll create the oauth_validator.c file
#include <string.h>
#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;
}
token_utils.h/.c -- utility functions
#ifndef TOKEN_UTILS_H
#define TOKEN_UTILS_H
#include <stdbool.h>
#include "common/jsonapi.h"
#include "nodes/pg_list.h"
const char* parse_token_payload(const char *token);
void extract_sub_scope_fields(const char *json, char **sub_field, char **scope_field);
const char *decode_base64(const char *b64);
char *base64url_to_base64(const char *b64url);
List *split_scopes(const char *raw);
bool check_scopes(List *granted, List *required);
#endif
#include "postgres.h"
#include "token_utils.h"
#include "common/base64.h"
#include "mb/pg_wchar.h"
#define SUB_FIELD 0 /* Index for 'sub' field */
#define SCOPE_FIELD 1 /* Index for 'scope' field */
/*
* JSON object field handler.
* Marks that the currently processed field is 'sub' or 'scope'
* to store its value at the next processing stage.
*/
static JsonParseErrorType
token_field_start(void *state, char *fname, bool isnull)
{
char **fields = (char **) state;
if (strcmp(fname, "sub") == 0)
fields[SUB_FIELD] = (char *) 1; /* Mark that we are processing 'sub' field */
else if (strcmp(fname, "scope") == 0)
fields[SCOPE_FIELD] = (char *) 1; /* Mark that we are processing 'scope' field */
return JSON_SUCCESS;
}
/*
* JSON scalar value handler.
* Stores the value of 'sub' or 'scope' if it was marked earlier.
*/
static JsonParseErrorType
token_scalar(void *state, char *token, JsonTokenType tokentype)
{
char **fields = (char **) state;
if (fields[SUB_FIELD] == (char *) 1)
fields[SUB_FIELD] = pstrdup(token); /* Save the value of 'sub' */
else if (fields[SCOPE_FIELD] == (char *) 1)
fields[SCOPE_FIELD] = pstrdup(token); /* Save the value of 'scope' */
return JSON_SUCCESS;
}
/*
* Extracts 'sub' and 'scope' fields from a JSON string.
*
* Parameters:
* - json: JSON string
* - sub_field: returns the value of 'sub' field
* - scope_field: returns the value of 'scope' field
*/
void
extract_sub_scope_fields(const char *json, char **sub_field, char **scope_field)
{
JsonLexContext lex;
JsonSemAction sem;
char **fields = palloc0(sizeof(char *) * 2); /* Allocate memory for 2 strings ('sub', 'scope') */
*sub_field = NULL;
*scope_field = NULL;
/* Create a lexical context for JSON parsing */
makeJsonLexContextCstringLen(&lex, json, strlen(json), GetDatabaseEncoding(), true);
/* Set up JSON parser handlers */
memset(&sem, 0, sizeof(sem));
sem.semstate = (void *) fields;
sem.object_field_start = token_field_start;
sem.scalar = token_scalar;
/* Start JSON parsing */
pg_parse_json(&lex, &sem);
/* Return the found values */
*sub_field = fields[SUB_FIELD];
*scope_field = fields[SCOPE_FIELD];
}
/*
* Extracts the payload from a JWT token.
* Returns the decoded payload string in JSON format.
*/
const char*
parse_token_payload(const char *token)
{
char *dot1 = NULL;
char *dot2 = NULL;
int payload_len = 0;
char *payload_b64url = NULL;
char *b64 = NULL;
if(!token)
return NULL;
/* Find the first and second dots in JWT (separators for header.payload.signature) */
dot1 = strchr(token, '.');
dot2 = dot1 ? strchr(dot1 + 1, '.') : NULL;
if (!dot1 || !dot2)
{
elog(LOG, "Invalid token format, two dots required: %s", token);
return NULL;
}
/* Extract the encoded payload between the dots */
payload_len = dot2 - (dot1 + 1);
payload_b64url = pnstrdup(dot1 + 1, payload_len);
/* Convert base64url to regular base64 */
b64 = base64url_to_base64(payload_b64url);
/* Decode base64 to JSON string */
return decode_base64(b64);
}
/*
* Converts a base64url string to base64 format.
* Replaces '-' with '+', '_' with '/' and adds padding '=' if necessary.
*/
char *
base64url_to_base64(const char *b64url)
{
int len = strlen(b64url);
int pad = (4 - (len % 4)) % 4; /* Determine the number of '=' padding characters */
char *b64 = palloc(len + pad + 1);
for (int i = 0; i < len; i++)
{
if (b64url == '-')
b64 = '+';
else if (b64url == '_')
b64 = '/';
else
b64 = b64url;
}
/* Add padding '=' */
for (int i = 0; i < pad; i++)
b64[len + i] = '=';
b64[len + pad] = '\0';
return b64;
}
/*
* Decodes a base64 string into a regular string.
* Returns the decoded string or NULL in case of error.
*/
const char *
decode_base64(const char *b64)
{
int encoded_len = strlen(b64);
int max_decoded_len = pg_b64_dec_len(encoded_len); /* Calculate required buffer length */
char *decoded = palloc(max_decoded_len + 1);
int decoded_len = pg_b64_decode(b64, encoded_len, decoded, max_decoded_len);
if (decoded_len <= 0)
{
elog(LOG, "Invalid token format: base64 decoding error");
return NULL;
}
decoded[decoded_len] = '\0';
return decoded;
}
/*
* Splits a space-separated string (e.g., scope list from token) into a List of strings.
*/
List *
split_scopes(const char *raw)
{
List *result = NIL;
char *str = pstrdup(raw); /* Make a copy of the string because strtok modifies it */
char *tok = strtok(str, " ");
while (tok)
{
result = lappend(result, pstrdup(tok));
tok = strtok(NULL, " ");
}
return result;
}
/*
* String comparison function for list sorting.
*/
static int
list_string_cmp(const ListCell *a, const ListCell *b)
{
const char *sa = (const char *) lfirst(a);
const char *sb = (const char *) lfirst(b);
return strcmp(sa, sb);
}
/*
* Checks whether all required scopes are present in the granted scopes.
* Lists are sorted beforehand for easier comparison.
*
* Returns true if all required scopes are found in granted scopes.
*/
bool
check_scopes(List *granted, List *required)
{
ListCell *gcell;
ListCell *rcell;
/* Sort both lists to simplify comparison */
list_sort(granted, list_string_cmp);
list_sort(required, list_string_cmp);
gcell = list_head(granted);
rcell = list_head(required);
while (rcell != NULL && gcell != NULL)
{
char *r = (char *) lfirst(rcell);
char *g = (char *) lfirst(gcell);
int cmp = strcmp(r, g);
if (cmp == 0)
{
/* Match found --- move to the next required element */
rcell = lnext(required, rcell);
gcell = lnext(granted, gcell);
}
else if (cmp > 0)
{
/* granted is behind --- move to the next granted element */
gcell = lnext(granted, gcell);
}
else
{
/* required element not found in granted --- return false */
return false;
}
}
/* If not all required elements were found --- error */
if (rcell != NULL)
return false;
return true;
}
Makefile
# contrib/oauth_validator/Makefile
PGFILEDESC = "oauth_validator - OAuth validator"
MODULE_big = oauth_validator
OBJS =\
$(WIN32RES)\
oauth_validator.o\
token_utils.o
PG_CPPFLAGS += -I$(top_srcdir)/src/common
PG_CPPFLAGS += -I$(libpq_srcdir)
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
Callbacks
startup_cb callback
The startup_cb callback is executed immediately after the module is loaded. It can be used to configure the local state and perform additional initialization if required. If the validator has a state, it can use the state->private_data field to store it.
typedef void (*ValidatorStartupCB) (ValidatorModuleState *state);
ValidatorStartupCB startup_cb;
validate_cb callback
The validate_cb callback is executed when the user tries to pass authorization using OAuth. Any state set in previous calls will be available in state->private_data.
typedef bool (*ValidatorValidateCB) (const ValidatorModuleState *state,
const char *token, const char *role,
ValidatorModuleResult *result);
ValidatorValidateCB validate_cb;
The argument token will contain a carrier token for verification. PostgreSQL made sure that the token was formed syntactically correct, but no other check was carried out. The role parameter contains the role on behalf of which the user requested login. The callback should set the output parameters in the resulting structure, which is defined as:
typedef struct ValidatorModuleResult
{
bool authorized;
char *authn_id;
} ValidatorModuleResult;
The connection will be established only if the validator sets the result->authorized parameter to true. To authenticate a user under an authenticated username (i.e. a specific one using a token), memory must be allocated (using the palloc function ), and a pointer to this memory region must be assigned to the result->authn_id field. Alternatively, the result->authn_id parameter can be set to NULL if the token is valid but the associated user ID cannot be determined.
If the token verification fails , the validator must return false due to incorrect token format, lack of necessary user rights, or other error, then any parameters from the result argument are ignored and the connection is interrupted. In case of successful verification of the token, the validator must return true.
The behavior after returning from validate_cb depends on the specific HBA setting. Usually, the user name result->authn_id must correspond exactly to the role under which the user logs in (this behavior can be changed using the user's card). But when authenticating according to the HBA rule with delegate_ident_mapping enabled, PostgreSQL will not perform any checks on the result->authn_id value at all; in this case, the validator must ensure that the token has sufficient privileges so that the user can log in under the specified role.
Shutdown_cb callback
The shutdown_cb callback is executed when there is a server process associated with the connection. If the validator has any allocated state, this callback should release it to avoid resource leakage.
typedef void (*ValidatorShutdownCB) (ValidatorModuleState *state);
ValidatorShutdownCB shutdown_cb;
Authorization process
Logging
Let's start by configuring logging. To see what requests PostgreSQL sends and what responses it receives, query logging can be enabled in postgresql.conf:
log_connections = on
Examples of logs will be presented below, prefixes will appear at the beginning of the lines, meaning the following:
the prefix ">" means a Keycloak request.
the prefix "<" means the Keycloak response.
[libcurl] * Trying 192.168.0.156:8080...
[libcurl] * Connected to 192.168.0.156 (192.168.0.156) port 8080 (#0)
[libcurl] > GET /realms/postgres-realm/.well-known/openid-configuration HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] >
[libcurl] < HTTP/1.1 200 OK
[libcurl] < content-length: 6638
[libcurl] < Cache-Control: no-cache, must-revalidate, no-transform, no-store
[libcurl] < Content-Type: application/json;charset=UTF-8
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"issuer":"","authorization_endpoint":"","token_endpoint":"","introspection_endpoint":"","userinfo_endpoint":"","end_session_endpoint":"","frontchannel_logout_session_supported":true,"frontchannel_logout_supported":true,"jwks_uri":"","check_session_iframe":"","grant\_types\_supported":["authorization\_code","client\_credentials","implicit","password","refresh\_token","urn:ietf:params:oauth:grant-type:device\_code","urn:ietf:params:oauth:grant-type:token-exchange","urn:ietf:params:oauth:grant-type:uma-ticket","urn:openid:params:grant-type:ciba"],"acr\_values\_supported":["0","1"],"response\_types\_supported":["code","none","id\_token","token","id\_token token","code id_token","code token","code id_token token"],"subject_types_supported":["public","pairwise"],"prompt_values_supported":["none","login","consent"],"id_token_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"id_token_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"id_token_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"userinfo_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512","none"],"userinfo_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"userinfo_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"request_object_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512","none"],"request_object_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"request_object_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"response_modes_supported":["query","fragment","form_post","query.jwt","fragment.jwt","form_post.jwt","jwt"],"registration_endpoint":"","token_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"token_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"introspection_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"introspection_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"authorization_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"authorization_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"authorization_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"claims_supported":["aud","sub","iss","auth_time","name","given_name","family_name","preferred_username","email","acr"],"claim_types_supported":["normal"],"claims_parameter_supported":true,"scopes_supported":["openid","offline_access","organization","service_account","postgres","address","phone","acr","profile","microprofile-jwt","web-origins","roles","basic","email"],"request_parameter_supported":true,"request_uri_parameter_supported":true,"require_request_uri_registration":true,"code_challenge_methods_supported":["plain","S256"],"tls_client_certificate_bound_access_tokens":true,"revocation_endpoint":"","revocation_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"revocation_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"backchannel_logout_supported":true,"backchannel_logout_session_supported":true,"device_authorization_endpoint":"","backchannel_token_delivery_modes_supported":["poll","ping"],"backchannel_authentication_endpoint":"","backchannel_authentication_request_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","ES256","RS256","ES512","PS256","PS512","RS512"],"require_pushed_authorization_requests":false,"pushed_authorization_request_endpoint":"","mtls_endpoint_aliases":{"token_endpoint":"","revocation_endpoint":"","introspection_endpoint":"","device_authorization_endpoint":"","registration_endpoint":"","userinfo_endpoint":"","pushed_authorization_request_endpoint":"","backchannel_authentication_endpoint":""},"authorization\_response\_iss\_parameter\_supported":true}
auth device
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/auth/device HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 47
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > scope=openid+postgres&client_id=postgres-client
[libcurl] < HTTP/1.1 200 OK
[libcurl] < Cache-Control: no-store, must-revalidate, max-age=0
[libcurl] < content-length: 296
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"device_code":"tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY","user_code":"WXAI-ZNVY","verification_uri":"","verification_uri_complete":"","expires\_in":600,"interval":5}
token
The client is awaiting approval of the user's authorization request:
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/token HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 147
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > device_code=tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY&grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Adevice_code&client_id=postgres-client
[libcurl] < HTTP/1.1 400 Bad Request
[libcurl] < Cache-Control: no-store
[libcurl] < Pragma: no-cache
[libcurl] < content-length: 98
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"error":"authorization_pending","error_description":"The authorization request is still pending"}
The user is approved:
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/token HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 147
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > device_code=tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY&grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Adevice_code&client_id=postgres-client
[libcurl] < HTTP/1.1 200 OK
[libcurl] < Cache-Control: no-store
[libcurl] < Pragma: no-cache
[libcurl] < content-length: 3307
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"access_token":"eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICIwb1RQSV85LXVIQWJ5Q0t3M2luTm5MTW5hU21hc05nWS1OaDVkTzJ4X0lNIn0.eyJleHAiOjE3NDYwMzEyNTMsImlhdCI6MTc0NjAzMDk1MywiYXV0aF90aW1lIjoxNzQ2MDMwOTUyLCJqdGkiOiJvbnJ0ZGc6MWU3MmRlNGUtNTRhYi00OTZjLWIxYWYtY2FhYmRiYzJlYzExIiwiaXNzIjoiaHR0cDovLzE5Mi4xNjguMC4xNTY6ODA4MC9yZWFsbXMvcG9zdGdyZXMtcmVhbG0iLCJhdWQiOiJhY2NvdW50Iiwic3ViIjoiMGZjNzJiNmYtNjIyMS00ZWQ4LWE5MTYtMDY5ZTdhMDgxZDE0IiwidHlwIjoiQmVhcmVyIiwiYXpwIjoicG9zdGdyZXMtY2xpZW50Iiwic2lkIjoiNjRlMDUzMTMtM2UyMi00MmNjLWE0YmItOTAxODU2YTFhYjMzIiwiYWxsb3dlZC1vcmlnaW5zIjpbIi8qIl0sInJlYWxtX2FjY2VzcyI6eyJyb2xlcyI6WyJvZmZsaW5lX2FjY2VzcyIsImRlZmF1bHQtcm9sZXMtcG9zdGdyZXMtcmVhbG0iLCJ1bWFfYXV0aG9yaXphdGlvbiJdfSwicmVzb3VyY2VfYWNjZXNzIjp7ImFjY291bnQiOnsicm9sZXMiOlsibWFuYWdlLWFjY291bnQiLCJtYW5hZ2UtYWNjb3VudC1saW5rcyIsInZpZXctcHJvZmlsZSJdfX0sInNjb3BlIjoib3BlbmlkIHByb2ZpbGUgcG9zdGdyZXMiLCJuYW1lIjoiYWxpY2UgcG9zdGdyZXMiLCJwcmVmZXJyZWRfdXNlcm5hbWUiOiJhbGljZSIsImdpdmVuX25hbWUiOiJhbGljZSIsImZhbWlseV9uYW1lIjoicG9zdGdyZXMifQ.RXMszI-snIdrXyyTw74U8QXQeDG3zpfV4OvxYuJQvsb86eauXkKHAH35GfEm3XvQbtmpdSdfs1S4i11d69dUjpVTgPpzx6G7IXCXj2NTowzZuyuvdnLxPi1aXdxXqOKNSLSj5PXhGIaZhWsn2sR8dAJ0jjWTUO_lh8qJuJYaDcFulWn_flHVGQYzMZ5PTneRadg8h_1dWp4HSr6yC74NmF94dnOBmytivM4a__Wcq6TkZ3KLn_gafqnn72HpWY0WRwyZdQuzc5o8mE3UUAoKukxMnwDG7Yhxif2Y
a5aCloMbL9aDghbMypahl3MiJHHx3j50FavSRm0FJa3zK9w","expires_in":300,"refresh_expires_in":1800,"refresh_token":"eyJhbGciOiJIUzUxMiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICJjOGNiNjY3Ni00OTAxLTRmNjItOTI0OS1kMzY2MWI5Mjg3OTIifQ.eyJleHAiOjE3NDYwMzI3NTMsImlhdCI6MTc0NjAzMDk1MywianRpIjoiZTJkNzkzODUtNjBhZS00MTIwLWIwODAtNjVmYWU4ZmNhYzIzIiwiaXNzIjoiaHR0cDovLzE5Mi4xNjguMC4xNTY6ODA4MC9yZWFsbXMvcG9zdGdyZXMtcmVhbG0iLCJhdWQiOiJodHRwOi8vMTkyLjE2OC4wLjE1Njo4MDgwL3JlYWxtcy9wb3N0Z3Jlcy1yZWFsbSIsInN1YiI6IjBmYzcyYjZmLTYyMjEtNGVkOC1hOTE2LTA2OWU3YTA4MWQxNCIsInR5cCI6IlJlZnJlc2giLCJhenAiOiJwb3N0Z3Jlcy1jbGllbnQiLCJzaWQiOiI2NGUwNTMxMy0zZTIyLTQyY2MtYTRiYi05MDE4NTZhMWFiMzMiLCJzY29wZSI6Im9wZW5pZCBwcm9maWxlIHdlYi1vcmlnaW5zIHBvc3RncmVzIHJvbGVzIGJhc2ljIn0.43pRSq4PBO7ZY86jt8dIL7xZJylntY_CZXllcRfwfh41IRCOft6iqIWdJQp7TJv_JIDI-_-QeOSf1EC_wzABNg","token_type":"Bearer","id_token":"eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICIwb1RQSV85LXVIQWJ5Q0t3M2luTm5MTW5hU21hc05nWS1OaDVkTzJ4X0lNIn0.eyJleHAiOjE3NDYwMzEyNTMsImlhdCI6MTc0NjAzMDk1MywiYXV0aF90aW1lIjoxNzQ2MDMwOTUyLCJqdGkiOiIzNWM3ZDAyZC05OGFjLTQzMTgtOTg3NC0zYzY0Mjg5NjFhMjgiLCJpc3MiOiJodHRwOi8vMTkyLjE2OC4wLjE1Njo4MDgwL3JlYWxtcy9wb3N0Z3Jlcy1yZWFsbSIsImF1ZCI6InBvc3RncmVzLWNsaWVudCIsInN1YiI6IjBmYzcyYjZmLTYyMjEtNGVkOC1hOTE2LTA2OWU3YTA4MWQxNCIsInR5cCI6IklEIiwiYXpwIjoicG9zdGdyZXMtY2xpZW50Iiwic2lkIjoiNjRlMDUzMTMtM2UyMi00MmNjLWE0YmItOTAxODU2YTFhYjMzIiwiYXRfaGFzaCI6ImphOXRKZ1E0VkVPTTNBZGc0VWJBVGciLCJuYW1lIjoiYWxpY2UgcG9zdGdyZXMiLCJwcmVmZXJyZWRfdXNlcm5hbWUiOiJhbGljZSIsImdpdmVuX25hbWUiOiJhbGljZSIsImZhbWlseV9uYW1lIjoicG9zdGdyZXMifQ.dH5hM21-ygBiCXoA9NVOou-L3esUVJUFFUmt_1fU0jc9al4Lk7EUN-cqHicHZPD48HhkIWMJK7WZjxnZLDlkG7ORGdDKPGccMU4-sGsRuVu-GDuN5kHAh_NJZsLBXz9UkpNBkd8ROxK3-fbmyYdwsuwNeg6KNhSOj0FxEnxLc0-HrjEE92P7hzq0PD29oY2jhRKcqpbtknMwxFkkMBi8xPgdpuyTmLtJD3-xxYuwMKP7WUGzwzVAFqfrhFm5O5dJxeld5fTFE4Kyl9fR24JcjtfxBeIHVJqLiQkl9Et_KNGiFoXDG4Xwcc7eIUaBnauhY5_froYvKS8NbQxCOUg","not-before-policy":0,"session_state":"64e05313-3e22-42cc-a4bb-901856a1ab33","scope":"openid profile postgres"}Authorization via psql
For testing purposes, we will allow authorization over the unsecured http protocol (when using https, you will need to configure certificate chains):
export PGOAUTHDEBUG="UNSAFE"
Let's launch psql and set the connection details:
psql "user=alice dbname=postgres oauth_issuer= oauth_client_id=postgres-client oauth_client_secret=YYi8LqfzHRMnqUUlptpWVC2k7eWNrqjX"
We will see the URL and the code that you will need to enter after clicking on this URL:
Visit and enter the code: LJDW-RURX
We enter it in the browser [
Enter the code and click Submit.
Next, we see the user login window:
Enter the username and password (see ) and click on Sign In.
Next, he asks if we are ready to provide (send) such information to PostgreSQL. Click
.Successfully logged in...
... and we can enter commands in the terminal:
psql (18devel) Type "help" for help.
postgres=>
As a result, we successfully logged in to PostgreSQL.
Conclusion
Integration OAuth 2.0 Device Authorization Flow, introduced in the PostgreSQL database management system 18 and Tantor Postgres 17.5.0, allows the use of the SSO (single sign-on) mechanism. Implementation of centralized access control via providers like Keycloak increase the level of protection, eliminating the risks of password interception, and optimize administration in distributed environments. The article provides a step-by-step guide from setting up Keycloak and PostgreSQL configurations before implementing the token validator and successful authorization via psql.
You will see the Manage realms page:
Next, click on Create realm, and in the dialog box that appears, enter "postgres-realm" in the Realm name field.
After clicking on Create, our realm will be created and will become the current one.
Creating Users
Users are entities that can log in to the system. They can have associated attributes such as email, username, address, phone number, and birthday. To open the User creation window, click on the Users tab in the left panel:
Next, click Create new user, a window will appear with the data entry for the new user. In the Username field, enter the name "alice" and fill in the fields Email, First name, and Last name.
Click the Create button, a new user window will appear. You will need an ID to the DB administrator for mapping the Keycloak and PostgreSQL user in the pg_ident.conf file.
Switch to the Credentials tab and click on Set password to set the password:
Enter the password "alice". Temporary switch (temporary password) is set to the off position, otherwise, at the first login, the system will require the user to set a new password.
Click Save, then in the dialog box that appears click Save password:
The password is set:
Creating Client scopes
The Client scope is a way to limit the access rights that are declared in tokens. It allows the client to request only the roles they need, which makes tokens more secure and manageable.
Switch to the Client scopes tab:
Click on the Create client scope button, a window for creating a scope will open. Enter the "postgres" value in the Name field, select the Default type, activate Include in token scope, and save by clicking Save.
Creating a Client
Clients are 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 that appears, enter "postgres-client" in the Client ID field. Then click Next.
In the Capability config window that appears:
Turn on Client authentication (On position);
Disable Standard flow, because we do not use Authorization Code Flow;
Enable the OAuth 2.0 Device Authorization Grant;
Click Next.
We don't change anything in the Login settings window, just click Save.
The client we've created opens:
Next, go to the Client scopes tab and check that:
"postgres" is present (in the picture at the very bottom) and the Default type is set for it;
"basic" also has the Default type.
The rest of the scopes are not important for our example, you can leave everything as it is.
The Credentials tab contains the Client Secret, which we will need to enter in the terminal when logging into PostgreSQL (see in the "Authorization via psql" section).
Configuring PostgreSQL by a database administrator
The possibility of OAuth operation presupposes the appropriate configuration of PostgreSQL:
Creating a user in PostgreSQL;
Configure the parameters in the postgresql.conf file;
Configure the parameters in the pg_ident.conf file in the case of mapping users through it between Keycloak and PostgreSQL. If the mapping occurs in the validator that wrote developer, you don't need to configure it.;
Configure the parameters in the pg_hba.conf file.
A role is an entity that can own objects and have certain rights in the database. A role can represent a user, a group, or both, depending on the use case.
Create a role and give it the right to connect to the database:
CREATE ROLE alice;
ALTER ROLE alice WITH LOGIN;
Configuring the postgresql.conf file
In the oauth_validator_libraries parameter we will set the name of the validator that will verify the token (see the section ).
oauth_validator_libraries = 'oauth_validator'
If only one verification library is provided, it will be used by default for all OAuth connections; otherwise, all entries of oauth HBA must explicitly set the verification tool selected from this list. If an empty string value is set (by default), OAuth connections will be denied.
User mapping between Keycloak and PostgreSQL
There are two ways to map users:
via the pg_ident.conf file - this is configured by the database administrator;
using a validator -- the developer adds this mapping to the validator.
Configure the display of Keycloak user IDs and databases:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
oauthmap "0fc72b6f-6221-4ed8-a916-069e7a081d14" "alice"
The name of the mapping is indicated in the first column, and the user ID from Keycloak is indicated in the second column (see ), in the third is the name of the role in PostgreSQL.
Mapping users through a validator
It is described below in the section .
Configuring the pg_hba.conf file
Setting up the client's login to the database:
# TYPE DATABASE USER ADDRESS METHOD local all all oauth issuer="" scope="openid postgres" map="oauthmap"
In the fourth field, set oauth and then its parameters. In the issuer parameter, set the URL of the discovery service . In the Scope parameter, we set the access areas that will be requested from Keycloak for the client.
Next, you need to set the algorithm for mapping users between Keycloak and PostgreSQL (see "").
Mapping users via pg_ident.conf
We add the map parameter, in which we set the map id from the pg_ident.conf file, we have this "oauthmap":
# TYPE DATABASE USER ADDRESS METHOD local all all oauth
issuer="" scope="openid postgres" map="oauthmap"
Mapping users through a validator
In this case, the delegate_ident_mapping=1 parameter should be set instead of the map parameter.
# TYPE DATABASE USER ADDRESS METHOD local all all oauth
issuer="" scope="openid postgres" delegate_ident_mapping=1
The delegate_ident_mapping parameter has a higher priority than map, so if the map parameter is also specified with delegate_ident_mapping=1, it will be ignored and user mapping will go through the validator.
Writing a validator by the developer
OAuth authentication modules implement their functionality by defining a set of callbacks. The server will call them as needed to process the authorization request from the user.
Implementation of the token validator
When mapping users between Keycloak and PostgreSQL, the implementation via pg_ident.conf differs from the mapping via the validator only by the implementation of the get_user function. In the example below, the mapping is implemented using pg_ident.conf. Token verification It consists in verifying that the required scope specified in pg_hba.conf is present in the scope field in the token received from the server. Upon successful verification, the user ID from the sub token field is assigned res->authn_id.
The main verification logic is implemented in the validate_token function. The general scheme of its work:
- Token content analysis.
The source string of the token is analyzed to extract its contents (payload). If the token has an incorrect format or the contents cannot be extracted, the verification fails.
- Extracting the sub and scope fields from the JWT token. The token content must include both fields:
- sub (Subject) - user ID
- scope -- a list of permissions (Scopes) provided by the token, separated by spaces
If any of these fields are missing, the verification is considered failed.
Purpose of the identifier. The sub value is assigned to the res->authn_id field, which PostgreSQL uses to identify the user. This value is then compared with the entries in pg_ident.conf to determine the actual role in the database that the user can use.
Comparison of permissions (scopes). The permissions granted by the token are compared with those required by the corresponding entry in pg_hba.conf (from oauth_scope). If all required permissions are present in the token, the verification is considered successful.
Setting the authorization result. The res->authorized flag is set to true if the permissions match, otherwise false.
Identification mapping. The sub value is then mapped (outside of this module) to the entries in pg_ident.conf to determine the actual role in the database that the user can use.
Let's start writing our training validator (its sources are also posted on ). First, let's create the oauth_validator folder, and in it we'll create the oauth_validator.c file
#include <string.h>
#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;
}
token_utils.h/.c -- utility functions
#ifndef TOKEN_UTILS_H
#define TOKEN_UTILS_H
#include <stdbool.h>
#include "common/jsonapi.h"
#include "nodes/pg_list.h"
const char* parse_token_payload(const char *token);
void extract_sub_scope_fields(const char *json, char **sub_field, char **scope_field);
const char *decode_base64(const char *b64);
char *base64url_to_base64(const char *b64url);
List *split_scopes(const char *raw);
bool check_scopes(List *granted, List *required);
#endif
#include "postgres.h"
#include "token_utils.h"
#include "common/base64.h"
#include "mb/pg_wchar.h"
#define SUB_FIELD 0 /* Index for 'sub' field */
#define SCOPE_FIELD 1 /* Index for 'scope' field */
/*
* JSON object field handler.
* Marks that the currently processed field is 'sub' or 'scope'
* to store its value at the next processing stage.
*/
static JsonParseErrorType
token_field_start(void *state, char *fname, bool isnull)
{
char **fields = (char **) state;
if (strcmp(fname, "sub") == 0)
fields[SUB_FIELD] = (char *) 1; /* Mark that we are processing 'sub' field */
else if (strcmp(fname, "scope") == 0)
fields[SCOPE_FIELD] = (char *) 1; /* Mark that we are processing 'scope' field */
return JSON_SUCCESS;
}
/*
* JSON scalar value handler.
* Stores the value of 'sub' or 'scope' if it was marked earlier.
*/
static JsonParseErrorType
token_scalar(void *state, char *token, JsonTokenType tokentype)
{
char **fields = (char **) state;
if (fields[SUB_FIELD] == (char *) 1)
fields[SUB_FIELD] = pstrdup(token); /* Save the value of 'sub' */
else if (fields[SCOPE_FIELD] == (char *) 1)
fields[SCOPE_FIELD] = pstrdup(token); /* Save the value of 'scope' */
return JSON_SUCCESS;
}
/*
* Extracts 'sub' and 'scope' fields from a JSON string.
*
* Parameters:
* - json: JSON string
* - sub_field: returns the value of 'sub' field
* - scope_field: returns the value of 'scope' field
*/
void
extract_sub_scope_fields(const char *json, char **sub_field, char **scope_field)
{
JsonLexContext lex;
JsonSemAction sem;
char **fields = palloc0(sizeof(char *) * 2); /* Allocate memory for 2 strings ('sub', 'scope') */
*sub_field = NULL;
*scope_field = NULL;
/* Create a lexical context for JSON parsing */
makeJsonLexContextCstringLen(&lex, json, strlen(json), GetDatabaseEncoding(), true);
/* Set up JSON parser handlers */
memset(&sem, 0, sizeof(sem));
sem.semstate = (void *) fields;
sem.object_field_start = token_field_start;
sem.scalar = token_scalar;
/* Start JSON parsing */
pg_parse_json(&lex, &sem);
/* Return the found values */
*sub_field = fields[SUB_FIELD];
*scope_field = fields[SCOPE_FIELD];
}
/*
* Extracts the payload from a JWT token.
* Returns the decoded payload string in JSON format.
*/
const char*
parse_token_payload(const char *token)
{
char *dot1 = NULL;
char *dot2 = NULL;
int payload_len = 0;
char *payload_b64url = NULL;
char *b64 = NULL;
if(!token)
return NULL;
/* Find the first and second dots in JWT (separators for header.payload.signature) */
dot1 = strchr(token, '.');
dot2 = dot1 ? strchr(dot1 + 1, '.') : NULL;
if (!dot1 || !dot2)
{
elog(LOG, "Invalid token format, two dots required: %s", token);
return NULL;
}
/* Extract the encoded payload between the dots */
payload_len = dot2 - (dot1 + 1);
payload_b64url = pnstrdup(dot1 + 1, payload_len);
/* Convert base64url to regular base64 */
b64 = base64url_to_base64(payload_b64url);
/* Decode base64 to JSON string */
return decode_base64(b64);
}
/*
* Converts a base64url string to base64 format.
* Replaces '-' with '+', '_' with '/' and adds padding '=' if necessary.
*/
char *
base64url_to_base64(const char *b64url)
{
int len = strlen(b64url);
int pad = (4 - (len % 4)) % 4; /* Determine the number of '=' padding characters */
char *b64 = palloc(len + pad + 1);
for (int i = 0; i < len; i++)
{
if (b64url == '-')
b64 = '+';
else if (b64url == '_')
b64 = '/';
else
b64 = b64url;
}
/* Add padding '=' */
for (int i = 0; i < pad; i++)
b64[len + i] = '=';
b64[len + pad] = '\0';
return b64;
}
/*
* Decodes a base64 string into a regular string.
* Returns the decoded string or NULL in case of error.
*/
const char *
decode_base64(const char *b64)
{
int encoded_len = strlen(b64);
int max_decoded_len = pg_b64_dec_len(encoded_len); /* Calculate required buffer length */
char *decoded = palloc(max_decoded_len + 1);
int decoded_len = pg_b64_decode(b64, encoded_len, decoded, max_decoded_len);
if (decoded_len <= 0)
{
elog(LOG, "Invalid token format: base64 decoding error");
return NULL;
}
decoded[decoded_len] = '\0';
return decoded;
}
/*
* Splits a space-separated string (e.g., scope list from token) into a List of strings.
*/
List *
split_scopes(const char *raw)
{
List *result = NIL;
char *str = pstrdup(raw); /* Make a copy of the string because strtok modifies it */
char *tok = strtok(str, " ");
while (tok)
{
result = lappend(result, pstrdup(tok));
tok = strtok(NULL, " ");
}
return result;
}
/*
* String comparison function for list sorting.
*/
static int
list_string_cmp(const ListCell *a, const ListCell *b)
{
const char *sa = (const char *) lfirst(a);
const char *sb = (const char *) lfirst(b);
return strcmp(sa, sb);
}
/*
* Checks whether all required scopes are present in the granted scopes.
* Lists are sorted beforehand for easier comparison.
*
* Returns true if all required scopes are found in granted scopes.
*/
bool
check_scopes(List *granted, List *required)
{
ListCell *gcell;
ListCell *rcell;
/* Sort both lists to simplify comparison */
list_sort(granted, list_string_cmp);
list_sort(required, list_string_cmp);
gcell = list_head(granted);
rcell = list_head(required);
while (rcell != NULL && gcell != NULL)
{
char *r = (char *) lfirst(rcell);
char *g = (char *) lfirst(gcell);
int cmp = strcmp(r, g);
if (cmp == 0)
{
/* Match found --- move to the next required element */
rcell = lnext(required, rcell);
gcell = lnext(granted, gcell);
}
else if (cmp > 0)
{
/* granted is behind --- move to the next granted element */
gcell = lnext(granted, gcell);
}
else
{
/* required element not found in granted --- return false */
return false;
}
}
/* If not all required elements were found --- error */
if (rcell != NULL)
return false;
return true;
}
Makefile
# contrib/oauth_validator/Makefile
PGFILEDESC = "oauth_validator - OAuth validator"
MODULE_big = oauth_validator
OBJS =\
$(WIN32RES)\
oauth_validator.o\
token_utils.o
PG_CPPFLAGS += -I$(top_srcdir)/src/common
PG_CPPFLAGS += -I$(libpq_srcdir)
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
Callbacks
startup_cb callback
The startup_cb callback is executed immediately after the module is loaded. It can be used to configure the local state and perform additional initialization if required. If the validator has a state, it can use the state->private_data field to store it.
typedef void (*ValidatorStartupCB) (ValidatorModuleState *state);
ValidatorStartupCB startup_cb;
validate_cb callback
The validate_cb callback is executed when the user tries to pass authorization using OAuth. Any state set in previous calls will be available in state->private_data.
typedef bool (*ValidatorValidateCB) (const ValidatorModuleState *state,
const char *token, const char *role,
ValidatorModuleResult *result);
ValidatorValidateCB validate_cb;
The argument token will contain a carrier token for verification. PostgreSQL made sure that the token was formed syntactically correct, but no other check was carried out. The role parameter contains the role on behalf of which the user requested login. The callback should set the output parameters in the resulting structure, which is defined as:
typedef struct ValidatorModuleResult
{
bool authorized;
char *authn_id;
} ValidatorModuleResult;
The connection will be established only if the validator sets the result->authorized parameter to true. To authenticate a user under an authenticated username (i.e. a specific one using a token), memory must be allocated (using the palloc function ), and a pointer to this memory region must be assigned to the result->authn_id field. Alternatively, the result->authn_id parameter can be set to NULL if the token is valid but the associated user ID cannot be determined.
If the token verification fails , the validator must return false due to incorrect token format, lack of necessary user rights, or other error, then any parameters from the result argument are ignored and the connection is interrupted. In case of successful verification of the token, the validator must return true.
The behavior after returning from validate_cb depends on the specific HBA setting. Usually, the user name result->authn_id must correspond exactly to the role under which the user logs in (this behavior can be changed using the user's card). But when authenticating according to the HBA rule with delegate_ident_mapping enabled, PostgreSQL will not perform any checks on the result->authn_id value at all; in this case, the validator must ensure that the token has sufficient privileges so that the user can log in under the specified role.
Shutdown_cb callback
The shutdown_cb callback is executed when there is a server process associated with the connection. If the validator has any allocated state, this callback should release it to avoid resource leakage.
typedef void (*ValidatorShutdownCB) (ValidatorModuleState *state);
ValidatorShutdownCB shutdown_cb;
Authorization process
Logging
Let's start by configuring logging. To see what requests PostgreSQL sends and what responses it receives, query logging can be enabled in postgresql.conf:
log_connections = on
Examples of logs will be presented below, prefixes will appear at the beginning of the lines, meaning the following:
the prefix ">" means a Keycloak request.
the prefix "<" means the Keycloak response.
[libcurl] * Trying 192.168.0.156:8080...
[libcurl] * Connected to 192.168.0.156 (192.168.0.156) port 8080 (#0)
[libcurl] > GET /realms/postgres-realm/.well-known/openid-configuration HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] >
[libcurl] < HTTP/1.1 200 OK
[libcurl] < content-length: 6638
[libcurl] < Cache-Control: no-cache, must-revalidate, no-transform, no-store
[libcurl] < Content-Type: application/json;charset=UTF-8
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"issuer":"","authorization_endpoint":"","token_endpoint":"","introspection_endpoint":"","userinfo_endpoint":"","end_session_endpoint":"","frontchannel_logout_session_supported":true,"frontchannel_logout_supported":true,"jwks_uri":"","check_session_iframe":"","grant\_types\_supported":["authorization\_code","client\_credentials","implicit","password","refresh\_token","urn:ietf:params:oauth:grant-type:device\_code","urn:ietf:params:oauth:grant-type:token-exchange","urn:ietf:params:oauth:grant-type:uma-ticket","urn:openid:params:grant-type:ciba"],"acr\_values\_supported":["0","1"],"response\_types\_supported":["code","none","id\_token","token","id\_token token","code id_token","code token","code id_token token"],"subject_types_supported":["public","pairwise"],"prompt_values_supported":["none","login","consent"],"id_token_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"id_token_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"id_token_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"userinfo_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512","none"],"userinfo_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"userinfo_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"request_object_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512","none"],"request_object_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"request_object_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"response_modes_supported":["query","fragment","form_post","query.jwt","fragment.jwt","form_post.jwt","jwt"],"registration_endpoint":"","token_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"token_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"introspection_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"introspection_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"authorization_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"authorization_encryption_alg_values_supported":["ECDH-ES+A256KW","ECDH-ES+A192KW","ECDH-ES+A128KW","RSA-OAEP","RSA-OAEP-256","RSA1_5","ECDH-ES"],"authorization_encryption_enc_values_supported":["A256GCM","A192GCM","A128GCM","A128CBC-HS256","A192CBC-HS384","A256CBC-HS512"],"claims_supported":["aud","sub","iss","auth_time","name","given_name","family_name","preferred_username","email","acr"],"claim_types_supported":["normal"],"claims_parameter_supported":true,"scopes_supported":["openid","offline_access","organization","service_account","postgres","address","phone","acr","profile","microprofile-jwt","web-origins","roles","basic","email"],"request_parameter_supported":true,"request_uri_parameter_supported":true,"require_request_uri_registration":true,"code_challenge_methods_supported":["plain","S256"],"tls_client_certificate_bound_access_tokens":true,"revocation_endpoint":"","revocation_endpoint_auth_methods_supported":["private_key_jwt","client_secret_basic","client_secret_post","tls_client_auth","client_secret_jwt"],"revocation_endpoint_auth_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","HS256","HS512","ES256","RS256","HS384","ES512","PS256","PS512","RS512"],"backchannel_logout_supported":true,"backchannel_logout_session_supported":true,"device_authorization_endpoint":"","backchannel_token_delivery_modes_supported":["poll","ping"],"backchannel_authentication_endpoint":"","backchannel_authentication_request_signing_alg_values_supported":["PS384","RS384","EdDSA","ES384","ES256","RS256","ES512","PS256","PS512","RS512"],"require_pushed_authorization_requests":false,"pushed_authorization_request_endpoint":"","mtls_endpoint_aliases":{"token_endpoint":"","revocation_endpoint":"","introspection_endpoint":"","device_authorization_endpoint":"","registration_endpoint":"","userinfo_endpoint":"","pushed_authorization_request_endpoint":"","backchannel_authentication_endpoint":""},"authorization\_response\_iss\_parameter\_supported":true}
auth device
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/auth/device HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 47
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > scope=openid+postgres&client_id=postgres-client
[libcurl] < HTTP/1.1 200 OK
[libcurl] < Cache-Control: no-store, must-revalidate, max-age=0
[libcurl] < content-length: 296
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"device_code":"tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY","user_code":"WXAI-ZNVY","verification_uri":"","verification_uri_complete":"","expires\_in":600,"interval":5}
token
The client is awaiting approval of the user's authorization request:
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/token HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 147
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > device_code=tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY&grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Adevice_code&client_id=postgres-client
[libcurl] < HTTP/1.1 400 Bad Request
[libcurl] < Cache-Control: no-store
[libcurl] < Pragma: no-cache
[libcurl] < content-length: 98
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"error":"authorization_pending","error_description":"The authorization request is still pending"}
The user is approved:
[libcurl] * Connection #0 to host 192.168.0.156 left intact
[libcurl] * Found bundle for host: 0x6124961fd400 [serially]
[libcurl] * Can not multiplex, even if we wanted to
[libcurl] * Re-using existing connection #0 with host 192.168.0.156
[libcurl] * Server auth using Basic with user 'postgres-client'
[libcurl] > POST /realms/postgres-realm/protocol/openid-connect/token HTTP/1.1
[libcurl] > Host: 192.168.0.156:8080
[libcurl] > Authorization: Basic cG9zdGdyZXMtY2xpZW50OmZTY1hYcDFUcFNQY3BVaEZLcWk0eDk4alZ5NTR1Y1RC
[libcurl] > Content-Length: 147
[libcurl] > Content-Type: application/x-www-form-urlencoded
[libcurl] >
[libcurl] > device_code=tgElqUogevqjEkZy6-Z1i209pgoKW9_CT0t9wwNjafY&grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Adevice_code&client_id=postgres-client
[libcurl] < HTTP/1.1 200 OK
[libcurl] < Cache-Control: no-store
[libcurl] < Pragma: no-cache
[libcurl] < content-length: 3307
[libcurl] < Content-Type: application/json
[libcurl] < Referrer-Policy: no-referrer
[libcurl] < Strict-Transport-Security: max-age=31536000; includeSubDomains
[libcurl] < X-Content-Type-Options: nosniff
[libcurl] < X-Frame-Options: SAMEORIGIN
[libcurl] <
[libcurl] < {"access_token":"eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICIwb1RQSV85LXVIQWJ5Q0t3M2luTm5MTW5hU21hc05nWS1OaDVkTzJ4X0lNIn0.eyJleHAiOjE3NDYwMzEyNTMsImlhdCI6MTc0NjAzMDk1MywiYXV0aF90aW1lIjoxNzQ2MDMwOTUyLCJqdGkiOiJvbnJ0ZGc6MWU3MmRlNGUtNTRhYi00OTZjLWIxYWYtY2FhYmRiYzJlYzExIiwiaXNzIjoiaHR0cDovLzE5Mi4xNjguMC4xNTY6ODA4MC9yZWFsbXMvcG9zdGdyZXMtcmVhbG0iLCJhdWQiOiJhY2NvdW50Iiwic3ViIjoiMGZjNzJiNmYtNjIyMS00ZWQ4LWE5MTYtMDY5ZTdhMDgxZDE0IiwidHlwIjoiQmVhcmVyIiwiYXpwIjoicG9zdGdyZXMtY2xpZW50Iiwic2lkIjoiNjRlMDUzMTMtM2UyMi00MmNjLWE0YmItOTAxODU2YTFhYjMzIiwiYWxsb3dlZC1vcmlnaW5zIjpbIi8qIl0sInJlYWxtX2FjY2VzcyI6eyJyb2xlcyI6WyJvZmZsaW5lX2FjY2VzcyIsImRlZmF1bHQtcm9sZXMtcG9zdGdyZXMtcmVhbG0iLCJ1bWFfYXV0aG9yaXphdGlvbiJdfSwicmVzb3VyY2VfYWNjZXNzIjp7ImFjY291bnQiOnsicm9sZXMiOlsibWFuYWdlLWFjY291bnQiLCJtYW5hZ2UtYWNjb3VudC1saW5rcyIsInZpZXctcHJvZmlsZSJdfX0sInNjb3BlIjoib3BlbmlkIHByb2ZpbGUgcG9zdGdyZXMiLCJuYW1lIjoiYWxpY2UgcG9zdGdyZXMiLCJwcmVmZXJyZWRfdXNlcm5hbWUiOiJhbGljZSIsImdpdmVuX25hbWUiOiJhbGljZSIsImZhbWlseV9uYW1lIjoicG9zdGdyZXMifQ.RXMszI-snIdrXyyTw74U8QXQeDG3zpfV4OvxYuJQvsb86eauXkKHAH35GfEm3XvQbtmpdSdfs1S4i11d69dUjpVTgPpzx6G7IXCXj2NTowzZuyuvdnLxPi1aXdxXqOKNSLSj5PXhGIaZhWsn2sR8dAJ0jjWTUO_lh8qJuJYaDcFulWn_flHVGQYzMZ5PTneRadg8h_1dWp4HSr6yC74NmF94dnOBmytivM4a__Wcq6TkZ3KLn_gafqnn72HpWY0WRwyZdQuzc5o8mE3UUAoKukxMnwDG7Yhxif2Y
a5aCloMbL9aDghbMypahl3MiJHHx3j50FavSRm0FJa3zK9w","expires_in":300,"refresh_expires_in":1800,"refresh_token":"eyJhbGciOiJIUzUxMiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICJjOGNiNjY3Ni00OTAxLTRmNjItOTI0OS1kMzY2MWI5Mjg3OTIifQ.eyJleHAiOjE3NDYwMzI3NTMsImlhdCI6MTc0NjAzMDk1MywianRpIjoiZTJkNzkzODUtNjBhZS00MTIwLWIwODAtNjVmYWU4ZmNhYzIzIiwiaXNzIjoiaHR0cDovLzE5Mi4xNjguMC4xNTY6ODA4MC9yZWFsbXMvcG9zdGdyZXMtcmVhbG0iLCJhdWQiOiJodHRwOi8vMTkyLjE2OC4wLjE1Njo4MDgwL3JlYWxtcy9wb3N0Z3Jlcy1yZWFsbSIsInN1YiI6IjBmYzcyYjZmLTYyMjEtNGVkOC1hOTE2LTA2OWU3YTA4MWQxNCIsInR5cCI6IlJlZnJlc2giLCJhenAiOiJwb3N0Z3Jlcy1jbGllbnQiLCJzaWQiOiI2NGUwNTMxMy0zZTIyLTQyY2MtYTRiYi05MDE4NTZhMWFiMzMiLCJzY29wZSI6Im9wZW5pZCBwcm9maWxlIHdlYi1vcmlnaW5zIHBvc3RncmVzIHJvbGVzIGJhc2ljIn0.43pRSq4PBO7ZY86jt8dIL7xZJylntY_CZXllcRfwfh41IRCOft6iqIWdJQp7TJv_JIDI-_-QeOSf1EC_wzABNg","token_type":"Bearer","id_token":"eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICIwb1RQSV85LXVIQWJ5Q0t3M2luTm5MTW5hU21hc05nWS1OaDVkTzJ4X0lNIn0.eyJleHAiOjE3NDYwMzEyNTMsImlhdCI6MTc0NjAzMDk1MywiYXV0aF90aW1lIjoxNzQ2MDMwOTUyLCJqdGkiOiIzNWM3ZDAyZC05OGFjLTQzMTgtOTg3NC0zYzY0Mjg5NjFhMjgiLCJpc3MiOiJodHRwOi8vMTkyLjE2OC4wLjE1Njo4MDgwL3JlYWxtcy9wb3N0Z3Jlcy1yZWFsbSIsImF1ZCI6InBvc3RncmVzLWNsaWVudCIsInN1YiI6IjBmYzcyYjZmLTYyMjEtNGVkOC1hOTE2LTA2OWU3YTA4MWQxNCIsInR5cCI6IklEIiwiYXpwIjoicG9zdGdyZXMtY2xpZW50Iiwic2lkIjoiNjRlMDUzMTMtM2UyMi00MmNjLWE0YmItOTAxODU2YTFhYjMzIiwiYXRfaGFzaCI6ImphOXRKZ1E0VkVPTTNBZGc0VWJBVGciLCJuYW1lIjoiYWxpY2UgcG9zdGdyZXMiLCJwcmVmZXJyZWRfdXNlcm5hbWUiOiJhbGljZSIsImdpdmVuX25hbWUiOiJhbGljZSIsImZhbWlseV9uYW1lIjoicG9zdGdyZXMifQ.dH5hM21-ygBiCXoA9NVOou-L3esUVJUFFUmt_1fU0jc9al4Lk7EUN-cqHicHZPD48HhkIWMJK7WZjxnZLDlkG7ORGdDKPGccMU4-sGsRuVu-GDuN5kHAh_NJZsLBXz9UkpNBkd8ROxK3-fbmyYdwsuwNeg6KNhSOj0FxEnxLc0-HrjEE92P7hzq0PD29oY2jhRKcqpbtknMwxFkkMBi8xPgdpuyTmLtJD3-xxYuwMKP7WUGzwzVAFqfrhFm5O5dJxeld5fTFE4Kyl9fR24JcjtfxBeIHVJqLiQkl9Et_KNGiFoXDG4Xwcc7eIUaBnauhY5_froYvKS8NbQxCOUg","not-before-policy":0,"session_state":"64e05313-3e22-42cc-a4bb-901856a1ab33","scope":"openid profile postgres"}Authorization via psql
For testing purposes, we will allow authorization over the unsecured http protocol (when using https, you will need to configure certificate chains):
export PGOAUTHDEBUG="UNSAFE"
Let's launch psql and set the connection details:
psql "user=alice dbname=postgres oauth_issuer= oauth_client_id=postgres-client oauth_client_secret=YYi8LqfzHRMnqUUlptpWVC2k7eWNrqjX"
We will see the URL and the code that you will need to enter after clicking on this URL:
Visit and enter the code: LJDW-RURX
We enter it in the browser [
Enter the code and click Submit.
Next, we see the user login window:
Enter the username and password (see ) and click on Sign In.
Next, he asks if we are ready to provide (send) such information to PostgreSQL. Click
.Successfully logged in...
... and we can enter commands in the terminal:
psql (18devel) Type "help" for help.
postgres=>
As a result, we successfully logged in to PostgreSQL.
Conclusion
Integration of OAuth 2.0 Device Authorization Flow, introduced in the PostgreSQL database management system 18 and Tantor Postgres 17.5.0, allows the use of the SSO (single sign-on) mechanism. Implementation of centralized access control via providers like Keycloak increase the level of protection, eliminating the risks of password interception, and optimize administration in distributed environments. The article provides a step-by-step guide from setting up Keycloak and PostgreSQL configurations before implementing the token validator and successful authorization via psql.