Your Cheat Sheet for pgtle Installation & Configuration

This guide provides a rapid overview of the critical steps to get pgtle installed and configured in your PostgreSQL environment, as detailed in Part 2: The Environment Setup – Preparing for pgtle Glory.

If you’re here to conquer the entire saga, a true hero’s journey, then make sure to check out our Series Overview Page for all the other exciting chapters and quick-reference guides.

For comprehensive details on pgtle installation and pgtle configuration, you can always refer to the official documentation:

• GitHub pg_tle Documentation: https://github.com/aws/pg_tle/blob/main/docs/01_install.md
• AWS RDS pg_tle User Guide: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_trusted_language_extension.html

1. Prerequisites: Essential Tools for pgtle Setup

Ensure you have these tools ready on your Ubuntu Server 24.04 LTS for a smooth pgtle setup:

• PostgreSQL 11 or later (already installed).
• git:

  sudo apt update
  sudo apt install git
  
  

• PostgreSQL Development Files: (Replace 16 with your PostgreSQL version). These are vital for pgtle compilation.

  sudo apt update
  sudo apt install postgresql-server-dev-16
  
  

• pg_config: Installed with PostgreSQL Development Files, used for pgtle installation.

2. pgtle Installation Steps

Step 1: Download & Compile pgtle

This step is crucial for getting pgtle installed on your system.

1. Clone the repository:

   git clone https://github.com/aws/pg_tle.git
   cd pg_tle
   
   

2. Compile and Install:

   make
   sudo make install
   
   

Step 2: Configure shared_preload_libraries in postgresql.conf

This is a critical part of the pgtle setup to ensure PostgreSQL loads the extension.

1. Edit postgresql.conf: (Replace 16 with your PostgreSQL version).

   sudo vi /etc/postgresql/16/main/postgresql.conf
   
   

2. Add pgtle to shared_preload_libraries:

   # Before:
   # shared_preload_libraries = 'pg_stat_statements,pgaudit'
   
   # After
   shared_preload_libraries = 'pg_stat_statements,pgaudit,pg_tle'
   
   

3. Restart PostgreSQL Server: This is essential for the pgtle configuration changes to take effect.

   #For Debian based systems
   sudo pg_ctlcluster 16 main restart
   
   # For systemd-based systems (most Linux distributions)
   sudo systemctl restart postgresql
   
   

Step 3: Create the pgtle Extension in Your Database

This finalizes the pgtle installation within your chosen database, making its functions available.

1. Connect to psql as superuser:

   psql
   
   

2. Create the extension:

   CREATE EXTENSION pgtle;
   
   

3. Verify installation:

   \dx
   
   

3. pgtle Hooks Overview

pgtle hooks are powerful interception points for custom code, central to advanced pgtle configuration.

• password_check Hook (passcheck):
  • • Purpose: Intercepts ALTER USER ... PASSWORD or CREATE USER ... PASSWORD.
    • Use: Custom password validation (length, complexity, history).
  • Function Signature:

    passcheck_hook(
        username TEXT,                    -- The name of the role setting the password.
        password TEXT,                    -- The password itself (plaintext or hashed).
        password_type pgtle.password_types, -- The format of the 'password' argument. Can be:
                                          --   - PASSWORD_TYPE_PLAINTEXT: The password is in plain text.
                                          --   - PASSWORD_TYPE_MD5: The password is MD5 hashed.
                                          --   - PASSWORD_TYPE_SCRAM_SHA_256: The password is SCRAM-SHA-256 hashed.
        valid_until TIMESTAMPTZ,          -- If set, the time until the password on the account no longer works.
        valid_null BOOLEAN                -- If true, 'valid_until' is set to NULL.
    ) RETURNS VOID -- Your function should return VOID. Raise an error to reject the password.
    
    

• client_authentication Hook (clientauth):
  • Purpose: Intercepts client connection attempts before standard authentication.
  • Use: Custom access control, rate limiting, connection filtering.
  • Function Signature:

    clientauth_hook(
        port pgtle.clientauth_port_subset, -- An object containing a subset of Port details:
                                           --   - noblock (BOOLEAN)
                                           --   - remote_host (TEXT)
                                           --   - remote_hostname (TEXT)
                                           --   - remote_hostname_resolv (INTEGER)
                                           --   - remote_hostname_errcode (INTEGER)
                                           --   - database_name (TEXT)
                                           --   - user_name (TEXT)
                                           --   - application_name (TEXT)
        status INTEGER                     -- Connection status code:
                                           --   - 0: Successful connection (so far)
                                           --   - -1: Connection error
    ) RETURNS TEXT OR VOID -- Return TEXT to reject with a message, or VOID to allow.
    
    

4. Key pgtle Configuration Parameters (in postgresql.conf)

These postgresql.conf pgtle parameters control pgtle‘s behavior and hook activation. Proper pgtle configuration is essential for security.

• pgtle.enable_password_check:
  • Default: off
  • What it controls: Determines if the passcheck hook mechanism is active.
    • off: Disables the passcheck hook entirely. This is the default setting.
    • on: Enables the passcheck hook, but only calls your custom function if one is registered (via pgtle.register_feature with the feature name 'pgtle.password_check_functions').
    • require: Requires a passcheck hook function to be defined and registered. If no function is found, password changes will be rejected. Use with caution!
  • Context: SIGHUP (meaning you can change this value and apply it without a full database restart by reloading the configuration, e.g., pg_ctl reload or sudo systemctl reload postgresql).
• pgtle.passcheck_db_name (Available in pgtle versions 1.3.0 and higher)
  • Default: "" (empty string)
  • What it controls: If set to a specific database name, pgtle will query that database for the registered passcheck function, regardless of which database the client is connected to. This is useful for centralizing your password validation logic.
  • Warning: If this parameter is set, passcheck functions are executed as a superuser! Define your functions very carefully and be aware of potential security risks from malicious code.
  • Context: SIGHUP
• pgtle.enable_clientauth:
  • Default: off
  • What it controls: Determines if the clientauth hook mechanism is active.
    • off: Disables the clientauth hook. This is the default.
    • on: Enables the clientauth hook, but only calls your custom function if one is registered (via pgtle.register_feature with the feature name 'pgtle.client_authentication_functions').
    • require: Requires a clientauth hook function to be defined and registered. Warning: connections will be rejected if no functions are registered to this hook!
  • Context: SIGHUP. Important Note: A full database restart is typically needed to enable the clientauth feature (switching from off to on or require), as it involves registering background workers. Disabling it (e.g., on to off) doesn’t require a restart, but restarting is still recommended to free up worker resources.
• pgtle.clientauth_db_name:
  • Default: postgres
  • What it controls: Specifies which database to query for the registered clientauth functions. All clientauth functions should be created and registered in this central database. When a client connects to any database in the cluster, the functions in this specified database will be executed.
  • Context: Postmaster (requires a full database restart to apply changes).
• pgtle.clientauth_num_parallel_workers:
  • Default: 1
  • Minimum: 1
  • Maximum: min(max_connections, 256)
  • What it controls: The number of background workers pgtle will use to handle client_authentication requests in parallel. Increase this value to handle large connection bursts or if your clientauth functions are expected to run for longer. Ensure this value is less than PostgreSQL’s max_worker_processes to leave room for other processes.
  • Context: Postmaster (requires a full database restart to apply changes).
• pgtle.clientauth_users_to_skip:
  • Default: "" (empty string)
  • What it controls: A comma-separated list of usernames that will bypass the clientauth hook. If a connecting user is on this list, clientauth functions will not be executed for their connection. Useful for administrative users or specific applications.
  • Context: SIGHUP
• pgtle.clientauth_databases_to_skip:
  • Default: "" (empty string)
  • What it controls: A comma-separated list of database names that will bypass the clientauth hook. If a client connects to a database on this list, clientauth functions will not be executed.
  • Context: SIGHUP

Understanding these detailed parameters gives you comprehensive control over your pgtle environment, allowing you to secure it further and precisely dictate how its powerful hooks behave. For our password validation journey, the primary focus will be on the password_check hook and how we register our custom functions to it.

Are you ready to truly make your PostgreSQL database a fortress? The next part will be a deep dive into the code that will validate your passwords with the vigilance of a Jedi Master! Stay tuned!