PostgreSQL Configuration Changes You Need to Make Post-Installation
So you’ve installed postgres onto your machine, and you want to start working with it.
How?
The key to understanding the post-installation procedure is to realize that it “depends”.
- It “depends” on the OS i.e. MSWindows vs Linux.
- It “depends” on the flavor of Linux i.e. Debian vs RedHat.
- It “depends” if it’s a package install or from source code.
Let’s start by working with the most basic steps common to all installs and we’ll break it down further from there.
A successfully installed postgres, no matter the version, is characterized by the following:
- a newly created datacluster is present
- a configuration file pg_hba.conf is to be edited
- a configuration file postgresql.conf is to be edited
There are other configuration files but we’ll work with these.
For the purposes of discussion let’s further assume you’ve started up the cluster and postgres is running on the host. Here’s an example of what you can see when you run a utility, such as netstat, that reports the network connections:
$netstat -tlnp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 27929/postgres
The first thing is to look at is the “Local Address”. Notice how it says 127.0.0.1:5432. Okay, so that means that the server is currently listening on the localhost on port 5432. But you want 0.0.0.0:5432 otherwise remote connections cannot be accepted. With an editor, open up file pg_hba.conf and look at the “default” rules. Keep in mind that the configuration file can be located in one of several locations, we’ll cover that later.
ATTENTION: Setting the Address (CIDR) to 0.0.0.0 is for connectivity purposes only. As soon as you know everything works you should restrict this to as few permitted connections as possible. This is not something you should do on a production machine.
The actual “rules” per line can vary from one type of postgres installation to another. The good news is that RedHat/Centos look alike and all Debian/Ubuntu have their own similar styles too. The relevant settings are at the bottom of the file as all else above is commented documentation.
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 peer # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5
Look at the first line, where TYPE is “local”. So long as you can log in locally, via UNIX DOMAIN SOCKETS, and sudo as the superuser, postgres is the default, you can access your service without a password.
METHOD should be peer but if it uses something else, like md5, you’ll need to change the string. Alternatively, if you feel particularly trustful of the other user accounts on the host, you can use the METHOD trust permitting free access to all locally logged-in UNIX accounts.
# ATTENTION: # the service must be reloaded for any edits to pg_hba.conf to take effect # $sudo su - postgres $psql -c "select 'hello world' as greetings" greetings ------------- hello world
Looking at the second line one sees that TYPE is IPV4. This rule, as well as the rule for TYPE IPv6, prevents localhost logins unless one knows the password:
$psql -h localhost -c "select 'hello world' as greetings" Password for user postgres:
So let’s fix this by assigning a password to ROLE postgres by logging via UNIX DOMAIN SOCKETS since we already permit logins by METHOD peer:
-- -- example invocation, change the password to something real -- ALTER ROLE postgres WITH PASSWORD 'mypassword';
TIP: edits to pg_hba.conf requires the service to reload the file i.e. SIGHUP
Now that we’ve had connectivity for localhost connections, we’re using an IP v4 socket for this example, we can now proceed to address remote connections.
You’re going to need to add another rule which should be placed after the localhost rule:
host all all 0.0.0.0/0 md5
And here’s a line you can write for IPV6:
host all all ::0/0 md5
TIP: The demonstrated example rules let everybody connect to the host. A knowledge of CIDR is key to enforcing network security.
Keeping in mind that your system will be unique, here’s what the pg_hba.conf should start to look like:
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 0.0.0.0/0 md5 # IPv6 local connections: host all all ::1/128 md5 host all all ::0/0 md5 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5
You’re almost there!
Now that you’ve added a password to the superuser and updated the configuration file pg_hba.conf, it’s time to visit another configuration file postgresql.conf.
Locate the file and edit runtime parameter listen_addresses. The default setting prohibits remote connections. Resetting the value either to a nic’s IP address or just using the wild card will make it accessible.
TIP: As postgres, execute the following in a psql session in order to locate your configuration files.
select distinct sourcefile from pg_settings;
For those people feeling fancy, one can bind the postgres service to more than one IP address as a comma-separated list:
listen_addresses = '*'
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
An alternate method updating the runtime parameters can also be accomplished using the SQL statement:
postgres=# ALTER SYSTEM SET listen_addresses = '*'; ALTER SYSTEM
The final step, restarting the service, is where we start splitting hairs again:
- Redhat distributions require dataclusters to be manually created before they can be administered.
- PostgreSQL Debian distributions, including Ubuntu, automatically creates and starts up the datacluster.
Systemd:
- Redhat/Centos:
/usr/pgsql-11/bin/postgresql-12-setup initdb
systemctl start|stop postgresql-12
- Debian/Ubuntu:
systemctl restart postgresql
Debian derived Linux Distributions include a collection of command-line utilities in order to administer the PostgreSQL service:
# example CLI # pg_ctlcluster
Usage: /usr/bin/pg_ctlcluster <version> <cluster> <action> [-- <pg_ctl options>]
# restarting postgres version 12 on a Debian derived distribution pg_ctlcluster 12 main restart
After a successful service restart you should get something similar to the following:
Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 27929/postgres
And finally, the remote connectivity test:
# # THE REMOTE LOGIN # psql 'host=myhost user=postgres password=mypassword' -c "select 'hello world' as greeetings "
Then there’s replication, but that’s another blog altogether.
That’s it for now!
Our white paper “Why Choose PostgreSQL?” looks at the features and benefits of PostgreSQL and presents some practical usage examples. We also examine how PostgreSQL can be useful for companies looking to migrate from Oracle.
by Robert Bernier via Percona Database Performance Blog
Comments
Post a Comment