DEV Community: Tantor Labs

How to load test PostgreSQL database and not miss anything

Tantor Labs — Tue, 11 Nov 2025 06:39:33 +0000

During load testing of Tantor Postgres databases or other PostgreSQL-based databases using the standard tool pgbench, specialists often encounter non-representative results and the need for repeated tests due to the fact that details of the environment (such as DBMS configuration, server characteristics, PostgreSQL versions) are not recorded. In this article we are going to review author's pg_perfbench, which is designed to address this issue. It ensures that scenarios are repeatable, prevents the loss of important data, and streamlines result comparison by registering all parameters in a single template. It also automatically launches pgbench with TPC-B load generation, collects all metadata on the testing environment, and generates a structured report.

Automation of PostgreSQL-based DB load testing and system information collection

During development and support, it is frequently necessary to quickly conduct a load test in order to evaluate hypotheses or the overall efficiency of Tantor Postgres DB or other PostgreSQL-based databases. Most often, specialists use the standard tool pgbench for TPC-B load generation and evaluate the results. However, when describing the results of such tests, important details about the environment are often overlooked. There is no clear description of the used configuration: PostgreSQL parameters, server hardware characteristics, client version, and network settings. And that can lead to ambiguous or incomplete conclusions re performance. And you may need to spend time on re-running tests.

To eliminate such routine tasks and minimize the risk of 'forgotten' details, I developed the pg_perfbench tool, which starts pgbench and collects data on the database environment, then generates a full report based on a predefined template. This helps to replay testing scenarios, document all necessary database environment details, and quickly compare test results if needed.

Motivation for development

Let's review a typical performance measurement scenario for Tantor Postgres or other PostgreSQL-based databases, during which we need not only to obtain transactions per second but also to create a detailed description of the performed test. In real practice, database administrators and developers face the following tasks:

Checking the behavior of the database under increased load (number of connections, data size, etc.).
Comparing the performance of different versions of PostgreSQL or different configurations (for example, different parameters like shared_buffers, work_mem, enabled extensions, etc.).
Recording test results for follow-up analysis and replay.

However, without a full description of the environment, including OS versions, parameters of the network infrastructure, server characteristics (CPU, RAM, disk subsystem), and the PostgreSQL configuration itself, follow-up analysis and replay of experiments become quite challenging. Any insignificant change, whether the use of different data directories (for example, one with included checksums, and another without them) or other differences in hardware and network configuration, can significantly affect the results. Then, when trying to compare the new indicators with the previous ones, it becomes clear that the tests are actually incomparable, since they were run under different conditions.

The idea of supplementing standard load testing tools with automatic report generation originates from the need to record both productivity indicators and details on the environment. For similar pgbench runs, solutions with custom scripts are well suited, but they often require modification and manual collection of data to get a complete picture. In practice, this leads to an increase in temporary costs and the risk of missing some important detail in the final description.

Standard load testing cycle with pgbench

pgbench operates with a load built per TPC-B model, and allows you to quickly obtain the following metrics:

tps (transactions per second) - the number of transactions executed per second.
Latency average - average latency between transactions' execution, measured in milliseconds (ms).
Number of transactions actually processed - the total number of successfully processed transactions.
Number of failed transactions - the number of transactions that ended with an error.
Initial connection time - the time spent on establishing the initial connection with the database, measured in milliseconds (ms) or seconds (s) depending on the measurement context.

To demonstrate, let's take a classic scenario: cyclic launch of pgbench in a simple Bash script with iterations, where the number of clients (connections to the database) varies. Script example:

/usr/lib/postgresql/15/bin/pgbench -i --scale=4000 --foreign-keys -h `hostname` -p 5432 -U postgres test

#!/bin/bash
clients="1 10 20 50 100"
t=600
dir=/var/lib/postgresql/test_result
mkdir -p $dir
for c in $clients; do
echo "pgbench_${c}_${t}.txt"
echo "start test: "`date +"%Y.%m.%d_%H:%M:%S"` >> "${dir}/pgbench_${c}.txt"
/usr/lib/postgresql/15/bin/pgbench -h `hostname` -p 5432 test -c $c -j $c -T $t >> "${dir}/pgbench_${c}.txt"
echo "stop test: "`date +"%Y.%m.%d_%H:%M:%S"` >> "${dir}/pgbench_${c}.txt"
done

This code:

Initializes pgbench test database with a scale of 4000 (parameter --scale=4000) and foreign keys (--foreign-keys).
Launches load cycles based on the variable number of clients (1, 10, 20, 50, 100) over 600 seconds (-T 600).
Logs the results into separate files, starting and ending each iteration with timestamps.

Typically, a chart is generated at the end, showing the dependency of indicators (e.g., tps or average latency) on the number of clients:

We can also create an additional chart to show the Latency average.

Despite the fact that pgbench data already provide a basic performance estimate, we also need to consider the following to get the full analysis:

Server parameters:

CPU (model, number of cores, Hyper-Threading support);
RAM (capacity, frequency, type);
disk space (storage type, capacity, RAID configuration, etc.);
network parameter settings (TCP/IP stack, MTU, etc.);
system drivers (e.g. for SSD or network cards).

Database parameters:

exact version of PostgreSQL (server and client, if necessary — patches);
enabled extensions (e.g. pg_repack, pg_stat_statements);
database configuration (postgresql.conf) and key parameters (shared_buffers, work_mem, wal_buffers, etc.);
pg_config results (build options, paths to directories, etc.);
enabled replications (physical or logical).

When replaying the same test, we need to ensure the consistency of the above-listed environmental parameters, since discrepancies in the listed aspects can affect the final numbers (for example, if the CPU in one environment differs from the CPU in another, direct comparison of tps results becomes meaningless). Therefore, in serious research, it is necessary to confirm that the test was replayed with the same environmental parameters.

The need for ready-made solutions and automation

To conduct load testing and collect the listed system information, you can use a set of open-source utilities, but practically all of them require manual integration and additional scripts. In other words, the administrator has to separately collect information about the configuration of Tantor Postgres or another PostgreSQL-based DB, network settings, operating system kernel version, etc., and then generate a unified report, often generated in an arbitrary format, which makes it challenging to compare this report with colleagues' data.

In order for all necessary data to be assembled and documented automatically, adds-on to standard tools are required. This is the issue pg_perfbench is designed to solve, as it allows:

running standard or custom pgbench scripts:
simultaneously collecting metadata about hardware, OS, and PostgreSQL configuration;
automatically generating output report per specified template.

As a result, when conducting stress tests, you can be sure that no crucial detail related to the environment will be missed. This report simplifies comparison of results, saves time on configuring repeated runs, and ensures a high level of reproducibility of experiments.

Automation of DB performance test

The overview of pg_perfbench operation during load testing:

The process of load testing and performance analysis of PostgreSQL is significantly simplified if in the end we receive a full structured report containing not only numerical indicators (tps, Latency average, etc.), but also detailed information about the environment. Such a report helps:

To compare the results of different runs and configuration versions without losing context.
To store all essential information about the server, PostgreSQL parameters, and network settings in one place.
To replay test scenarios, with ready-to-use template containing all the crucial data.

There are several core sections in the generated report:

System properties - describes the characteristics of the hardware part (processor, memory, disks, network settings).
Common database info - contains information about the PostgreSQL version, enabled extensions, and key database parameters.
Common benchmark info - specifies pgbench run settings (number of clients, testing time, used commands).
Tests result info - finalizes the results of executed tests, displays performance indicators (TPS, delays, etc.).

Collapsed report sections:

The formats used to generate allow easy reading and visualization of test results: JSON for storing data (including measurement results and configuration) and HTML for visualization of the report allowing to add links, styles, charts, etc. In our implementation, the HTML report contains a nested JSON object with filled data, used to visualize the report. Thus, the final process may look like:

Running test scenarios and collecting information.
Automatic report generation: a JSON file with a detailed structure and sections is created, and an HTML version of the report is generated based on the same template.
Analysis of results: required values are taken from JSON to build charts, comparison tables, etc., and the HTML report visualizes the testing results in a convenient interactive form.

Each section has parameters for structuring the interactive HTML report.

Section with a description of the server environment may include a table with information on the processor, memory, disks, as well as the output of some system commands. Expanded section contains elements (collapsed):

Section with a description of the DB configuration may contain postgresql.conf parameters, list of extensions, etc.

Collecting data on the DB environment

To collect server information, commands are usually executed in the terminal, for example:

df -h   # <---- disk space

cat /etc/fstab # <---- description of file systems

cat /etc/os-release # <---- description of the Linux distribution

uname -r -m # <---- kernel version

To collect information about the DB environment, you can use shell commands or SQL commands that are stored in separate files. The description of the report item indicates a file with these commands, and their results are included in the data field:

Let's look at the report section with data on the server environment filled:

Each item of the report structure has a description for interactive layout in HTML, which includes the following parameters:

header - the specified name of the section;
description - a description or a brief note;
state - the state of the report item (collapsed or expanded);
item_type - type of the returned script result (table or text);
shell_comand_file or sql_command_file - name of the shell or SQL script;
data - the field where the result of the script execution is recorded.

Information retrieval from the database is structured similarly:

Configuration of the database load testing

pgbench is configured in pg_perfbench user parameters:

python -m pg_perfbench --mode=benchmark\
--log-level=debug\
--connection-type=ssh\
--ssh-port=22\
--ssh-key=/key/p_key\
--ssh-host=10.100.100.100\
--remote-pg-host=127.0.0.1\
--remote-pg-port=5432\
--pg-host=127.0.0.1\
--pg-port=5439\
--pg-user=postgres\
--pg-password=pswd\
--pg-database=tdb\
--pg-data-path=/var/lib/postgresql/16/main\
--pg-bin-path=/usr/lib/postgresql/16/bin\
--benchmark-type=default\
--pgbench-clients=1,10,20,50,100\
--pgbench-path=/usr/bin/pgbench\
--psql-path=/usr/bin/psql\
--init-command="ARG_PGBENCH_PATH -i --scale=10 --foreign-keys -p ARG_PG_PORT -h ARG_PG_HOST -U postgres ARG_PG_DATABASE"\
--workload-command="ARG_PGBENCH_PATH -p ARG_PG_PORT -h ARG_PG_HOST -U ARG_PG_USER ARG_PG_DATABASE -c ARG_PGBENCH_CLIENTS -j 10 -T 10 --no-vacuum"

All settings are recorded in the report for a full description of the tool startup. The number of iterations is also set by the user: you can specify either --pgbench-clients (number of clients) or --pgbench-time (duration) for each test run.

Configuration of pg_perfbench user parameters is described in the documentation.

pgbench launch iterations will be displayed in the benchmark description section:

Since standard pgbench load queries are used, initialization and loading scripts will be displayed as pgbench commands:

Result of database load testing

pgbench data is conveniently structures as tables, where each row is one iteration, and the columns correspond to the following key metrics:

After that, on the chart, you can compare iterations by TPS (vertical axis - TPS, horizontal axis - number of clients):

Eventually, automating of collection of all required data and converting it into a convenient form (JSON + HTML, charts, tables) significantly simplifies not only analysis but also co-working on the project. You can reproduce tests quicker, compare results, and identify bottlenecks in PostgreSQL performance without wasting time on investigating what exactly was tested and under what conditions.

Comparison of reports

When testing and analyzing PostgreSQL performance, we often need to compare several results of load tests. This can be applicable for the following scenarios:

comparison of the behavior of the same database on different servers;
checking how changes in postgresql.conf affect the final throughput (TPS) and latency.

pg_perfbench provides a mechanism for comparing ready reports in --mode=join mode. The the core purpose of its work is to:

Make sure that we are actually comparing corresponding environments (identical PostgreSQL versions, similar system parameters, the same set of extensions, etc.).
After checking the key elements for matching, a single "consolidated" report is formed, where the difference in settings and benchmark results can be seen.

A JSON file with keys used to compare data from different reports may have the following structure:

{
      "description": <Description of the current task>,
       "items":
              [
               "sections.system.reports.sysctl_vm.data",
               "sections.system.reports.total_ram.data",
               "sections.system.reports.cpu_info.data",
               "sections.system.reports.etc_fstab.data",
               "sections.db.reports.version_major.data",
               "sections.db.reports.pg_available_extensions.data",
               "sections.db.reports.pg_config.data",
               "sections.benchmark.reports.options.data",
               "sections.benchmark.reports.custom_tables.data",
               "sections.benchmark.reports.custom_workload.data"
               ]
}

The "items" field contains an array of object fields representing the structure of the report. Equality of values will be checked based on these values. If it turns out that some of the compared mandatory items differ (for example, CPU on servers differ, or different versions of PostgreSQL are used), the utility will interrupt the join and display an error message. This protects against incorrect comparisons when the results simply do not make sense to compare directly.

Example log:

2025-03-28 16:31:02,285 INFO  root : 218 - Loaded 2 report(s): benchmark-ssh-custom-config.json, benchmark-ssh-default-config.json
2025-03-28 16:31:02,286 ERROR root : 190 - Comparison failed: Unlisted mismatch in 'cpu_info'
 reference report - benchmark-ssh-custom-config
 comparable report - benchmark-ssh-default-config
2025-03-28 16:31:02,286 ERROR root : 222 - Merge of reports failed.
2025-03-28 16:31:02,286 ERROR root : 317 - Emergency program termination. No report has been generated.

Comparison of DB performance with different settings in postgresql.conf

One of the most common tasks is to test the effect of changing PostgreSQL settings. Let's review a simple scenario when we have two configuration files:

postgresql_1.conf:

.....
shared_buffers = 166MB
work_mem = 10000kB
maintenance_work_mem = 20MB
effective_cache_size = 243MB

postgresql_2.conf:

.....
shared_buffers = 90MB
work_mem = 5000kB
maintenance_work_mem = 10MB
effective_cache_size = 150MB

The goal is to understand how the throughput capacity changes when the configuration parameters are changed. You need to run* pg_perfbench with these two configurations, as described above (each run of measurements generates a separate report*). Read more on configuration of user parameters in the documentation.

To join two reports, specify JSON with a list of "items" that do not include pg_settings (pg_perfbench/join_tasks/task_compare_dbs_on_single_host.json).

{
    "description": "Comparison of database performance across different configurations in the same environment using the same PostgreSQL version",
    "items": [
        "sections.system.reports.sysctl_vm.data",
        "sections.system.reports.sysctl_net_ipv4_tcp.data",
        "sections.system.reports.sysctl_net_ipv4_udp.data",
        "sections.system.reports.total_ram.data",
        "sections.system.reports.cpu_info.data",
        "sections.system.reports.etc_fstab.data",
        "sections.db.reports.version_major.data",
        "sections.db.reports.pg_available_extensions.data",
        "sections.db.reports.pg_config.data",
        "sections.benchmark.reports.options.data",
        "sections.benchmark.reports.custom_tables.data",
        "sections.benchmark.reports.custom_workload.data"
    ]
}

Now when running

python -m pg_perfbench --mode=join\
--report-name=join-diff-conf-reports\
--join-task=task_compare_dbs_on_single_host.json\
--input-dir=/pg_perfbench/report

the utility will check for compliance of the specified parameters: identical CPU, identical amount of RAM (at least the one documented in the reports), the same major version of PostgreSQL, matching set of extensions, etc. If the parameters comply, the utility will generate a consolidated report, and if any discrepancies are revealed in the listed items, it will output an error message. In the process log there will be a list of loaded reports and the result of the join:

python -m pg_perfbench --mode=join\
--report-name=join-diff-conf-reports\
--join-task=task_compare_dbs_on_single_host.json\
--input-dir=/pg_perfbench/report
2025-03-28 16:53:31,476       INFO                                root :   55 - Logging level: info
.....
#-----------------------------------
2025-03-28 16:53:31,476       INFO                                root :  211 - Compare items 'task_compare_dbs_on_single_host.json' loaded successfully:
sections.system.reports.sysctl_vm.data
sections.system.reports.sysctl_net_ipv4_tcp.data
sections.system.reports.sysctl_net_ipv4_udp.data
sections.system.reports.total_ram.data
sections.system.reports.cpu_info.data
sections.system.reports.etc_fstab.data
sections.db.reports.version_major.data
sections.db.reports.pg_available_extensions.data
sections.db.reports.pg_config.data
sections.benchmark.reports.options.data
sections.benchmark.reports.custom_tables.data
sections.benchmark.reports.custom_workload.data
.....
2025-03-28 16:53:31,481       INFO                                root :   99 - The report is saved in the 'report' folder
2025-03-28 16:53:31,481       INFO                                root :  322 - Benchmark report saved successfully.

Join-report will contain:

List of the compared reports and join parameters:

system data matches at least for the items listed in the join list. If there are elements not specified in the join list, and there are differences in the data field in the compared reports, then in the final Join report, the tab for such an item will be marked with a special color and with "Diff". In the tab itself, the results for the corresponding items of all compared reports will be shown.

DB data matches by join parameters.

Configuration of pgbench load. Differences in the configurations of user parameters are in different configurations (postgresql.conf) of the database reports.

results section with performance difference across benchmark reports.

On the chart, you can quickly assess the difference in benchmarks by tps.

Conclusion

The use of the pg_perfbench allows:

To avoid missing environment info: all important hardware parameters (CPU, RAM, disks, network settings) and configurations of Tantor Postgres or other PostgreSQL-based DB (version, extensions, main and secondary parameters, key utilities) are automatically collected during testing, which eliminates the risk of working with incomplete data during analysis.
To reduce the time spent on preparing and replaying tests: you no longer need to manually describe every detail of the test environment, and then repeat all manual steps. Load parameters (number of clients, test time, pgbench commands) and key system parameters are automatically recorded in one report.
To simplify analysis and comparison of results: information is collected in a structured format (JSON and/or HTML) that is easy to navigate. Visual representation (charts, tables) allows you to quickly assess the dynamics of indicators (tps), and the built-in join functionality makes it easier to compare results of several reports.
To ensure reproducibility of experiments: if necessary, you can go back to previous measurements and re-run them under the same conditions without having to manually restore all parameters - all important settings and environment information are already included in the report.
To standardize the approach to load testing: pg_perfbench provides a unified interface to connect to different types of environments (local machine, SSH, Docker) and integrate with pgbench. This increases flexibility and provides centralized management of the testing process.

Thus, pg_perfbench makes PostgreSQL load testing transparent, reliable, and reproducible, significantly simplifying the analysis of results, rerunning of scenarios, and comparing them.

You can find the reviewed project at https://github.com/TantorLabs/pg_perfbench

Redundant statistics slow down your Postgres? Try sampling in pg_stat_statements

Tantor Labs — Tue, 11 Nov 2025 06:33:34 +0000

pg_stat_statements is the standard PostgreSQL extension used to track query statistics: number of executions, total and average execution time, number of returned rows, and other metrics. This information allows to analyze query behavior over time, identify problem areas, and make informed optimization decisions. However, in systems with high contention, pg_stat_statements itself can become a bottleneck and cause performance drops. In this article, we will analyze in which scenarios the extension becomes a source of problems, how sampling is structured, and in which cases its application can reduce overhead.

Issue

Let's briefly recall how pg_stat_statements is structured in order to understand when and why the extension may slow down performance. This will help understand which mechanisms within the extension can become a bottleneck under high load.\
The key data structure in pg_stat_statements is a hash table. Each bucket in it contains execution metrics for a specific query. The key for this table is formed based on four parameters:

queryid — unique identifier of the normalized query;
User OID;
Database OID;
a toplevel flag indicating whether the query is top-level, meaning it's not nested within any internal function or subquery.

The hash table finds or creates the corresponding entry using this key and updates the collected query metrics.

Upon receiving a request, pg_stat_statements executes the following sequence of operations:

Search for the bucket: a shared LWLock is acquired on Проверить предлогthe hash table and it is searched through by key;
Normalization (optional): if there is no suitable bucket, the query is pre-normalized: literals are replaced with placeholders like $1, $2, etc.;
Creating a new bucket: the lock level of LWLock is raised to exclusive and a new bucket is created;
Recording query information in the bucket: to update the query metrics in the bucket, the SpinLock of that bucket is acquired. Then the locks on SpinLock and LWLock are released.

These locking operations with a large number of unique queries or high contention in pg_stat_statements become a bottleneck. Let's review this using a scenario where all SQL queries are unique from the perspective of pg_stat_statements. A machine with 48 CPUs can reproduce such a load. To ensure that the queries are unique, we will create 1000 similar tables with different names:

init_script.sql

DO $$

DECLARE

    i INT;

BEGIN

    FOR i IN 1..1000 LOOP

        EXECUTE format('CREATE TABLE table_%s (id INT PRIMARY KEY, value TEXT);', i);

        EXECUTE format('INSERT INTO table_%s (id, value) VALUES (1, ''test'');', i);

    END LOOP;

END;

$$;

Then, using the built-in random number generator in pgbench (pgbench_script.sql), we will create queries on these tables so that each of them is different and falls into a new bucket of the pg_stat_statements hash table.

pgbench_script.sql

\set table1_id random(1, 1000)

\set table2_id random(1, 1000)

\set table3_id random(1, 1000)

SELECT t1.value AS value1, t2.value AS value2, t3.value AS value3

FROM table_:table1_id t1

JOIN table_:table2_id t2 ON t1.id = t2.id

JOIN table_:table3_id t3 ON t2.id = t3.id

WHERE t1.id = 1 AND t2.id = 1 AND t3.id = 1;

To visually see what causes the performance drop, let's call pg_stat_activity once a second while the benchmark is running. The results of each query will be written in /tmp/waits file:

waits.sql

\o /tmp/waits

select 'OUT', COALESCE(wait_event, 'None') wait_event, COALESCE(wait_event_type, 'No wait') wait_event_type from  pg_stat_activity where state = 'active';

\watch 1

After the benchmark is completed, we group all types of delays and count how many times each of them occurred:

cat /tmp/waits | grep OUT | awk '{print $2 "|" $3}' FS="|" | sort | uniq -c | sort -n -r -k1

Then we run the benchmark, compare the system performance with the pg_stat_statements extension enabled and disabled, and display the reasons for performance drop. To do this, we will use standard pgbench utility:

Number of clients (-c): 48 - according to the number of CPUs;
Threads (-j): 20 - limits contention at the OS level to avoid overloading the CPU and context switches;
Duration (-T): 120 seconds;
Script: run pgbench_script.sql (-f pgbench_script.sql);
Metrics: total number of delays and final TPS.

pgbench -c48 -j20 -T120 -f pgbench_script.sql --progress 10 | grep "tps = " 2>>$RESULTS >>$RESULTS &

Now lets combine all these actions in one script:

RESULTS="/tmp/results"

rm -rfv $RESULTS

nohup pgbench -c48 -j20 -T120 -f pgbench_script.sql --progress 10 | grep "tps = " 2>>$RESULTS >>$RESULTS &

timeout 125 psql -f waits.sql

echo " count | wait_event | wait_event_type" >>$RESULTS

echo "--------------------------------------" >>$RESULTS

cat /tmp/waits | grep OUT | awk '{print $2 "|" $3}' FS="|" | sort | uniq -c | sort -n -r -k1 >>$RESULTS

cat $RESULTS

rm -rfv /tmp/waits

We will get the following results:

# With pg_stat_statements off

tps = 237 437.104223 (without initial connection time)

 count | wait_event | wait_event_type

--------------------------------------

   2922  None            No wait

    918  ClientRead    Client

# With pg_stat_statements on

tps =  32 112.129029 (without initial connection time)

 count |     wait_event     | wait_event_type

--------------------------------------

   4703  pg_stat_statements      LWLock

    884  None                    No wait

    143  ClientRead              Client

As we see here, with a large number of unique queries, enabled pg_stat_statements can significantly reduce performance — even leading to fold drop in TPS. And this all is caused by frequent 'exclusive' LWLock acquisition.Now let's review another scenario — with a large number of similar queries. Here we will need a more powerful machine with 192 CPUs. For the test, we will again use a script that periodically checks pg_stat_activity, but this time we will create the load using the same query executed through pgbench -M prepared -S with 192 clients:

pgbench -c192 -j20 -T120 -M prepared -S --progress 10 | grep "tps = " 2>>$RESULTS >>$RESULTS &

When we run this benchmark...

RESULTS="/tmp/results"

rm -rfv $RESULTS

pgbench -i -s500

nohup pgbench -c192 -j20 -T120 -M prepared -S --progress 10 | grep "tps = " 2>>$RESULTS >>$RESULTS &

timeout 125 psql -f waits.sql

echo " count | wait_event | wait_event_type" >>$RESULTS

echo "--------------------------------------" >>$RESULTS

cat /tmp/waits | grep OUT | awk '{print $2}' FS="|" | sort | uniq -c | sort -n -r -k1 >>$RESULTS

cat $RESULTS

rm -rfv /tmp/waits

..we get the following results:

# Results with pg_stat_statements off

tps = 1 015 425.438193 (without initial connection time)

 count | wait_event | wait_event_type

--------------------------------------

  13201  None             No wait

   3482  ClientRead       Client

# Results with pg_stat_statements on

tps =   484 338.163894 (without initial connection time)

 count | wait_event | wait_event_type

--------------------------------------

 11 214  SpinDelay        Timeout

   9481  None             No wait

    930  ClientRead       Client

If we try to reproduce this on a machine with 48 CPUs (i.e., with 48 users)...

pgbench -c48 -j20 -T120 -M prepared -S --progress 10 | grep "tps = " 2>>$RESULTS >>$RESULTS &

...the performance issues will stay well within the bounds of statistical error.

# Results with pg_stat_statements off

tps = 625 335.965464 (without initial connection time)

 count | wait_event | wait_event_type

--------------------------------------

    979  ClientRead         Client

    927  None               No wait

# Results with pg_stat_statements on

tps = 611 708.477697 (without initial connection time)

 count | wait_event | wait_event_type

--------------------------------------

   1000  ClientRead         Client

    978  None               No wait

This indicates that the impact of pg_stat_statements when working with duplicate queries becomes noticeable only at very high levels of parallelism. The main reason is the contention for the same entry in the hash table, which is accompanied by frequent SpinLock acquisitions when updating query metrics in the hash table bucket. When many threads simultaneously execute the same query, they try to update the same structure — increment call counters, execution time, and other metrics. This leads to severe contention for SpinLock, which under high load causes delays and reduces TPS.

What is sampling?

Query sampling is a method of uniform filtering, where only part of all queries is included in the sample. In the context of pg_stat_statements, this means that metric information is recorded not for every executed query, but only for some of them, with equal probability. A similar approach is used in PostgreSQL in other places: log_transaction_sample_rate and log_statement_sample_rate are used to reduce log volume, as well as in auto_explain.sample_rate and pg_store_plans.sample_rate. In Tantor Postgres 17.5, a corresponding setting was added to pg_stat_statements — the GUC parameter pg_stat_statements.sample_rate, which allows you to set fraction of queries that is tracked in the extension's statistics. The value of the parameter (from 0 to 1) determines what fraction of queries will be tracked in pg_stat_statements. A query will be sampled if the following condition is met:

Sampling

is_query_sampled = pgss_sample_rate != 0.0 &&

                    (pgss_sample_rate == 1.0 ||

                    pg_prng_double(&pg_global_prng_state) <= pgss_sample_rate);

Since the number of queries will be very large, using this inequation allows filtering only the specified fraction of the queries. The sampling method has one significant drawback: not all queries are tracked in pg_stat_statements. This impacts the completeness of the collected information, especially during debugging or analyzing rare but problematic queries. On the other hand, since only a part of the queries is tracked, the load on locks is reduced, and consequently, the overall system performance is improved.

Limitations of the sampling

If queries are sampled at the stage of adding a new bucket to the hash table, thereby unloading the LWLock, there is a risk of losing a valuable query hat would offer crucial information if tracked in pg_stat_statements. Moreover, even if sampling did take care of the performance problem, there is still the issue of security and correctness of query storage. The fact is that adding a bucket to the hash table can occur both before and after the query execution. But the structure required for normalizing the query is relayed only before the query execution, namely at the parsing stage, when we need to create a structure to sore literals for normalization. If at this stage pg_stat_statements decides not to save the query due to sampling, but then (after execution) still tries to add it to the hash table, the query will be recorded in its original (non-normalized) form. This may lead to the leakage of sensitive information in pg_stat_statements (for example, passwords or personal data in literals of a SQL query). Therefore, sampling during query parsing is unacceptable: it can violate security requirements.\
Nevertheless, both the PostgreSQL community and the developers at Tantor Labs are trying to solve the problem of a large number of unique queries in a different way — by merging similar queries under one queryid. This reduces the number of unique entries in the hash table and, accordingly, decreases the frequency of its locking. The community has already merged the following queries into one QueryId:

In SET commands;
Identical queries with different lengths of IN(...);
Temporary tables.

In scenarios where the load on SpinLock becomes bottleneck — for example, when the same entry in a hash table is updated frequently — sampling can be quite effective. Since SpinLock protects only a single bucket, fewer calls to it (by skipping some queries) reduces contention between threads and thus improves overall performance.

Sampling results

Let's review the above scenario with a powerful machine of 192 CPUs. The same waits.sql scripts and enabled pg_stat_statements are used for the test. Now let's run a benchmark to assess the impact of the pg_stat_statements.sample_rate on performance and the nature of waits. We run a loop over five sample_rate values: 1, 0.75, 0.5, 0.25, and 0. For each value, we run a load testing using pgbench:

benchmark.sh

CONNECTIONS=192

RESULTS="/tmp/results"

pgbench -i -s500

rm -rfv $RESULTS

for i in 1 .75 .5 .25 0

do

  psql -c "alter system set pg_stat_statements.sample_rate = ${i};" 2>/dev/null >/dev/null

  psql -c "select pg_reload_conf();" 2>/dev/null >/dev/null

  psql -c "show pg_stat_statements.sample_rate;" 2>/dev/null >/dev/null

  echo -e "\nsample_rate = $i" >>$RESULTS

  nohup pgbench -c $CONNECTIONS -j20 -T120 -S -Mprepared --progress 10 | grep "tps = " 2>>/tmp/results >>$RESULTS &

  timeout 125 psql -f /tmp/waits.sql

  echo " count | wait_event | wait_event_type" >>$RESULTS

  echo "--------------------------------------" >>$RESULTS

  cat /tmp/waits | grep OUT | awk '{print $2 "|" $3}' FS="|" | sort | uniq -c | sort -n -r -k1 >>$RESULTS

  rm -rfv /tmp/waits

done

cat $RESULTS

After running the benchmark with various pg_stat_statements.sample_rate values, we got the results in the table below. It shows how performance and the nature of waits change when we change the fraction of queries hitting the hash table:

At sample_rate = 1.0, when metrics are collected for all queries, TPS is the lowest and there is a huge number of waits on SpinLock. As the sample_rate decreases to 0.75 and below, TPS spikes, and SpinDelay decreases by 2.3 times. At sample_rate = 0.25 and below, SpinDelay disappears.\
Thus, sampling effectively reduces the overhead of pg_stat_statements and significantly improves performance in scenarios with high contention for SpinLock.

Conclusion

In pg_stat_statements, LWLock is used when adding a new entry to the hash table, and with a large number of unique queries, it can become a bottleneck. The PostgreSQL community is trying to solve this problem by reducing the number of new entries (namely by merging similar queries under one queryid), and in Tantor Postgres 17.5, corresponding configuration parameters were added to mask arrays and temporary tables: pg_stat_statements.mask_const_arrays and pg_stat_statements.mask_temp_tables. This helps to cluster similar queries more precisely.

In turn, SpinLock is used to protect individual buckets and becomes a source of contention when counters for identical queries are frequently updated, especially on machines with a large number of CPUs. To solve this problem, pg_stat_statements.sample_rate parameter was added in Tantor Postgres SE 17.5: it allows reducing the load by sampling queries, thereby eliminating the problem described in the article.

The developers of Tantor Labs proposed to merge the query sampling mechanism in pg_stat_statements in the main branch of PostgreSQL. There's already an active discussion happening over at pgsql-hackers — if you've got any thoughts or feedback, feel free to jump in!

OAuth 2.0 authorization in PostgreSQL using Keycloak as an example

Tantor Labs — Mon, 10 Nov 2025 10:42:46 +0000

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 OAuth 2.0 Device Authorization Flow 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 Keycloak, 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 http://localhost:8080, 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.

Creating Roles

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 "Writing a validator by the developer").

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.

Mapping users via the pg_ident.conf file

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 "Creating Users" section), in the third is the name of the role in PostgreSQL.

Mapping users through a validator

It is described below in the "Writing a validator by the developer" 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="http://192.168.0.156:8080/realms/postgres-realm/.well-known/openid-configuration" 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 http://192.168.0.156:8080/realms/postgres-realm/.well-known/openid-configuration . 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 "User mapping between Keycloak and PostgreSQL").

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="http://192.168.0.156:8080/realms/postgres-realm/.well-known/openid-configuration" 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="http://192.168.0.156:8080/realms/postgres-realm/.well-known/openid-configuration" 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:

1\ 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.

2\ 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.

3\ 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.

4\ 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.

5\ Setting the authorization result.

The res->authorized flag is set to true if the permissions match, otherwise false.

6\ 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 GitHub). 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[i] == '-')
            b64[i] = '+';
        else if (b64url[i] == '_')
            b64[i] = '/';
        else
            b64[i] = b64url[i];
    }

    /* 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.

discovery

[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":"http://192.168.0.156:8080/realms/postgres-realm","authorization_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/auth","token_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/token","introspection_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/token/introspect","userinfo_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/userinfo","end_session_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/logout","frontchannel_logout_session_supported":true,"frontchannel_logout_supported":true,"jwks_uri":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/certs","check_session_iframe":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/login-status-iframe.html","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":"http://192.168.0.156:8080/realms/postgres-realm/clients-registrations/openid-connect","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":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/revoke","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":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/auth/device","backchannel_token_delivery_modes_supported":["poll","ping"],"backchannel_authentication_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/ext/ciba/auth","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":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/ext/par/request","mtls_endpoint_aliases":{"token_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/token","revocation_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/revoke","introspection_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/token/introspect","device_authorization_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/auth/device","registration_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/clients-registrations/openid-connect","userinfo_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/userinfo","pushed_authorization_request_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/ext/par/request","backchannel_authentication_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/ext/ciba/auth"},"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":"http://192.168.0.156:8080/realms/postgres-realm/device","verification_uri_complete":"http://192.168.0.156:8080/realms/postgres-realm/device?user\_code=WXAI-ZNVY","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_gafqnn72HpWY0WRwyZdQuzc5o8mE3UUAoKukxMnwDG7Yhxif2YFb_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-GDuNFo_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=http://192.168.0.156:8080/realms/postgres-realm/.well-known/openid-configuration 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 http://192.168.0.156:8080/realms/postgres-realm/device and enter the code: LJDW-RURX

We enter it in the browser [http://192.168.0.156:8080/realms/postgres-realm/device](http://192.168.0.156:8080/realms/postgres-realm/device :

Enter the code and click Submit.

Next, we see the user login window:

Enter the username and password (see "Creating users") and click Sign In.

Next, he asks if we are ready to provide (send) such information to PostgreSQL. Click Yes.

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

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:

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.

Creating Roles

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 "Writing a validator by the developer" section).

oauth_validator_libraries = 'oauth_validator'

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.

Mapping users via the pg_ident.conf file

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 "Creating Users"), in the third is the name of the role in PostgreSQL.

Mapping users through a validator

It is described below in the "Writing a validator by the developer" 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="http://192.168.0.156:8080/realms/postgres-realm/.well-known/openid-configuration" scope="openid postgres" map="oauthmap"

Next, you need to set the algorithm for mapping users between Keycloak and PostgreSQL (see "User mapping between Keycloak and PostgreSQL").

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="http://192.168.0.156:8080/realms/postgres-realm/.well-known/openid-configuration" 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="http://192.168.0.156:8080/realms/postgres-realm/.well-known/openid-configuration" scope="openid postgres" delegate_ident_mapping=1

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

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 GitHub). 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[i] == '-')
            b64[i] = '+';
        else if (b64url[i] == '_')
            b64[i] = '/';
        else
            b64[i] = b64url[i];
    }

    /* 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

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;

typedef struct ValidatorModuleResult
{
   bool authorized;
   char *authn_id;
} ValidatorModuleResult;

Shutdown_cb callback

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.

discovery

[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":"http://192.168.0.156:8080/realms/postgres-realm","authorization_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/auth","token_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/token","introspection_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/token/introspect","userinfo_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/userinfo","end_session_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/logout","frontchannel_logout_session_supported":true,"frontchannel_logout_supported":true,"jwks_uri":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/certs","check_session_iframe":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/login-status-iframe.html","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":"http://192.168.0.156:8080/realms/postgres-realm/clients-registrations/openid-connect","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":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/revoke","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":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/auth/device","backchannel_token_delivery_modes_supported":["poll","ping"],"backchannel_authentication_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/ext/ciba/auth","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":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/ext/par/request","mtls_endpoint_aliases":{"token_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/token","revocation_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/revoke","introspection_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/token/introspect","device_authorization_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/auth/device","registration_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/clients-registrations/openid-connect","userinfo_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/userinfo","pushed_authorization_request_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/ext/par/request","backchannel_authentication_endpoint":"http://192.168.0.156:8080/realms/postgres-realm/protocol/openid-connect/ext/ciba/auth"},"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":"http://192.168.0.156:8080/realms/postgres-realm/device","verification_uri_complete":"http://192.168.0.156:8080/realms/postgres-realm/device?user\_code=WXAI-ZNVY","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_gafqnn72HpWY0WRwyZdQuzc5o8mE3UUAoKukxMnwDG7Yhxif2YFb_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-GDuNFo_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=http://192.168.0.156:8080/realms/postgres-realm/.well-known/openid-configuration 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 http://192.168.0.156:8080/realms/postgres-realm/device and enter the code: LJDW-RURX

We enter it in the browser [http://192.168.0.156:8080/realms/postgres-realm/device](http://192.168.0.156:8080/realms/postgres-realm/device :

Enter the code and click Submit.

Next, we see the user login window:

Enter the username and password (see "Creating users") and click on Sign In.

Next, he asks if we are ready to provide (send) such information to PostgreSQL. Click Yes.

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.