Configure OpenResty with PostgreSQL connection pooling for database integration

Update system packages

Start by updating your package manager to ensure you get the latest versions of all components.

sudo apt update && sudo apt upgrade -y

sudo dnf update -y

Install OpenResty and dependencies

Install OpenResty web server with required development tools and libraries for Lua module compilation.

sudo apt install -y software-properties-common
curl -fsSL https://openresty.org/package/pubkey.gpg | sudo gpg --dearmor -o /usr/share/keyrings/openresty.gpg
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/openresty.gpg] http://openresty.org/package/ubuntu $(lsb_release -sc) main" | sudo tee /etc/apt/sources.list.d/openresty.list
sudo apt update
sudo apt install -y openresty openresty-opm build-essential libpq-dev git

sudo dnf install -y epel-release
sudo dnf config-manager --add-repo https://openresty.org/package/rhel/openresty.repo
sudo dnf install -y openresty openresty-opm gcc gcc-c++ make postgresql-devel git

Install lua-resty-postgres module

Install the PostgreSQL connector module for OpenResty using the OpenResty Package Manager.

sudo opm get openresty/lua-resty-postgres
sudo opm get openresty/lua-resty-http
sudo opm get openresty/lua-resty-upstream-healthcheck

Create OpenResty directory structure

Set up the proper directory structure for OpenResty configuration files and Lua modules.

sudo mkdir -p /usr/local/openresty/nginx/conf/conf.d
sudo mkdir -p /usr/local/openresty/nginx/lua
sudo mkdir -p /var/log/openresty
sudo chown -R www-data:www-data /var/log/openresty

Configure main nginx configuration

Create the main OpenResty configuration file with Lua module support and connection pooling settings.

user www-data;
worker_processes auto;
error_log /var/log/openresty/error.log warn;
pid /var/run/openresty.pid;

events {
    worker_connections 1024;
    use epoll;
}

http {
    include /usr/local/openresty/nginx/conf/mime.types;
    default_type application/octet-stream;

    # Lua settings
    lua_package_path "/usr/local/openresty/lualib/?.lua;/usr/local/openresty/nginx/lua/?.lua;;";
    lua_code_cache on;
    lua_socket_pool_size 100;
    lua_socket_keepalive_timeout 60000;
    lua_socket_connect_timeout 5000;
    lua_socket_send_timeout 5000;
    lua_socket_read_timeout 5000;

    # Shared memory for connection pooling
    lua_shared_dict db_pool 10m;
    lua_shared_dict healthcheck 1m;

    # Initialize connection pool on startup
    init_by_lua_block {
        require "resty.core"
        local config = require "db_config"
        config.init()
    }

    # Logging format
    log_format main '$remote_addr - $remote_user [$time_local] "$request" '
                   '$status $body_bytes_sent "$http_referer" '
                   '"$http_user_agent" rt=$request_time uct="$upstream_connect_time" '
                   'uht="$upstream_header_time" urt="$upstream_response_time"';

    access_log /var/log/openresty/access.log main;

    # Basic settings
    sendfile on;
    tcp_nopush on;
    tcp_nodelay on;
    keepalive_timeout 65;
    types_hash_max_size 2048;
    client_max_body_size 16M;

    # Gzip compression
    gzip on;
    gzip_vary on;
    gzip_proxied any;
    gzip_comp_level 6;
    gzip_types text/plain text/css text/xml text/javascript application/javascript application/xml+rss application/json;

    # Include server configurations
    include /usr/local/openresty/nginx/conf/conf.d/*.conf;
}

Create database configuration module

Create a Lua module for PostgreSQL database configuration and connection pool management.

local _M = {}

-- Database configuration
_M.db_config = {
    host = "127.0.0.1",
    port = 5432,
    database = "webapp",
    user = "webapp_user",
    password = "secure_password_here",
    charset = "utf8",
    max_packet_size = 1024 * 1024,
    -- Connection pool settings
    pool_timeout = 60000,  -- 60 seconds
    pool_size = 50,        -- Maximum connections per worker
    keepalive_timeout = 30000, -- 30 seconds
    keepalive_requests = 1000,
    connect_timeout = 5000,    -- 5 seconds
    send_timeout = 5000,       -- 5 seconds
    read_timeout = 10000       -- 10 seconds
}

-- Health check configuration
_M.health_check = {
    interval = 30,  -- seconds
    timeout = 5,    -- seconds
    max_failures = 3,
    recovery_time = 60  -- seconds
}

function _M.init()
    ngx.log(ngx.INFO, "Database configuration initialized")
end

return _M

Create connection pool module

Implement the PostgreSQL connection pool with automatic health checks and failover capabilities.

local postgres = require "resty.postgres"
local config = require "db_config"

local _M = {}

local db_pool = ngx.shared.db_pool
local healthcheck = ngx.shared.healthcheck

-- Get database connection from pool
function _M.get_connection()
    local db = postgres:new()
    
    -- Set timeouts
    db:set_timeout(config.db_config.connect_timeout)
    
    -- Connect to database
    local ok, err = db:connect({
        host = config.db_config.host,
        port = config.db_config.port,
        database = config.db_config.database,
        user = config.db_config.user,
        password = config.db_config.password,
        charset = config.db_config.charset,
        max_packet_size = config.db_config.max_packet_size
    })
    
    if not ok then
        ngx.log(ngx.ERR, "Failed to connect to PostgreSQL: ", err)
        return nil, err
    end
    
    return db, nil
end

-- Return connection to pool
function _M.close_connection(db)
    if not db then
        return
    end
    
    -- Set keepalive to pool the connection
    local ok, err = db:set_keepalive(
        config.db_config.keepalive_timeout,
        config.db_config.pool_size
    )
    
    if not ok then
        ngx.log(ngx.ERR, "Failed to set keepalive: ", err)
        db:close()
    end
end

-- Execute query with automatic connection management
function _M.query(sql, params)
    local db, err = _M.get_connection()
    if not db then
        return nil, err
    end
    
    -- Set query timeout
    db:set_timeout(config.db_config.read_timeout)
    
    local res, err, errcode, sqlstate
    
    if params and #params > 0 then
        res, err, errcode, sqlstate = db:query(sql, params)
    else
        res, err, errcode, sqlstate = db:query(sql)
    end
    
    if not res then
        ngx.log(ngx.ERR, "Query failed: ", err, " (code: ", errcode, ", state: ", sqlstate, ")")
        db:close()  -- Close on error
        return nil, err
    end
    
    -- Return connection to pool
    _M.close_connection(db)
    
    return res, nil
end

-- Health check function
function _M.health_check()
    local db, err = _M.get_connection()
    if not db then
        ngx.log(ngx.ERR, "Health check failed - connection error: ", err)
        return false
    end
    
    local res, err = db:query("SELECT 1 as health_check")
    
    if not res then
        ngx.log(ngx.ERR, "Health check failed - query error: ", err)
        db:close()
        return false
    end
    
    _M.close_connection(db)
    ngx.log(ngx.INFO, "Database health check passed")
    return true
end

-- Get connection pool statistics
function _M.get_pool_stats()
    local stats = {}
    
    -- This would require custom implementation based on your monitoring needs
    stats.active_connections = "N/A"
    stats.total_connections = "N/A"
    stats.pool_size = config.db_config.pool_size
    
    return stats
end

return _M

Create application API module

Create a sample API module demonstrating how to use the connection pool for database operations.

local cjson = require "cjson"
local db_pool = require "db_pool"

local _M = {}

-- Helper function to send JSON response
local function send_json_response(status, data)
    ngx.status = status
    ngx.header.content_type = "application/json"
    ngx.say(cjson.encode(data))
end

-- Get users endpoint
function _M.get_users()
    local sql = "SELECT id, username, email, created_at FROM users ORDER BY id LIMIT 100"
    
    local result, err = db_pool.query(sql)
    
    if not result then
        send_json_response(500, {
            error = "Database query failed",
            message = err
        })
        return
    end
    
    send_json_response(200, {
        success = true,
        data = result,
        count = #result
    })
end

-- Get user by ID endpoint
function _M.get_user_by_id(id)
    -- Validate input
    if not id or id == "" then
        send_json_response(400, {
            error = "Invalid user ID"
        })
        return
    end
    
    local sql = "SELECT id, username, email, created_at FROM users WHERE id = $1"
    local params = {id}
    
    local result, err = db_pool.query(sql, params)
    
    if not result then
        send_json_response(500, {
            error = "Database query failed",
            message = err
        })
        return
    end
    
    if #result == 0 then
        send_json_response(404, {
            error = "User not found"
        })
        return
    end
    
    send_json_response(200, {
        success = true,
        data = result[1]
    })
end

-- Health check endpoint
function _M.health_check()
    local is_healthy = db_pool.health_check()
    
    if is_healthy then
        send_json_response(200, {
            status = "healthy",
            database = "connected",
            timestamp = ngx.time()
        })
    else
        send_json_response(503, {
            status = "unhealthy",
            database = "disconnected",
            timestamp = ngx.time()
        })
    end
end

-- Pool statistics endpoint
function _M.pool_stats()
    local stats = db_pool.get_pool_stats()
    
    send_json_response(200, {
        pool_stats = stats,
        worker_pid = ngx.worker.pid(),
        timestamp = ngx.time()
    })
end

return _M

Configure virtual host

Create a virtual host configuration that uses the PostgreSQL connection pool for API endpoints.

server {
    listen 80;
    server_name example.com www.example.com;
    
    # Security headers
    add_header X-Frame-Options "SAMEORIGIN" always;
    add_header X-XSS-Protection "1; mode=block" always;
    add_header X-Content-Type-Options "nosniff" always;
    add_header Referrer-Policy "no-referrer-when-downgrade" always;
    add_header Content-Security-Policy "default-src 'self' http: https: data: blob: 'unsafe-inline'" always;
    
    # Root directory
    root /var/www/html;
    index index.html index.htm;
    
    # API endpoints with database integration
    location /api/users {
        access_by_lua_block {
            -- Rate limiting could be added here
        }
        
        content_by_lua_block {
            local api = require "api"
            
            if ngx.var.request_method == "GET" then
                local uri_parts = ngx.re.split(ngx.var.uri, "/")
                
                if #uri_parts == 3 then  -- /api/users
                    api.get_users()
                elseif #uri_parts == 4 then  -- /api/users/{id}
                    local user_id = uri_parts[4]
                    api.get_user_by_id(user_id)
                else
                    ngx.status = 404
                    ngx.say("Endpoint not found")
                end
            else
                ngx.status = 405
                ngx.header.allow = "GET"
                ngx.say("Method not allowed")
            end
        }
    }
    
    # Health check endpoint
    location /health {
        access_log off;
        
        content_by_lua_block {
            local api = require "api"
            api.health_check()
        }
    }
    
    # Pool statistics endpoint (restrict access in production)
    location /admin/pool-stats {
        allow 127.0.0.1;
        allow 10.0.0.0/8;
        deny all;
        
        content_by_lua_block {
            local api = require "api"
            api.pool_stats()
        }
    }
    
    # Static content
    location / {
        try_files $uri $uri/ =404;
    }
    
    # Error pages
    error_page 404 /404.html;
    error_page 500 502 503 504 /50x.html;
    
    location = /50x.html {
        root /usr/share/nginx/html;
    }
}

Create systemd service file

Set up a systemd service file for OpenResty with proper dependencies and security settings.

[Unit]
Description=OpenResty Web Server
Documentation=https://openresty.org/
After=network.target remote-fs.target nss-lookup.target
Wants=network.target

[Service]
Type=forking
PIDFile=/var/run/openresty.pid
ExecStartPre=/usr/local/openresty/nginx/sbin/nginx -t
ExecStart=/usr/local/openresty/nginx/sbin/nginx
ExecReload=/bin/sh -c "/bin/kill -s HUP $(/bin/cat /var/run/openresty.pid)"
ExecStop=/bin/sh -c "/bin/kill -s TERM $(/bin/cat /var/run/openresty.pid)"
KillMode=mixed
PrivateTmp=true
Restart=on-failure
RestartSec=5s

# Security settings
NoNewPrivileges=yes
PrivateDevices=yes
ProtectHome=yes
ProtectSystem=strict
ReadWritePaths=/var/log/openresty /var/run /tmp

[Install]
WantedBy=multi-user.target

Set up database and test user

Create a test PostgreSQL database and user for demonstrating the connection pool.

sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql

sudo dnf install -y postgresql postgresql-server postgresql-contrib
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

sudo -u postgres psql -c "CREATE DATABASE webapp;"
sudo -u postgres psql -c "CREATE USER webapp_user WITH PASSWORD 'secure_password_here';"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE webapp TO webapp_user;"
sudo -u postgres psql -d webapp -c "CREATE TABLE users (id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE, email VARCHAR(100), created_at TIMESTAMP DEFAULT NOW());"
sudo -u postgres psql -d webapp -c "INSERT INTO users (username, email) VALUES ('admin', 'admin@example.com'), ('user1', 'user1@example.com');"
sudo -u postgres psql -d webapp -c "GRANT ALL PRIVILEGES ON TABLE users TO webapp_user;"
sudo -u postgres psql -d webapp -c "GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO webapp_user;"

Create web directory and sample page

Set up the web directory structure with proper permissions and a sample HTML page.

sudo mkdir -p /var/www/html
sudo chown -R www-data:www-data /var/www/html
echo '


    


    <h1>OpenResty PostgreSQL Connection Pool Demo</h1>
    <p>API Endpoints:</p>
    <ul>
        <li><a href="/api/users">GET /api/users</a> - List all users</li>
        <li><a href="/api/users/1">GET /api/users/{id}</a> - Get user by ID</li>
        <li><a href="/health">GET /health</a> - Health check</li>
    </ul>

' | sudo tee /var/www/html/index.html

Enable and start services

Enable and start both PostgreSQL and OpenResty services with automatic startup on boot.

sudo systemctl daemon-reload
sudo systemctl enable openresty
sudo systemctl start openresty
sudo systemctl status openresty

Set up SSL/TLS encryption

Configure SSL certificates for secure database connections and HTTPS endpoints.

sudo mkdir -p /etc/openresty/ssl
sudo openssl req -x509 -nodes -days 365 -newkey rsa:2048 \
  -keyout /etc/openresty/ssl/server.key \
  -out /etc/openresty/ssl/server.crt \
  -subj "/C=US/ST=State/L=City/O=Organization/CN=example.com"

server {
    listen 443 ssl http2;
    server_name example.com www.example.com;
    
    # SSL configuration
    ssl_certificate /etc/openresty/ssl/server.crt;
    ssl_certificate_key /etc/openresty/ssl/server.key;
    ssl_protocols TLSv1.2 TLSv1.3;
    ssl_ciphers ECDHE-RSA-AES256-GCM-SHA512:DHE-RSA-AES256-GCM-SHA512:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384;
    ssl_prefer_server_ciphers off;
    ssl_session_cache shared:SSL:10m;
    ssl_session_timeout 10m;
    
    # Enhanced security headers
    add_header Strict-Transport-Security "max-age=31536000; includeSubDomains; preload" always;
    add_header X-Frame-Options "SAMEORIGIN" always;
    add_header X-XSS-Protection "1; mode=block" always;
    add_header X-Content-Type-Options "nosniff" always;
    
    # Include the same location blocks as HTTP config
    root /var/www/html;
    index index.html index.htm;
    
    # All location blocks from webapp.conf would go here
    include /usr/local/openresty/nginx/conf/locations.conf;
}

Configure rate limiting

Implement rate limiting to protect against abuse and ensure fair resource usage.

local limit_req = require "resty.limit.req"

local _M = {}