NGINX PostgreSQL Module: Direct Database Queries

What if NGINX could talk to your PostgreSQL database directly — no PHP, no Python, no Node.js? That is exactly what the NGINX PostgreSQL module (ngx_postgres) does. It turns NGINX into a lightweight database gateway. It executes SQL queries, returns results over HTTP, and handles connection pooling. No application processes needed.

For DevOps engineers building internal APIs, health checks, or data-driven routing, this module eliminates an entire middleware layer. Configure everything in nginx.conf instead of deploying a full application stack.

How the NGINX PostgreSQL Module Works

The ngx_postgres module operates as an NGINX upstream module. When a request matches a location with postgres_pass, NGINX connects to PostgreSQL. It creates a new connection or reuses one from the keepalive pool. Then it runs the SQL query and returns the result.

The request flow looks like this:

1. Client sends an HTTP request to NGINX
2. NGINX matches the location with postgres_pass
3. NGINX executes the SQL query (with variable interpolation)
4. PostgreSQL returns the result set
5. NGINX formats the output and sends it to the client

NGINX’s non-blocking event loop handles this efficiently. A single worker can manage thousands of concurrent database requests. The built-in connection pool (postgres_keepalive) reuses connections across requests. This avoids TCP handshake and authentication overhead on every request.

This approach resembles how the NGINX SQLite module integrates a database into NGINX. The key difference: ngx_postgres connects to an external PostgreSQL server. SQLite runs in-process.

Installation

RHEL, CentOS, AlmaLinux, Rocky Linux

sudo dnf install https://extras.getpagespeed.com/release-latest.rpm
sudo dnf install nginx-module-postgres

Then add this line to the top of /etc/nginx/nginx.conf:

load_module modules/ngx_postgres_module.so;

Debian and Ubuntu

First, set up the GetPageSpeed APT repository, then install:

sudo apt-get update
sudo apt-get install nginx-module-postgres

On Debian/Ubuntu, the package loads the module automatically. No load_module directive is needed.

Find package details on the RPM module page and APT module page.

SELinux Configuration (RHEL-based Systems)

If SELinux is enabled, NGINX needs permission to connect to PostgreSQL. Without this, you get 502 Bad Gateway and “Permission denied” errors. Run:

sudo setsebool -P httpd_can_network_connect_db 1

The -P flag makes this setting persist across reboots.

Configuration Reference

The NGINX PostgreSQL module provides ten directives and four built-in variables.

postgres_server

Syntax: postgres_server address[:port] dbname=name user=user password=pass
Default: none
Context: upstream

Defines the PostgreSQL server connection. The default port is 5432. Place this inside an upstream block:

upstream database {
    postgres_server 127.0.0.1 dbname=myapp user=webapp password=secret;
}

The dbname, user, and password parameters are required. Add multiple directives for basic load distribution.

postgres_keepalive

Syntax: postgres_keepalive off | max=count [mode=single|multi] [overflow=ignore|reject]
Default: max=10 mode=single overflow=ignore
Context: upstream

Configures the connection pool:

• max — keepalive connections per worker (default: 10)
• mode — single for one server (default) or multi for several
• overflow — ignore (default) allows requests but closes the connection; reject returns 503

upstream database {
    postgres_server 127.0.0.1 dbname=myapp user=webapp password=secret;
    postgres_keepalive max=20 mode=single overflow=reject;
}

To disable keepalive entirely:

postgres_keepalive off;

postgres_pass

Syntax: postgres_pass upstream_name
Default: none
Context: location, if in location

Routes requests to the specified PostgreSQL upstream. Works like proxy_pass. The name can contain NGINX variables:

location /db {
    postgres_pass database;
    postgres_query "SELECT 1";
}

postgres_query

Syntax: postgres_query [methods] sql_query
Default: none
Context: http, server, location, if in location

Defines the SQL query. Supports NGINX variable interpolation. Use captured groups, arguments, and headers in your SQL. Specify HTTP methods for per-verb queries:

location /api/items {
    postgres_pass database;

    # GET requests read data
    postgres_query GET  "SELECT * FROM items";

    # POST requests insert data
    postgres_query POST "INSERT INTO items (name) VALUES ('$arg_name') RETURNING *";

    # Default query for all other methods
    postgres_query      "SELECT 'method not implemented'";

    postgres_output text;
}

Supported methods: GET, HEAD, POST, PUT, DELETE, PATCH, OPTIONS, MKCOL, COPY, MOVE, PROPFIND, PROPPATCH, LOCK, and UNLOCK.

Important: Never interpolate user input directly into SQL. Use postgres_escape (below) to prevent injection.

postgres_rewrite

Syntax: postgres_rewrite [methods] condition [=]status_code
Default: none
Context: http, server, location, if in location

Rewrites the HTTP status code based on query results. Essential for RESTful APIs. Available conditions:

• no_changes — no rows affected (for INSERT, UPDATE, DELETE)
• changes — at least one row affected
• no_rows — empty result set
• rows — at least one row returned

Prefix the code with = to send the response body instead of the error page:

location ~ ^/user/(?<user_id>\d+)$ {
    postgres_pass     database;
    postgres_query    "SELECT * FROM users WHERE id=$user_id";
    postgres_output   text;
    postgres_rewrite  no_rows 404;  # Return 404 if user not found
}

Combine method-specific rewrites like this:

location /api/users {
    postgres_pass database;
    postgres_query    POST "INSERT INTO users (name) VALUES ('test') RETURNING *";
    postgres_rewrite  POST changes 201;     # 201 Created on success
    postgres_rewrite  POST no_changes 409;  # 409 Conflict if nothing inserted
    postgres_output   text;
}

postgres_output

Syntax: postgres_output rds | text | value | binary_value | none
Default: rds
Context: http, server, location, if in location

Controls the output format:

• rds — Resty DBD Stream binary (default). For use with ngx_rds_json.
• text — newline-separated plain text values.
• value — single scalar (first row, first column). Must return exactly one row and column.
• binary_value — same as value but raw binary.
• none — no output. Use with postgres_set to extract variables.

For most APIs, text or value works best.

postgres_set

Syntax: postgres_set $variable row column [optional|required]
Default: none
Context: http, server, location

Extracts a value from the result set into an NGINX variable. Rows and columns start at 0. You can use column names:

location /user-info {
    postgres_pass     database;
    postgres_query    "SELECT name, email FROM users WHERE id=1";
    postgres_set      $user_name  0 name required;
    postgres_set      $user_email 0 email optional;
    postgres_output   text;
    add_header X-User-Name $user_name;
}

With required, missing or NULL values trigger a 500 error. With optional (default), the variable stays empty.

Note that postgres_set works with any output mode. Use postgres_output none when you only need the variables and do not want to send the query result to the client.

postgres_escape

Syntax: postgres_escape $escaped [=]$unescaped
Default: none
Context: http, server, location

Escapes and quotes a string for safe SQL use. Your primary defense against SQL injection:

location /search {
    postgres_escape   $safe_query $arg_q;
    postgres_pass     database;
    postgres_query    "SELECT * FROM users WHERE name=$safe_query";
    postgres_output   text;
    postgres_rewrite  no_rows 404;
}

By default, empty strings become SQL NULL. Prefix with = to get '' instead:

postgres_escape $safe_value =$arg_value;

postgres_connect_timeout

Syntax: postgres_connect_timeout timeout
Default: 10s
Context: http, server, location

Timeout for establishing a PostgreSQL connection:

postgres_connect_timeout 5s;

postgres_result_timeout

Syntax: postgres_result_timeout timeout
Default: 30s
Context: http, server, location

Timeout for receiving the query result:

postgres_result_timeout 60s;

Built-in Variables

Four read-only variables are available after query execution:

Use them for logging and debugging:

log_format postgres '$remote_addr - $request - query: $postgres_query '
                    'rows: $postgres_rows affected: $postgres_affected';

access_log /var/log/nginx/postgres.log postgres;

Practical Examples

Example 1: Simple Data API

Return all records from a table as plain text:

upstream database {
    postgres_server 127.0.0.1 dbname=testdb user=nginx password=secret;
}

server {
    listen 80;

    location /users {
        postgres_pass   database;
        postgres_query  "SELECT * FROM users";
        postgres_output text;
    }
}

A GET /users request produces newline-separated values:

1
Alice
alice@example.com
2
Bob
bob@example.com

Example 2: RESTful CRUD Endpoint

Map HTTP methods to SQL operations:

location /api/users {
    postgres_pass     database;

    postgres_query    GET  "SELECT * FROM users";
    postgres_query    POST "INSERT INTO users (name, email) VALUES ('NewUser', 'new@test.com') RETURNING *";
    postgres_rewrite  POST changes 201;
    postgres_rewrite  POST no_changes 409;

    postgres_output   text;
}

• GET /api/users returns all users with HTTP 200
• POST /api/users creates a user and returns HTTP 201

Example 3: SQL Injection Prevention

Use regex captures and postgres_escape for safe queries:

# Safe: user_id is constrained to digits by the regex
location ~ ^/user/(?<user_id>\d+)$ {
    postgres_pass     database;
    postgres_query    "SELECT * FROM users WHERE id=$user_id";
    postgres_output   text;
    postgres_rewrite  no_rows 404;
}

# Safe: user input is escaped before interpolation
location /search {
    postgres_escape   $safe_q $arg_q;
    postgres_pass     database;
    postgres_query    "SELECT * FROM users WHERE name=$safe_q";
    postgres_output   text;
    postgres_rewrite  no_rows 404;
}

SQL injection attempts like /search?q=Alice' OR '1'='1 are blocked. The postgres_escape directive quotes the input properly. The query returns no results instead of leaking data.

Example 4: Health Check Endpoint

A lightweight database health check without application code:

location /healthz {
    postgres_pass              database;
    postgres_query             "SELECT 1";
    postgres_output            value;
    postgres_connect_timeout   2s;
    postgres_result_timeout    2s;
}

Returns 1 with HTTP 200 when the database is reachable. Returns 502 when it is down. Load balancers can poll this endpoint.

Example 5: Extracting Values into Variables

Use postgres_set to capture query results for headers or logging:

location /user-header {
    postgres_pass     database;
    postgres_query    "SELECT name FROM users WHERE id=1";
    postgres_set      $first_user_name 0 0 required;
    postgres_output   value;
    add_header X-User-Name $first_user_name;
}

The response includes both the body and a custom header:

HTTP/1.1 200 OK
X-User-Name: Alice
Content-Type: application/octet-stream

Alice

This works well with the NGINX encrypted session module for encrypting values before passing them downstream.

Performance Considerations

The NGINX PostgreSQL module offers major advantages for simple database endpoints.

Connection pooling reduces overhead. Each worker maintains a keepalive pool. Connections persist for the worker’s lifetime. This eliminates per-request TCP and auth costs. Total connections equal max × worker_processes. Plan max_connections accordingly.

No process spawning. Unlike PHP-FPM or Python WSGI, NGINX handles requests in its event loop. No child processes, interpreters, or garbage collectors.

Minimal memory footprint. Memory is allocated only for the query and result. No framework overhead, ORM layer, or middleware chain.

Tuning recommendations:

• Match postgres_keepalive max= to your concurrent request rate
• Use overflow=reject to fail fast when the pool is full
• Monitor pg_stat_activity to verify connection counts

For rate-limiting database endpoints, pair this with the NGINX dynamic limit req module.

Security Best Practices

Always Escape User Input

Use postgres_escape for any user-supplied value. Regex captures like (?<id>\d+) are safe because the pattern blocks injection. For free-form text, always escape:

# DANGEROUS: raw user input in query
location /search-bad {
    postgres_query "SELECT * FROM items WHERE name='$arg_q'";
}

# SAFE: escaped user input
location /search-good {
    postgres_escape $safe_q $arg_q;
    postgres_query "SELECT * FROM items WHERE name=$safe_q";
}

Use a Dedicated Database User

Create a PostgreSQL user with minimal privileges:

CREATE USER nginx_readonly WITH PASSWORD 'strong_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO nginx_readonly;

Restrict Network Access

Configure pg_hba.conf to accept connections only from NGINX:

host    myapp    nginx_readonly    127.0.0.1/32    md5

Limit Query Timeouts

Prevent slow queries from blocking connections:

postgres_connect_timeout 3s;
postgres_result_timeout  5s;

The NGINX keyval module can store tokens or session state in-memory alongside your database lookups.

Troubleshooting

502 Bad Gateway

NGINX cannot connect to PostgreSQL. Check:

1. PostgreSQL not running: systemctl status postgresql
2. SELinux blocking: setsebool -P httpd_can_network_connect_db 1
3. Auth failure: Verify pg_hba.conf allows the NGINX user
4. Wrong credentials: Check dbname, user, and password

Check the error log:

tail -f /var/log/nginx/error.log

500 Internal Server Error

Common causes:

• postgres_output value with multiple rows: Use text instead
• postgres_set required with missing data: Use optional or fix the query
• SQL syntax error: Check $postgres_query in logs

Connection Pool Exhaustion

Seeing 503 with overflow=reject? The pool is full. Increase max or optimize query speed.

Debugging Queries

Log the actual SQL sent to PostgreSQL:

log_format pg_debug '$request - query: "$postgres_query" rows=$postgres_rows';
access_log /var/log/nginx/pg-debug.log pg_debug;

When to Use This Module

The NGINX PostgreSQL module excels at simple, read-heavy endpoints:

• Health check and readiness probes
• Internal microservice data lookups
• Database-driven routing decisions
• Simple read-only APIs (user lookups, config values)
• Authentication subrequests (credential checks)

For business logic, data transformation, or complex error handling, use an application server. This module does not replace your app layer. It removes unnecessary middleware when the response maps directly to a SQL query.

Conclusion

The NGINX PostgreSQL module builds lightweight, high-performance database endpoints directly within NGINX. It cuts latency, simplifies deployments, and reduces resource usage by eliminating middleware.

For the source code, visit the ngx_postgres GitHub repository. Packages are on the GetPageSpeed RPM repository and APT repository.