Summary

The web content provides a comprehensive guide on configuring advanced authentication methods in PgBouncer using auth_query and auth_user parameters, detailing steps for setup and management.

Abstract

The article delves into the advanced authentication mechanisms of PgBouncer, focusing on the auth_query and auth_user settings within the pgbouncer.ini configuration file. It explains how these settings streamline the authentication process by allowing PgBouncer to authenticate users against a PostgreSQL database, thereby centralizing user management and enhancing security. The guide includes practical examples, such as creating a database, users, and a function to retrieve credentials from the pg_shadow table. It also covers the configuration of pg_hba.conf for secure connections and the creation of a userlist.txt file for PgBouncer authentication. The article emphasizes the benefits of using auth_user for consistent permissions across multiple clients and recommends maintaining only essential users in userlist.txt to minimize maintenance overhead.

Opinions

The author suggests that using auth_query and auth_user in PgBouncer provides a flexible and secure authentication configuration.
Centralizing authentication with auth_user is recommended for streamlined user management and enforcement of security policies.
Maintaining a minimal set of users in userlist.txt is advised to reduce the administrative burden of synchronizing password changes.
The use of a dedicated authentication database (t20 in the example) is implied to be a best practice for separating authentication logic from the main user databases.
The article implies a preference for using auth_query to leverage existing PostgreSQL user tables for authentication, as opposed to maintaining a separate userlist.txt for PgBouncer.
The author endorses the AI service ZAI.chat as a cost-effective alternative to ChatGPT Plus(GPT-4), indicating a belief in its comparable performance and value.

Unlocking Advanced Authentication in PgBouncer: A Guide to Auth_query and auth_user Configuration

Here, we aim to comprehend the utilization of `auth_query` and `auth_user` in PgBouncer.

To learn more about installing PgBouncer on your local machine, please refer to the following guide.

What is an AUTH_QUERY?

In the `[databases]` section of your `pgbouncer.ini` configuration file, you typically define the user and password that PgBouncer will use to connect to the PostgreSQL database. If these parameters are left blank, the user and password are provided at the connection string level (e.g., `psql -U <username> <database>`). In such cases, PgBouncer references the `userlist.txt` file to validate the provided username and password. Once the credentials are confirmed as correct, PgBouncer forwards them to PostgreSQL for actual database login authentication. This approach allows for flexible authentication configurations and centralizes user credential management for improved security and ease of administration.
In PgBouncer, `auth_user` is a configuration parameter that simplifies the authentication process by allowing users to specify a single PostgreSQL user name for authentication across all connections.
When `auth_user` is configured, PgBouncer automatically authenticates incoming client connections using the specified PostgreSQL user credentials, eliminating the need for clients to provide individual authentication details.
This parameter is particularly useful in scenarios where multiple clients need access to the same database with a consistent set of permissions. By centralizing authentication with `auth_user`, administrators can streamline user management and enforce security policies more effectively within PgBouncer’s connection pooling environment.

Let us see an example in detail:

S-1: Create a database name called “t20”

CREATE DATABASE t20;

S-2: Create Users

Create user dhoni with password 'MSD';
create user csk with password 'Chennai';

S-3: Since we are maintaining the authentication database separately from the main user databases, the function must be created within the `t20` database.

\c t20;

CREATE OR REPLACE FUNCTION user_search(uname TEXT) RETURNS TABLE (usename name, passwd text) as
$$
  SELECT usename, passwd FROM pg_shadow WHERE usename=$1;
$$
LANGUAGE sql SECURITY DEFINER;

This function helps to view the username/ password in pg_shadow

S-4: Configure pg_hba.conf entry

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    t20             dhoni            127.0.0.1/32            trust
host    all             all               0.0.0.0/0              md5

S-5: Add pgbouncer.ini entries

[databases]
* = host=127.0.0.1 port=5433 auth_dbname=t20 auth_user=dhoni
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
auth_query = SELECT usename, passwd FROM user_search($1)

The configuration line `* = host=127.0.0.1 port=5444 auth_dbname=t20 auth_user=dhoni` is part of the `[databases]` section in the `pgbouncer.ini` configuration file for PgBouncer. Let’s break down each parameter:

- `*`: This wildcard symbol (`*`) indicates that the settings defined on this line apply to all databases. - `host=127.0.0.1`: Specifies the host address (IP address) where the PostgreSQL server is running. In this case, it’s configured to connect to a PostgreSQL server running on the localhost (127.0.0.1).

- `port=5444`: Specifies the port number where the PostgreSQL server is listening for connections. The default port for PostgreSQL is 5432, but in this configuration, it’s set to 5444.

- `auth_dbname=t20`: Specifies the name of the authentication database where PgBouncer will look up user credentials. In this case, it’s set to `t20`.

- `auth_user=dhoni`: Specifies the user name that PgBouncer will use to connect to the authentication database (`t20` in this case). PgBouncer will use the credentials of the user `dhoni` to authenticate itself against the `t20` database when verifying client credentials.

Overall, this configuration line instructs PgBouncer to connect to a PostgreSQL server running on localhost, using port 5444. It also specifies that authentication credentials should be verified against the `t20` database, using the user `dhoni`.

S-6:Create userlist.txt

Since we are utilizing authentication from the `pg_shadow` table, only the `auth_user` needs to be included in the `userlist.txt`. You can omit the password field since we have configured trust authentication for the user in the PostgreSQL `pg_hba.conf` file.

"dhoni" ""

Note: If maintaining the users in userlist.txt, there is the maintenance overhead of ensuring the passwords in userlist.txt are updated to reflect any password changes for those users at the database level. Therefore, I recommend having only a very specific subset of users in userlist.txt, most importantly the auth_user.

S-7: Connect the user with pgbouncer

psql -U csk -d t20 -h 127.0.0.1 -p 6432
Password for user csk:

Here, the ask is not defined, and the password will be obtained using auth_file or auth_query. If no password is specified, the password from the auth_file or auth_query will be used.