Staging ButItWorkedLocal

Tag: centos stream

  • PostgreSQL pg_hba.conf Connection Authorization Failed on CentOS Stream / Rocky Linux

    Troubleshoot and resolve 'connection authorization failed' errors in PostgreSQL on CentOS Stream and Rocky Linux by correctly configuring pg_hba.conf and related settings.

    When working with PostgreSQL on CentOS Stream or Rocky Linux, encountering a "connection authorization failed" error indicates that the database server successfully received your connection request but explicitly denied it based on its access control rules. This guide provides a comprehensive, expert-level approach to diagnose and resolve this common issue, ensuring your applications and users can connect securely.

    Symptom & Error Signature

    The primary symptom is an inability to connect to your PostgreSQL database, typically from a client application, a command-line psql utility, or another server. You will usually see a FATAL error message.

    Typical psql command line error:

    $ psql -h your_db_host -U your_db_user -d your_db_name
psql: FATAL:  connection authorization failed for user "your_db_user"
psql: FATAL:  no pg_hba.conf entry for host "your_client_ip", user "your_db_user", database "your_db_name", no encryption

    Common application error (e.g., Python with psycopg2):

    # Example output from a Python application attempting to connect
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.OperationalError: FATAL:  connection authorization failed for user "web_app_user"
FATAL:  no pg_hba.conf entry for host "192.168.1.100", user "web_app_user", database "webapp_db"

    PostgreSQL server log entries (found in /var/lib/pgsql/data/log/postgresql-*.log or journalctl -u postgresql-1X):

    202X-XX-XX XX:XX:XX UTC [12345] LOG:  connection received: host=192.168.1.100 port=54321
202X-XX-XX XX:XX:XX UTC [12345] FATAL:  no pg_hba.conf entry for host "192.168.1.100", user "web_app_user", database "webapp_db", no encryption
```

    Root Cause Analysis

    The "connection authorization failed" error almost exclusively points to an incorrect or missing entry in PostgreSQL's Host-Based Authentication (HBA) configuration file, pg_hba.conf. This file controls which hosts are allowed to connect, which users they can connect as, which databases they can access, and what authentication method is required.

    The underlying reasons typically fall into one of these categories:

    1. Missing pghba.conf Entry: The most common cause. There is no rule in pghba.conf that matches the incoming connection's parameters (source IP, user, database).
    2. Incorrect pg_hba.conf Entry: An existing entry is present, but one or more of its fields (e.g., source IP, user, database, authentication method) do not precisely match the connection attempt.
    3. Incorrect Order of Rules: pg_hba.conf rules are processed sequentially from top to bottom. The first rule that matches the connection attempt is used. If a broad, less secure rule appears before a more specific, secure rule, it might inadvertently allow or deny connections in unexpected ways.
    4. Incorrect Authentication Method: The pg_hba.conf entry specifies an authentication method (e.g., scram-sha-256, md5, trust, peer, ident) that doesn't match the client's provided credentials or the server's configured user password.
    5. * scram-sha-256: The modern, recommended secure password-based authentication.
    6. * md5: An older, less secure password-based authentication, still widely used.
    7. * trust: Allows anyone to connect without a password (highly insecure for non-local connections).
    8. * peer: Used for local connections where the operating system user matches the database user.
    9. * ident: Similar to peer, relies on an ident server on the client for authentication.
    10. listenaddresses Misconfiguration: While this usually results in "connection refused," if listenaddresses in postgresql.conf is set to localhost or 127.0.0.1 and a remote client tries to connect, the connection will not even reach the pg_hba.conf stage for remote IP addresses. It's essential to ensure PostgreSQL is listening on the correct network interfaces (e.g., * for all, or specific IP addresses).
    11. Incorrect Database/User Permissions: Even if pghba.conf allows the connection, the user might not have CONNECT privileges on the requested database or USAGE on specific schemas, leading to application errors after authentication. This is different from the pghba.conf error but often confused.

    Step-by-Step Resolution

    Follow these steps carefully to diagnose and resolve the pg_hba.conf connection authorization error.

    1. Locate pg_hba.conf and postgresql.conf

    First, you need to find the correct configuration files. The location can vary slightly depending on the PostgreSQL version and installation method.

    # Log in as the postgres user (or use sudo) to execute psql commands
sudo -u postgres psql -c 'SHOW hba_file;'
sudo -u postgres psql -c 'SHOW config_file;'

    Common locations on CentOS Stream / Rocky Linux for PostgreSQL 12-16:

    • pghba.conf: /var/lib/pgsql/data/pghba.conf (for older versions/manual setup) or /var/lib/pgsql/1X/data/pg_hba.conf (where 1X is your PostgreSQL major version, e.g., 15).
    • postgresql.conf: /var/lib/pgsql/data/postgresql.conf or /var/lib/pgsql/1X/data/postgresql.conf.

    [!NOTE] On modern CentOS/Rocky systems, PostgreSQL is often installed via dnf, and the data directory is version-specific (e.g., /var/lib/pgsql/15/data).

    2. Backup Original Configuration Files

    Before making any changes, always back up your configuration files.

    PG_VERSION=$(sudo -u postgres psql -t -P format=unaligned -c 'SHOW hba_file;' | cut -d'/' -f5) # Extracts '15' from '/var/lib/pgsql/15/data/pg_hba.conf'

    sudo cp ${PGCONFIGDIR}/pghba.conf ${PGCONFIGDIR}/pghba.conf.bak.$(date +%F-%H%M) sudo cp ${PGCONFIGDIR}/postgresql.conf ${PGCONFIGDIR}/postgresql.conf.bak.$(date +%F-%H%M) `

    3. Understand pg_hba.conf Syntax

    Each line in pg_hba.conf defines an access rule. Comments start with #. Blank lines are ignored. A rule typically follows this format:

    TYPE DATABASE USER ADDRESS METHOD [OPTIONS]

    • TYPE: Specifies the connection type.
    • * local: Connections via Unix-domain sockets (local access only).
    • * host: Connections via TCP/IP (both IPv4 and IPv6).
    • * hostssl: TCP/IP connections only if SSL is used.
    • hostnossl: TCP/IP connections only if SSL is not* used.
    • DATABASE: Which database(s) this rule applies to. Can be all, a specific database name, or replication (for streaming replication).
    • USER: Which user(s) this rule applies to. Can be all, a specific user name, or a group name prefixed with +.
    • ADDRESS: The client's IP address range or host.
    • * 127.0.0.1/32 or localhost: Only from the local machine (IPv4).
    • * ::1/128: Only from the local machine (IPv6).
    • * 0.0.0.0/0: All IPv4 addresses (highly insecure for most authentication methods).
    • * 192.168.1.0/24: A specific network range.
    • * 10.0.0.10/32: A single specific IP address.
    • METHOD: The authentication method. scram-sha-256 (recommended), md5, trust, peer, ident, gssapi, ssi.
    • OPTIONS: Additional options specific to the authentication method.

    4. Edit pg_hba.conf to Allow Connections

    Using the information from the error message (client IP, user, database), add or modify an entry in pg_hba.conf. Open the file with your preferred text editor (e.g., vi or nano).

    sudo vi ${PG_CONFIG_DIR}/pg_hba.conf

    Common Scenarios and Solutions:

    Scenario 1: Allow a specific application user from a specific IP address (most common and recommended). Add this line at the end of your pg_hba.conf file, or logically group it with other host entries:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
host    webapp_db       web_app_user    192.168.1.100/32        scram-sha-256
```
*   Replace `webapp_db` with your database name.
*   Replace `web_app_user` with your database username.
*   Replace `192.168.1.100/32` with the *exact IP address* of the client connecting to PostgreSQL. Use `/32` for a single IPv4 address or `/128` for a single IPv6 address. For a network, use the appropriate CIDR (e.g., `192.168.1.0/24`).

    Scenario 2: Allow all users from localhost for a specific database (for local applications/CLI tools).

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
host    your_db_name    all             127.0.0.1/32            scram-sha-256
host    your_db_name    all             ::1/128                 scram-sha-256

    Scenario 3: Allow local connections using peer authentication (recommended for local postgres user). This is typically already present and allows the Linux postgres user to connect to PostgreSQL as the postgres database user via Unix sockets without a password.

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer

    [!WARNING] Avoid using trust for remote connections (host) as it allows anyone to connect without any authentication. Only use trust for local connections in highly controlled environments or for specific, temporary debugging.

    host all all 0.0.0.0/0 md5 – This rule is highly insecure as it allows all users from any IP to connect to any database using a password. Only use 0.0.0.0/0 if you have very strict firewall rules in place, and even then, consider restricting it.

    5. Verify listen_addresses in postgresql.conf

    While pg_hba.conf handles authorization, postgresql.conf determines where PostgreSQL listens for connections. If PostgreSQL isn't listening on the correct network interface, remote connections will result in "connection refused," not "authorization failed." However, it's a common point of confusion.

    Open postgresql.conf:

    sudo vi ${PG_CONFIG_DIR}/postgresql.conf

    Find the listen_addresses parameter and ensure it's configured correctly:

    # What IP address(es) to listen on; '*' means all IP interfaces.
# In a production environment, it is best to be explicit.
#listen_addresses = 'localhost'         # (change requires restart)
listen_addresses = '*'                  # Listen on all available interfaces
#listen_addresses = '192.168.1.50,localhost' # Listen on specific IPs and localhost

    [!IMPORTANT] Changing listen_addresses requires a restart of the PostgreSQL service, not just a reload.

    6. Reload or Restart PostgreSQL

    After modifying pghba.conf, you must reload PostgreSQL for the changes to take effect. If you changed listenaddresses in postgresql.conf, a full restart is required.

    Reload (for pg_hba.conf changes):

    # Get the PostgreSQL service name (e.g., postgresql-15)

    sudo systemctl reload ${PG_SERVICE} `

    Restart (for postgresql.conf changes or if reload doesn't work):

    sudo systemctl restart ${PG_SERVICE}

    [!NOTE] systemctl reload is generally preferred as it doesn't drop existing connections. However, if issues persist or if listen_addresses was changed, a restart is necessary.

    7. Check Firewall Rules (firewalld)

    While less likely to cause an "authorization failed" error (which implies the connection reached PostgreSQL), firewall rules can prevent connections entirely, leading to "connection refused." It's a good practice to verify if you're troubleshooting any connection issue.

    PostgreSQL typically listens on port 5432. Ensure this port is open on your CentOS Stream/Rocky Linux server.

    # Check current firewall status

    If port 5432 is not listed, add it (for public zone, adjust if needed) sudo firewall-cmd –zone=public –add-port=5432/tcp –permanent sudo firewall-cmd –reload `

    8. Verify PostgreSQL User and Password

    Ensure the database user exists and has the correct password set, matching the authentication method in pg_hba.conf.

    # Connect as the postgres superuser

    List users and their attributes (look for your user) du

    If the user doesn't exist, create it: CREATE USER webappuser WITH PASSWORD 'averystrong_password' VALID UNTIL '2028-01-01';

    If the password needs to be set/reset (especially for scram-sha-256): ALTER USER webappuser WITH PASSWORD 'newstrongpassword';

    Grant connect privileges to the database (if not already done) GRANT CONNECT ON DATABASE webappdb TO webapp_user;

    Quit psql q `

    [!IMPORTANT] PostgreSQL 10+ defaults to scram-sha-256 for new password hashes. If your pghba.conf uses md5 and the user password was created more recently, there might be a mismatch. You can explicitly set the password using ALTER USER ... WITH PASSWORD ... and ensure pghba.conf matches.

    9. Test the Connection

    After making all changes and reloading/restarting PostgreSQL, attempt to connect again from your client or application.

    # From the client machine or server itself
psql -h your_db_host -U your_db_user -d your_db_name

    If successful, you should be prompted for a password (if using scram-sha-256 or md5) and then connect to the database. If the error persists, carefully review the PostgreSQL logs for the exact FATAL message and re-check each step, paying close attention to IP addresses, user names, database names, and authentication methods in your pg_hba.conf file.

  • Troubleshooting ‘Nginx workers connection limit reached’ on CentOS Stream / Rocky Linux

    Resolve Nginx connection limit errors on CentOS Stream & Rocky Linux. Learn to tune worker_connections, file descriptors, and kernel parameters for high traffic.

    When your Nginx web server experiences heavy traffic or misconfiguration, you might encounter issues where new connections are dropped, requests time out, or users see "502 Bad Gateway" or "503 Service Unavailable" errors. A common underlying cause on high-load systems is hitting the Nginx worker connection limit, leading to performance degradation or complete service interruption. This guide will walk you through diagnosing and resolving this critical issue on CentOS Stream and Rocky Linux environments.

    Symptom & Error Signature

    Users will typically experience slow page loads, connection refused errors, or HTTP 502/503 status codes. In the Nginx error logs, usually found at /var/log/nginx/error.log, you will observe messages similar to these:

    2023/10/26 14:35:01 [alert] 1234#1234: *1234567 too many open files: 1024, max_connections: 1024 (client: 192.168.1.1, server: example.com, request: "GET / HTTP/1.1", host: "example.com")
2023/10/26 14:35:02 [alert] 1234#1234: *1234568 worker_connections are not enough while connecting to upstream (X.X.X.X:80)
2023/10/26 14:35:03 [crit] 1235#1235: *1234569 accept() failed (24: Too many open files)

    These error messages clearly indicate that Nginx workers are unable to establish new connections, either due to their own configured limits or system-wide file descriptor constraints.

    Root Cause Analysis

    The "Nginx workers connection limit reached" error typically stems from one or more of the following underlying issues:

    1. Insufficient workerconnections: This is the most direct cause. The workerconnections directive in nginx.conf defines the maximum number of simultaneous connections that a single Nginx worker process can handle. If the aggregate capacity (workerprocesses * workerconnections) is lower than the actual peak concurrent connections, Nginx will drop new requests.
    2. Too Few workerprocesses: While workerconnections limits individual workers, workerprocesses determines how many workers Nginx spawns. If this value is too low, the server might not fully utilize available CPU cores, bottlenecking processing capacity even if workerconnections is high per worker.
    3. System-wide File Descriptor Limits (ulimit -n): Nginx is a file descriptor (FD)-intensive application. Each client connection, as well as connections to upstream servers, uses at least one file descriptor. If the operating system's per-process file descriptor limit (ulimit -n) for the Nginx process is lower than its configured worker_connections, Nginx will fail with "Too many open files" errors before reaching its internal connection limit.
    4. Kernel TCP Backlog Limits (net.core.somaxconn, net.ipv4.tcpmaxsyn_backlog): These kernel parameters control the maximum length of the queue for pending TCP connections. If Nginx processes established connections slower than new connections arrive, these queues can overflow, leading to dropped connections even before Nginx can accept them, often presenting as connection resets on the client side.
    5. Upstream Server Bottlenecks: If Nginx is configured as a reverse proxy, a slow or overloaded upstream application server (e.g., PHP-FPM, Node.js app, Tomcat) can cause Nginx worker processes to wait for responses, holding open connections and thereby exhausting the worker_connections pool prematurely.

    Step-by-Step Resolution

    Follow these steps to diagnose and resolve the Nginx worker connection limit issue.

    1. Analyze Current Nginx & System Configuration

    Before making changes, understand your current settings:

    First, identify the Nginx master process ID: `bash sudo systemctl status nginx | grep Main PID # Example output: Main PID: 1234 (nginx) ` Replace 1234 with your Nginx master PID in subsequent commands.

    • Check Nginx workerprocesses and workerconnections:
    • `bash
    • grep -E 'workerprocesses|workerconnections' /etc/nginx/nginx.conf
    • `
    • Check Nginx process's file descriptor limit:
    • `bash
    • cat /proc/$(sudo systemctl show –property MainPID nginx | cut -d= -f2)/limits | grep 'Max open files'
    • `
    • Compare this "Max open files" (soft limit) with your workerconnections value. workerconnections must be less than or equal to this limit.
    • Check system-wide kernel TCP backlog settings:
    • `bash
    • sysctl net.core.somaxconn net.ipv4.tcpmaxsyn_backlog
    • `

    2. Adjust Nginx workerconnections and workerprocesses

    Modify the Nginx main configuration file, typically /etc/nginx/nginx.conf.

    1. Open the Nginx configuration file:
    2. `bash
    3. sudo vi /etc/nginx/nginx.conf
    4. `
    5. Adjust workerprocesses and workerconnections:
    6. Locate the worker_processes and events blocks.
    7. * Set worker_processes to auto to allow Nginx to detect the optimal number of processes based on CPU cores, or explicitly to the number of CPU cores available on your server.
    8. * Significantly increase worker_connections. A common starting point for high-traffic sites is 10240 or 20480. You can go higher, but remember each connection consumes some memory.
    9. * Consider adding multi_accept on; in the events block. This tells a worker process to accept as many new connections as possible at once, rather than one by one, which can be beneficial under very high load.

    user nginx; worker_processes auto; # Set to 'auto' or the number of CPU cores (e.g., 4, 8)

    error_log /var/log/nginx/error.log warn; pid /run/nginx.pid;

    events { worker_connections 20480; # Increase this value significantly (e.g., 10240, 20480, 40960) multi_accept on; # Optional: Enable multiple connection accepts per worker }

    http { include /etc/nginx/mime.types; default_type application/octet-stream;

    … other http directives … } ` 3. Test Nginx configuration: `bash sudo nginx -t ` Ensure you see syntax is ok and test is successful. 4. Reload Nginx service: `bash sudo systemctl reload nginx `

    [!IMPORTANT] The total number of concurrent connections Nginx can theoretically handle is workerprocesses workerconnections. Ensure this value is adequate for your anticipated peak traffic but also mindful of your server's RAM and CPU resources. A common guideline is to set worker_connections to at least 2 UlimitNOFILE to account for both client and upstream connections.

    3. Increase System-wide File Descriptor Limits (ulimit)

    The Nginx process's file descriptor limit must be equal to or greater than its worker_connections setting. We'll modify the Systemd service unit for Nginx to achieve this.

    1. Create or edit the Nginx Systemd override file:
    2. `bash
    3. sudo systemctl edit nginx
    4. `
    5. This will open a file like /etc/systemd/system/nginx.service.d/override.conf.
    6. Add the LimitNOFILE directive:
    7. Add the following lines to the file, setting LimitNOFILE to a value higher than your worker_connections (e.g., 65536 or 131072).
        [Service]
    LimitNOFILE=65536
    ```
3.  **Save and exit.**
4.  **Reload the Systemd daemon:**
    ```bash
    sudo systemctl daemon-reload
    ```
5.  **Restart Nginx to apply changes:**
    ```bash
    sudo systemctl restart nginx
    ```
6.  **Verify the new `ulimit` for the Nginx process:**
    ```bash
    cat /proc/$(sudo systemctl show --property MainPID nginx | cut -d= -f2)/limits | grep 'Max open files'
    ```

    [!WARNING] While increasing LimitNOFILE allows Nginx to handle more connections, setting it excessively high without sufficient RAM can lead to memory exhaustion and system instability. Start with 65536 or 131072 and monitor your server's performance.

    4. Tune Kernel TCP Backlog Parameters

    Adjusting kernel parameters can help the system handle bursts of new connections more gracefully.

    1. Create or edit a custom sysctl configuration file:
    2. `bash
    3. sudo vi /etc/sysctl.d/90-custom.conf
    4. `
    5. (You can also use /etc/sysctl.conf, but .d files are cleaner for custom settings).
    6. Add or modify the following parameters:
        # Maximum number of connections that can be queued for a listening socket

    Maximum number of SYN requests that the kernel will keep in memory net.ipv4.tcpmaxsyn_backlog = 8192

    Allow reusing sockets in TIME_WAIT state for new connections (can help with port exhaustion) net.ipv4.tcptwreuse = 1

    Reduce TIME_WAIT state duration (for faster socket cleanup) net.ipv4.tcpfintimeout = 30 ` 3. Save and exit. 4. Apply the sysctl changes: `bash sudo sysctl -p /etc/sysctl.d/90-custom.conf ` (Or sudo sysctl -p if you edited /etc/sysctl.conf). 5. Verify the new kernel parameters: `bash sysctl net.core.somaxconn net.ipv4.tcpmaxsynbacklog net.ipv4.tcptwreuse net.ipv4.tcpfin_timeout `

    [!NOTE] net.core.somaxconn affects the listen directive's backlog parameter in Nginx. net.ipv4.tcpmaxsyn_backlog is crucial for preventing SYN flood attacks and managing a high rate of new connection attempts. These values should be adjusted incrementally based on your specific traffic patterns and monitoring.

    5. Monitor and Optimize

    After implementing these changes, continuous monitoring is crucial to ensure stability and optimal performance.

    • Monitor Nginx error logs: Keep an eye on /var/log/nginx/error.log for any new connection-related errors.
    • System resource monitoring: Use tools like atop, htop, dstat, or grafana/prometheus to monitor CPU, memory, and network usage.
    • Network statistics:
    • * Check for connections in various states: netstat -nat | awk '{print $NF}' | sort | uniq -c | sort -nr
    • * Summarize network statistics: ss -s
    • Nginx Stub Status Module: If enabled, monitor Nginx's active connections and requests per second:
    • `bash
    • curl -s http://localhost/nginx_status
    • `
    • (Requires stubstatus to be enabled in your Nginx configuration, e.g., in a server block: location /nginxstatus { stub_status on; allow 127.0.0.1; deny all; }).
    • Further Nginx Optimization:
    • * Consider keepalivetimeout and keepaliverequests if your application benefits from persistent connections.
    • * Optimize proxy buffering settings (proxybuffering, proxybuffers, proxybuffersize) if Nginx is a reverse proxy.
    • * Ensure your upstream application servers (e.g., PHP-FPM, uWSGI, Node.js) are also adequately scaled and configured to handle the increased load Nginx is now passing to them.

    By systematically adjusting Nginx configuration and underlying OS limits, you can significantly improve your server's ability to handle high concurrent connections, ensuring a more stable and responsive web service.