Import psql dump without change in pg_hba.conf?

You can import a PostgreSQL dump without modifying pg_hba.conf by leveraging environment variables to specify the connection details directly in your command line. This approach bypasses the need for persistent configuration changes, allowing you to authenticate and connect using specific host, port, user, and password parameters provided at runtime. By setting these variables or passing them as arguments, you maintain a clean configuration file while ensuring secure and flexible database restoration processes on Linux systems.

Understanding the Role of pg_hba.conf

The pg_hba.conf file is responsible for controlling client authentication in PostgreSQL. It determines which hosts are allowed to connect, which databases they can access, and what authentication methods (such as md5, scram-sha-256, or peer) are required. Modifying this file requires restarting the PostgreSQL service or reloading the configuration, which can be disruptive in production environments. Often, users encounter issues when trying to import dumps because the default connection settings do not match the authentication rules defined in this file. Instead of editing global settings, it is safer and more efficient to provide explicit connection parameters during the import operation.

Using Environment Variables for Connection

One of the most robust ways to handle connections without touching configuration files is through environment variables. The PostgreSQL client tools, including psql, recognize several standard environment variables that override default connection behavior. By exporting these variables before running your import command, you ensure that the tool connects exactly where you intend, using the credentials you provide. This method is particularly useful in scripts or automated tasks where configuration files should remain immutable. To use this method, you must set the following variables:

• PGHOST: Specifies the host name of the machine running the database server. If omitted, it defaults to a Unix domain socket.
• PGPORT: Sets the TCP port or local Unix domain socket file extension. The default is usually 5432.
• PGUSER: Defines the PostgreSQL user name to connect as. If not specified, it defaults to the current operating system user name.
• PGPASSWORD: Provides the password for the specified user. Note that storing passwords in plain text in environment variables can have security implications, so use with caution in shared environments.

For example, you can run the import command like this:

export PGHOST=localhost
export PGPORT=5432
export PGUSER=myuser
export PGPASSWORD=mypassword
psql -d mydb < dump.sql

This ensures that psql uses the provided credentials rather than relying on the system user or default socket connections that might be blocked by pg_hba.conf.

Passing Parameters Directly in the Command

If you prefer not to export environment variables, you can pass connection parameters directly to the psql command using long options. This is often cleaner for one-off commands as it keeps all necessary information in a single line. The -h flag specifies the host, -p sets the port, -U defines the user, and -d selects the database. However, note that the password cannot be passed as a command-line argument due to security restrictions regarding process visibility in the system's process list. Instead, you can use the -w flag to suppress password prompts if you are using a password file, or rely on the PGPASSWORD environment variable for that specific value. Alternatively, you can use a .pgpass file to store connection credentials securely without exposing them in the command line or environment variables. This file should be located in the user's home directory and have strict permissions (readable only by the owner). Here is an example of a direct command invocation:

psql -h localhost -p 5432 -U myuser -d mydb -f dump.sql

When you run this, psql will prompt for the password. You can type it in, or if you have configured a .pgpass file, it will use that automatically. This approach avoids any changes to pg_hba.conf because you are explicitly defining the connection endpoint and user.

Handling Authentication Errors

If you still encounter authentication errors despite providing correct parameters, check the PostgreSQL logs for detailed error messages. Common issues include mismatched hostnames (using localhost vs 127.0.0.1), incorrect port numbers, or users that do not exist in the database cluster. Remember that pg_hba.conf rules are evaluated in order, and the first matching rule is used. Ensure that your connection attempt matches a valid rule in the file. For instance, if your rule allows host connections from 127.0.0.1/32 but you connect via ::1 (IPv6 localhost), it may fail depending on how your network stack resolves localhost. In such cases, you can test connectivity using pg_isready to verify that the server is accepting connections on the specified host and port. This diagnostic step helps isolate whether the issue is with the network layer or the authentication layer. If pg_isready succeeds but psql fails, the problem is likely related to user permissions or password hashing algorithms.

Security Considerations

While avoiding changes to pg_hba.conf is convenient, it is crucial to maintain security best practices. Avoid hardcoding passwords in scripts or shell history. Use .pgpass files or integrated authentication methods where possible. Additionally, restrict the scope of the database user to only the necessary privileges for the import task. Importing large dumps can also impact system performance, so monitor resource usage during the process. For further assistance with related Linux database management tasks, you might find the guide on How to Shrink Sparse Files in Linux After Copying Without the -S Operator helpful for managing disk space after large imports. Similarly, understanding process management can aid in troubleshooting if the import process is killed unexpectedly, as discussed in How to find out exactly which process was killed by OOM killer?.

Frequently Asked Questions

Can I use psql without a password prompt?

Yes, you can set the PGPASSWORD environment variable to avoid interactive prompts. Alternatively, configure a .pgpass file in your home directory to store credentials securely, which psql will read automatically.

Why does my connection fail even with correct credentials?

Check if the host specification matches the rules in pg_hba.conf. Using localhost might resolve to IPv6 (::1) while the rule only allows IPv4 (127.0.0.1), causing a mismatch. Try specifying 127.0.0.1 explicitly.

Is it safe to export passwords in environment variables?

It is generally safe for short-lived sessions but visible in environment listings. For better security, use .pgpass files with restricted permissions or integrate with system authentication mechanisms like Kerberos or LDAP.

What if I need to import a dump from a remote server?

Use the -h flag to specify the remote host IP or domain name. Ensure that the remote server's pg_hba.conf allows connections from your IP address and that network firewalls permit traffic on the PostgreSQL port.

How do I verify if PostgreSQL is ready to accept connections?

Run the pg_isready command with the appropriate host and port flags. It returns immediately if the server is accepting connections, helping you diagnose network or service availability issues before attempting an import.