{"id":107,"date":"2026-07-17T00:00:00","date_gmt":"2026-07-17T00:00:00","guid":{"rendered":"http:\/\/\/var\/www\/html\/?p=107"},"modified":"2026-07-19T16:42:02","modified_gmt":"2026-07-19T16:42:02","slug":"postgresql-pghbaconf-connection-authorization-failed-on-centos-stream-rocky-linux","status":"publish","type":"post","link":"https:\/\/staging.butitworkedlocal.com\/?p=107","title":{"rendered":"PostgreSQL pg_hba.conf Connection Authorization Failed on CentOS Stream \/ Rocky Linux"},"content":{"rendered":"\n

Troubleshoot and resolve 'connection authorization failed' errors in PostgreSQL on CentOS Stream and Rocky Linux by correctly configuring pg_hba.conf and related settings.<\/strong><\/p>\n\n\n

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.<\/p>\n\n

Symptom & Error Signature<\/h3>\n\n

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

Typical psql<\/code> command line error:<\/strong><\/p>\n\n

$ psql -h your_db_host -U your_db_user -d your_db_name\npsql: FATAL:  connection authorization failed for user "your_db_user"\npsql: FATAL:  no pg_hba.conf entry for host "your_client_ip", user "your_db_user", database "your_db_name", no encryption<\/code><\/pre>\n\n
Common application error (e.g., Python with psycopg2):<\/strong><\/p>\n\n
# Example output from a Python application attempting to connect\nTraceback (most recent call last):\n  File "<stdin>", line 1, in <module>\npsycopg2.OperationalError: FATAL:  connection authorization failed for user "web_app_user"\nFATAL:  no pg_hba.conf entry for host "192.168.1.100", user "web_app_user", database "webapp_db"<\/code><\/pre>\n\n
PostgreSQL server log entries (found in \/var\/lib\/pgsql\/data\/log\/postgresql-*.log<\/code> or journalctl -u postgresql-1X<\/code>):<\/strong><\/p>\n\n
202X-XX-XX XX:XX:XX UTC [12345] LOG:  connection received: host=192.168.1.100 port=54321\n202X-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\n```<\/code><\/pre>\n\n
Root Cause Analysis<\/h3>\n\n
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<\/code>. 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.<\/p>\n\n
The underlying reasons typically fall into one of these categories:<\/p>\n\n
  1.  Missing pghba.conf<\/code> Entry:<\/strong> The most common cause. There is no rule in pg<\/em>hba.conf<\/code> that matches the incoming connection's parameters (source IP, user, database).<\/li>
  2.  Incorrect pg_hba.conf<\/code> Entry:<\/strong> 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.<\/li>
  3.  Incorrect Order of Rules:<\/strong> pg_hba.conf<\/code> rules are processed sequentially from top to bottom. The first<\/em> 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.<\/li>
  4.  Incorrect Authentication Method:<\/strong> The pg_hba.conf<\/code> entry specifies an authentication method (e.g., scram-sha-256<\/code>, md5<\/code>, trust<\/code>, peer<\/code>, ident<\/code>) that doesn't match the client's provided credentials or the server's configured user password.<\/li>
  5.     *   scram-sha-256<\/code>: The modern, recommended secure password-based authentication.<\/li>
  6.     *   md5<\/code>: An older, less secure password-based authentication, still widely used.<\/li>
  7.     *   trust<\/code>: Allows anyone to connect without a password (highly insecure for non-local connections).<\/li>
  8.     *   peer<\/code>: Used for local connections where the operating system user matches the database user.<\/li>
  9.     *   ident<\/code>: Similar to peer<\/code>, relies on an ident server on the client for authentication.<\/li>
  10.  listenaddresses<\/code> Misconfiguration:<\/strong> While this usually results in "connection refused," if listen<\/em>addresses<\/code> in postgresql.conf<\/code> is set to localhost<\/code> or 127.0.0.1<\/code> and a remote client tries to connect, the connection will not even reach the pg_hba.conf<\/code> stage for remote IP addresses. It's essential to ensure PostgreSQL is listening on the correct network interfaces (e.g., *<\/code> for all, or specific IP addresses).<\/li>
  11.  Incorrect Database\/User Permissions:<\/strong> Even if pghba.conf<\/code> allows the connection, the user might not have CONNECT<\/code> privileges on the requested database or USAGE<\/code> on specific schemas, leading to application errors after authentication. This is different from the pg<\/em>hba.conf<\/code> error but often confused.<\/li><\/ol>\n\n
    Step-by-Step Resolution<\/h3>\n\n
    Follow these steps carefully to diagnose and resolve the pg_hba.conf<\/code> connection authorization error.<\/p>\n\n
    1. Locate pg_hba.conf<\/code> and postgresql.conf<\/code><\/h4>\n\n
    First, you need to find the correct configuration files. The location can vary slightly depending on the PostgreSQL version and installation method.<\/p>\n\n
    # Log in as the postgres user (or use sudo) to execute psql commands\nsudo -u postgres psql -c 'SHOW hba_file;'\nsudo -u postgres psql -c 'SHOW config_file;'<\/code><\/pre>\n\n
    Common locations on CentOS Stream \/ Rocky Linux for PostgreSQL 12-16:<\/strong><\/p>\n\n
    •   pghba.conf<\/code><\/strong>: \/var\/lib\/pgsql\/data\/pg<\/em>hba.conf<\/code> (for older versions\/manual setup) or \/var\/lib\/pgsql\/1X\/data\/pg_hba.conf<\/code> (where 1X<\/code> is your PostgreSQL major version, e.g., 15<\/code>).<\/li>
    •   postgresql.conf<\/code><\/strong>: \/var\/lib\/pgsql\/data\/postgresql.conf<\/code> or \/var\/lib\/pgsql\/1X\/data\/postgresql.conf<\/code>.<\/li><\/ul>\n\n
      [!NOTE]\nOn modern CentOS\/Rocky systems, PostgreSQL is often installed via dnf<\/code>, and the data<\/code> directory is version-specific (e.g., \/var\/lib\/pgsql\/15\/data<\/code>).<\/p><\/blockquote>\n\n
      2. Backup Original Configuration Files<\/h4>\n\n
      Before making any changes, always back up your configuration files.<\/p>\n\n
      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'<\/code><\/pre>\n\n
      sudo cp ${PGCONFIG<\/em>DIR}\/pghba.conf ${PG<\/em>CONFIGDIR}\/pg<\/em>hba.conf.bak.$(date +%F-%H%M)\nsudo cp ${PGCONFIG<\/em>DIR}\/postgresql.conf ${PGCONFIG<\/em>DIR}\/postgresql.conf.bak.$(date +%F-%H%M)\n<\/code>`<\/p>\n\n
      3. Understand pg_hba.conf<\/code> Syntax<\/h4>\n\n
      Each line in pg_hba.conf<\/code> defines an access rule. Comments start with #<\/code>. Blank lines are ignored.\nA rule typically follows this format:<\/p>\n\n
      TYPE  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]<\/code><\/p>\n\n
      •   TYPE<\/code><\/strong>: Specifies the connection type.<\/li>
      •   *   local<\/code>: Connections via Unix-domain sockets (local access only).<\/li>
      •   *   host<\/code>: Connections via TCP\/IP (both IPv4 and IPv6).<\/li>
      •   *   hostssl<\/code>: TCP\/IP connections only if SSL is used.<\/li>
      •      hostnossl<\/code>: TCP\/IP connections only if SSL is <\/em>not* used.<\/li>
      •   DATABASE<\/code><\/strong>: Which database(s) this rule applies to. Can be all<\/code>, a specific database name, or replication<\/code> (for streaming replication).<\/li>
      •   USER<\/code><\/strong>: Which user(s) this rule applies to. Can be all<\/code>, a specific user name, or a group name prefixed with +<\/code>.<\/li>
      •   ADDRESS<\/code><\/strong>: The client's IP address range or host.<\/li>
      •   *   127.0.0.1\/32<\/code> or localhost<\/code>: Only from the local machine (IPv4).<\/li>
      •   *   ::1\/128<\/code>: Only from the local machine (IPv6).<\/li>
      •   *   0.0.0.0\/0<\/code>: All IPv4 addresses (highly insecure for most authentication methods).<\/li>
      •   *   192.168.1.0\/24<\/code>: A specific network range.<\/li>
      •   *   10.0.0.10\/32<\/code>: A single specific IP address.<\/li>
      •   METHOD<\/code><\/strong>: The authentication method. scram-sha-256<\/code> (recommended), md5<\/code>, trust<\/code>, peer<\/code>, ident<\/code>, gssapi<\/code>, ssi<\/code>.<\/li>
      •   OPTIONS<\/code><\/strong>: Additional options specific to the authentication method.<\/li><\/ul>\n\n
        4. Edit pg_hba.conf<\/code> to Allow Connections<\/h4>\n\n
        Using the information from the error message (client IP, user, database), add or modify an entry in pg_hba.conf<\/code>. Open the file with your preferred text editor (e.g., vi<\/code> or nano<\/code>).<\/p>\n\n
        sudo vi ${PG_CONFIG_DIR}\/pg_hba.conf<\/code><\/pre>\n\n
        Common Scenarios and Solutions:<\/strong><\/p>\n\n
        Scenario 1: Allow a specific application user from a specific IP address (most common and recommended).<\/strong>\nAdd this line at the end<\/em> of your pg_hba.conf<\/code> file, or logically group it with other host<\/code> entries:<\/p>\n\n
        # TYPE  DATABASE        USER            ADDRESS                 METHOD\nhost    webapp_db       web_app_user    192.168.1.100\/32        scram-sha-256\n```\n*   Replace `webapp_db` with your database name.\n*   Replace `web_app_user` with your database username.\n*   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`).<\/code><\/pre>\n\n
        Scenario 2: Allow all users from localhost<\/code> for a specific database (for local applications\/CLI tools).<\/strong><\/p>\n\n
        # TYPE  DATABASE        USER            ADDRESS                 METHOD\nhost    your_db_name    all             127.0.0.1\/32            scram-sha-256\nhost    your_db_name    all             ::1\/128                 scram-sha-256<\/code><\/pre>\n\n
        Scenario 3: Allow local connections using peer<\/code> authentication (recommended for local postgres<\/code> user).<\/strong>\nThis is typically already present and allows the Linux postgres<\/code> user to connect to PostgreSQL as the postgres<\/code> database user via Unix sockets without a password.<\/p>\n\n
        # TYPE  DATABASE        USER            ADDRESS                 METHOD\nlocal   all             postgres                                peer<\/code><\/pre>\n\n
        [!WARNING]\nAvoid using trust<\/code> for remote connections (host<\/code>) as it allows anyone to connect without any authentication. Only use trust<\/code> for local<\/code> connections in highly controlled environments or for specific, temporary debugging.<\/p>\n\n
        host    all             all             0.0.0.0\/0               md5<\/code> – 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<\/code> if you have very strict firewall rules in place, and even then, consider restricting it.<\/p><\/blockquote>\n\n
        5. Verify listen_addresses<\/code> in postgresql.conf<\/code><\/h4>\n\n
        While pg_hba.conf<\/code> handles authorization<\/em>, postgresql.conf<\/code> determines where<\/em> 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.<\/p>\n\n
        Open postgresql.conf<\/code>:<\/p>\n\n
        sudo vi ${PG_CONFIG_DIR}\/postgresql.conf<\/code><\/pre>\n\n
        Find the listen_addresses<\/code> parameter and ensure it's configured correctly:<\/p>\n\n
        # What IP address(es) to listen on; '*' means all IP interfaces.\n# In a production environment, it is best to be explicit.\n#listen_addresses = 'localhost'         # (change requires restart)\nlisten_addresses = '*'                  # Listen on all available interfaces\n#listen_addresses = '192.168.1.50,localhost' # Listen on specific IPs and localhost<\/code><\/pre>\n\n
        [!IMPORTANT]\nChanging listen_addresses<\/code> requires a restart<\/strong> of the PostgreSQL service, not just a reload.<\/p><\/blockquote>\n\n
        6. Reload or Restart PostgreSQL<\/h4>\n\n
        After modifying pghba.conf<\/code>, you must reload PostgreSQL for the changes to take effect. If you changed listen<\/em>addresses<\/code> in postgresql.conf<\/code>, a full restart is required.<\/p>\n\n
        Reload (for pg_hba.conf<\/code> changes):<\/strong><\/p>\n\n
        # Get the PostgreSQL service name (e.g., postgresql-15)<\/code><\/pre>\n\n
        sudo systemctl reload ${PG_SERVICE}\n<\/code>`<\/p>\n\n
        Restart (for postgresql.conf<\/code> changes or if reload doesn't work):<\/strong><\/p>\n\n
        sudo systemctl restart ${PG_SERVICE}<\/code><\/pre>\n\n
        [!NOTE]\nsystemctl reload<\/code> is generally preferred as it doesn't drop existing connections. However, if issues persist or if listen_addresses<\/code> was changed, a restart<\/code> is necessary.<\/p><\/blockquote>\n\n
        7. Check Firewall Rules (firewalld)<\/h4>\n\n
        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.<\/p>\n\n
        PostgreSQL typically listens on port 5432<\/code>. Ensure this port is open on your CentOS Stream\/Rocky Linux server.<\/p>\n\n
        # Check current firewall status<\/code><\/pre>\n\n
        If port 5432 is not listed, add it (for public zone, adjust if needed)\nsudo firewall-cmd –zone=public –add-port=5432\/tcp –permanent\nsudo firewall-cmd –reload\n<\/code>`<\/h1>\n\n
        8. Verify PostgreSQL User and Password<\/h4>\n\n
        Ensure the database user exists and has the correct password set, matching the authentication method in pg_hba.conf<\/code>.<\/p>\n\n
        # Connect as the postgres superuser<\/code><\/pre>\n\n
        List users and their attributes (look for your user)\ndu<\/h1>\n\n
        If the user doesn't exist, create it:\nCREATE USER webapp<\/em>user WITH PASSWORD 'avery<\/em>strong_password' VALID UNTIL '2028-01-01';<\/h1>\n\n
        If the password needs to be set\/reset (especially for scram-sha-256):\nALTER USER webapp<\/em>user WITH PASSWORD 'newstrong<\/em>password';<\/h1>\n\n
        Grant connect privileges to the database (if not already done)\nGRANT CONNECT ON DATABASE webappdb TO web<\/em>app_user;<\/h1>\n\n
        Quit psql\nq\n<\/code>`<\/h1>\n\n
        [!IMPORTANT]\nPostgreSQL 10+ defaults to scram-sha-256<\/code> for new password hashes. If your pghba.conf<\/code> uses md5<\/code> and the user password was created more recently, there might be a mismatch. You can explicitly set the password using ALTER USER ... WITH PASSWORD ...<\/code> and ensure pg<\/em>hba.conf<\/code> matches.<\/p><\/blockquote>\n\n
        9. Test the Connection<\/h4>\n\n
        After making all changes and reloading\/restarting PostgreSQL, attempt to connect again from your client or application.<\/p>\n\n
        # From the client machine or server itself\npsql -h your_db_host -U your_db_user -d your_db_name<\/code><\/pre>\n\n
        If successful, you should be prompted for a password (if using scram-sha-256<\/code> or md5<\/code>) and then connect to the database. If the error persists, carefully review the PostgreSQL logs for the exact FATAL<\/code> message and re-check each step, paying close attention to IP addresses, user names, database names, and authentication methods in your pg_hba.conf<\/code> file.<\/p>","protected":false},"excerpt":{"rendered":"
        Troubleshoot and resolve ‘connection authorization failed’ errors in PostgreSQL on CentOS Stream and Rocky Linux by correctly configuring pg_hba.conf and related settings.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[187],"tags":[280,242,279,275,150,172,6],"class_list":["post-107","post","type-post","status-publish","format-standard","hentry","category-database","tag-authorization-failed","tag-centos-stream","tag-database-connection","tag-pg_hba-conf","tag-postgresql","tag-rocky-linux","tag-troubleshooting"],"_links":{"self":[{"href":"https:\/\/staging.butitworkedlocal.com\/index.php?rest_route=\/wp\/v2\/posts\/107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/staging.butitworkedlocal.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/staging.butitworkedlocal.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/staging.butitworkedlocal.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/staging.butitworkedlocal.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=107"}],"version-history":[{"count":1,"href":"https:\/\/staging.butitworkedlocal.com\/index.php?rest_route=\/wp\/v2\/posts\/107\/revisions"}],"predecessor-version":[{"id":226,"href":"https:\/\/staging.butitworkedlocal.com\/index.php?rest_route=\/wp\/v2\/posts\/107\/revisions\/226"}],"wp:attachment":[{"href":"https:\/\/staging.butitworkedlocal.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/staging.butitworkedlocal.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/staging.butitworkedlocal.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}