RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.w3resource.com/PostgreSQL/snippets/pg-hba-conf-host.php below:

Website Navigation

Comprehensive Guide to pg_hba.conf Host Configuration

Comprehensive Guide to pg_hba.conf Host ConfigurationLast update on December 31 2024 13:03:32 (UTC/GMT +8 hours)

Configuring pg_hba.conf Entry for Host Connections

The pg_hba.conf file in PostgreSQL controls client authentication by defining who can connect, from where, and using which authentication method. Configuring a proper pg_hba.conf entry for a host is essential for allowing secure and specific connections to your PostgreSQL database.

Syntax:

Each line in pg_hba.conf follows this syntax:

type  database  user  address  auth-method  [auth-options]

Parameters:

1. type: The type of connection (host for TCP/IP connections).

2. database: The target database(s). Use all for all databases.

3. user: The user(s) allowed to connect. Use all for all users.

4. address: Client IP address or range (e.g., 192.168.1.0/24).

5. auth-method: The authentication method (e.g., md5, scram-sha-256, trust).

6. auth-options (optional): Additional options like passwords or certificates.

Examples

1. Allow Connections from a Specific Host

Code:

host  mydatabase  myuser  192.168.1.100/32  md5

Explanation:

host: Specifies a TCP/IP connection.
mydatabase: Restricts the connection to mydatabase.
myuser: Only the user myuser can connect.
192.168.1.100/32: Allows only the host with IP 192.168.1.100.
md5: Requires the user to provide a password hashed using MD5.

2. Allow All Connections from a Subnet

Code:

host  all  all  10.10.0.0/16  scram-sha-256

Explanation:

host: Allows TCP/IP connections.
all: Applies to all databases and users.
10.10.0.0/16: Allows connections from any host in the subnet 10.10.0.0/16.
scram-sha-256: Ensures secure password hashing.

3. Trust Connections from Localhost

Code:

host  all  all  127.0.0.1/32  trust

Explanation:

host: Specifies TCP/IP connection.
127.0.0.1/32: Restricts to localhost.
trust: No password is required. (Use only in development environments!)

Editing the pg_hba.conf File

1. Locate the File: The pg_hba.conf file is typically located in the data directory of your PostgreSQL installation.

 
/var/lib/postgresql//main/pg_hba.conf

2. Edit the File: Use a text editor to add or modify entries.

sudo nano /var/lib/postgresql/14/main/pg_hba.conf

3. Reload PostgreSQL: After making changes, reload the configuration for the changes to take effect.

 
sudo systemctl reload postgresql

Advanced Usage

Using CIDR Notation

CIDR (Classless Inter-Domain Routing) allows specifying a range of IPs.

 
host  all  all  192.168.0.0/24  md5

This entry permits all hosts in the 192.168.0.0 subnet to connect.

Combining SSL with Host Authentication

For secure connections, use the hostssl type.

 
hostssl  mydb  myuser  192.168.1.0/24  cert

Common Errors and Troubleshooting

1. Connection Refused: Ensure the client IP matches the address parameter in the entry.

2. Authentication Failed: Verify the auth-method and user credentials.

3. File Order Matters: PostgreSQL checks pg_hba.conf entries in order. Ensure specific rules appear before general ones.

Precautions

Always test new pg_hba.conf entries in a staging environment.
Avoid using trust authentication in production to prevent unauthorized access.
Combine restrictive IP ranges with secure authentication methods (e.g., scram-sha-256).

All PostgreSQL Questions, Answers, and Code Snippets Collection.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4