(For more resources related to this topic, see here.)
Installing the PostgreSQL database server
Most operating systems that come with a package management solution for open source software make PostgreSQL packages available for installation. In this recipe, we will install PostgreSQL from a package and set it up on your system. Installing the server package automatically installs the PostgreSQL command-line client package, as well.
How to do it…
Perform the following steps to install the PostgreSQL database server:
- Install the PostgreSQL database server package.
In most package repositories, the PostgreSQL server package is simply named postgresql-server. If your distribution allows you to select among different versions of PostgreSQL, the package names will contain version numbers such as postgresql-9.1 or postgresql93-server. Pick the package with the latest version unless you have reasons to stick with an older one.
- Click the Refresh Modules link at the bottom of Webmin’s main menu and reload the browser to update the menu.
- Navigate to Servers | PostgreSQL Database Server. You should see a screen that lists installed databases. It should include the default databases such as postgresql and template1.
- If you do not see the list of databases, but instead a message which indicates that the database system has not yet been initialized, click the Initialize Database button.
At the bottom of the screen, if you see the message, Warning: The Perl modules DBI and DBD::Pg are not installed on your system, click the link and follow Webmin’s instructions to install the missing Perl modules.
- Navigate to System | Bootup and Shutdown and verify that the init script, postgresql, is set to start at boot. If it isn’t, select its checkbox and click the Start Now and On Boot button.
How it works…
Webmin helps you find and install the postgresql-server package from your distribution’s repositories. The package installs the database server, client, and an init script that starts the server during system boot.
Before Postgres can be used to manage databases, a new cluster must be created. A PostgreSQL cluster is a collection of databases managed by a single server. Creating the cluster involves the creation of a directory in which the database files will be stored, and filling role, because all new databases in the cluster will be made by copying this template.
If your package installation script does not initialize a database cluster for you, you can ask Webmin to do it by clicking the Initialize Database button. This runs the following subcommand of the init script:
/etc/rc.d/init.d/postgresql initdb
Locating the PostgreSQL server configuration files
The main configuration file of the PostgreSQL server is usually named postgresql.conf, and is stored in the database cluster data directory by default. Various system distributions move this configuration outside of the data directory and place it in a different location, for example, in the /etc/ directory. In this recipe, we will demonstrate how to find the postgresql.conf and change it to modify the server’s configuration. Webmin does not assist you in the modification of the basic settings of PostgreSQL, so you will need to edit the configuration file manually.
Getting ready
Make sure that the PostgreSQL server is installed and running, and that you are able to connect to it via Webmin before starting. The recipe, Installing the PostgreSQL database server, provides more information.
How to do it…
Follow these steps to locate PostgreSQL’s main configuration file on your system:
- Navigate to Servers | PostgreSQL Database Server.
- Click the icon of the default database, postgres.
- Click the Execute SQL button.
- Enter the following SQL command in the provided text area:
SHOW config_file;
- Click the Execute button and you will see the output of the SQL command, which provides the full path to the main server configuration file, as shown in the following screenshot:
How it works…
When an init script starts the PostgreSQL server, it may specify the location of the database cluster’s data directory or the location of the server’s main configuration file (customarily called postgresql.conf). By default, the main configuration file is stored inside of the data directory, but package maintainers often move it to a different location (such as /etc/) to keep system configuration files in order. The SQL command, SHOW config_file;, can be used to check where the main configuration file is located.
There’s more…
The location of other configuration files and the values of other settings can also be displayed using the SQL SHOW command.
Determining location of other configuration files and data files
Use the following commands to check where other configuration files are located:
|
Setting |
Command |
|
Main configuration file (postgresql.conf) |
SHOW config_file; |
|
Data directory |
SHOW data_directory; |
|
Host-based access configuration file (pg_hba.conf) |
SHOW hba_file; |
|
Identity mapping file (pg_ident.conf) |
SHOW ident_file; |
|
Directory where the Unix-domain socket will be created |
SHOW unix_socket_directory; |
Checking values of other settings
You can also reveal the values of all settings by issuing the following command:
SHOW all;
Allowing access to PostgreSQL over the network
Programs that access PostgreSQL databases, which are called clients, may be running on the same machine as the server. In this case, the client and server will communicate most efficiently using a Unix-domain socket, a channel of inter-process communication accessed through the filesystem such as a file or directory. Access to a socket is controlled by filesystem permissions.
Other client programs may be able to communicate only over TCP network sockets. These clients may connect to the local server using the loopback interface and IP address of 127.0.0.1.
However, if a client program is located on a machine other than the server, then communication between them must take place over the network using the TCP protocol. There are a number of ways to set up network connections for PostgreSQL. The most efficient but least secure method is to use a direct unencrypted connection between the client and server. This method has the drawback that unencrypted information could potentially be eavesdropped upon or even modified in transit over the network. Because database systems are usually designed to be as efficient as possible, this type of communication is used often, but should only be deployed inside of a secure network. We will describe how to enable this type of communication in this recipe.
In order to make network access to your PostgreSQL server more secure, you can choose to encrypt the transferred information using SSL. This prevents eavesdropping and man-in-the-middle attacks, but leaves the PostgreSQL server’s network port exposed and potentially vulnerable to brute-force password guessing and other attacks.
If you really need security, for instance, to access your database server over the Internet, you should probably choose a third option: send the PostgreSQL traffic over an encrypted SSH tunnel. This is the least efficient of the described transmission methods, but it generates the fewest security concerns. For more information, take a look at the recipe, Accessing the PostgreSQL server over an SSH tunnel.
Getting ready
In this recipe, we will prepare your PostgreSQL server to accept incoming network connections. In order to test the connection, we will need access to two computers attached to the same network: the server and a client machine. Make note of the server and client’s IP or domain name before starting.
How to do it…
The steps in this recipe will be divided into five sections:
- First, we’ll instruct PostgreSQL to listen for incoming network connections on the standard port (5432).
- Next, we’ll create a database user named dbuser.
- Then, we will create a database named testtdb.
- We will allow remote access to the database.
- And finally, we will test the setup by connecting to our server from a secondary client machine.
Perform the following steps to instruct the PostgreSQL server to listen for network connections:
- Allow incoming TCP traffic to port 5432 through your firewall.
- Find the location of the PostgreSQL main server configuration file (postgresql.conf). Refer to the recipe, Locating the PostgreSQL server configuration files, for detailed instructions.
- Within the postgresql.conf file, find the line with the listen_addresses directive. This line may be commented out (start with the # character). Change the line to the following:
listen_addresses = '*'
The most effective way to edit files on your server is to use an editor such as Vim or Nano in a terminal session (for example, over SSH). But to make a small change in a configuration file, you do not need to leave Webmin.
- We must restart the server after making configuration changes. Navigate to Servers | PostgreSQL Database Server, click the Stop PostgreSQL Server button, and then click the Start PostgreSQL Server button.
Your PostgreSQL server will now listen for incoming network connections on port 5432.
Perform the following steps to create a new user:
- Navigate to Servers | PostgreSQL Database Server | PostgreSQL Users.
- Click the Create new user link.
- Set Username to dbuser and assign a strong password in the Password field.
- Answer No to the Can create databases? and Can create users? questions.
- Set Valid until to Forever:
- Click the Create button.
Perform the following steps to create a database:
- Navigate to Servers | PostgreSQL Database Server
- Click the Create a new database link.
- Set Database name to testdb.
- Set Owned by user to dbuser.
- Set Template database to template1:
- Click the Create button.
Perform the following steps to grant a user remote access to the database:
- Navigate to Servers | PostgreSQL Database Server.
- Click the Allowed Hosts icon.
- Click the Create a new allowed host link.
- Set Host address to Single host and enter the IP address of the client computer (for example, 10.10.10.100).
If the client can connect from more then one IP, you can specify a subnet by providing a network and netmask or CIDR length. For instance, to grant access to all computers in the 10.10.10.* subnet, you could specify the network as 10.10.10.0 and either the netmask as 255.255.255.0 or the CIDR length as 24.
- Set SSL connection required? to Yes.
You can shave off a little performance overhead by not using SSL, but you should only do that on entirely trusted networks.
- Set Database to testdb.
- Set Users to Listed users and enter dbuser.
- Set Authentication mode to MD5 encrypted password:
- Click the Create button.
- We’ll need to restart the server one more time to load the new access configuration. Navigate to Servers | PostgreSQL Database Server, click the Stop PostgreSQL Server button, and then click the Start PostgreSQL Server button.
On a busy production system it would be a bad idea to restart the database server unnecessarily, although that is the sure way of reloading all settings. After changing access settings, you don’t really need to restart the server. You could send it a SIGHUP signal instead. This signal instructs Postgres to reload its configuration. On systems equipped with the pg_ctl program, this can be achieved by issuing the following command:
$ sudo pg_ctl reload
On systems with the pg_ctlcluster command, you will need to specify the server version and cluster name, for example:
$ sudo pg_ctlcluster 9.1 main reload
Testing the connection
Try to connect to your database server from the client machine that uses the IP we specified. If your other machine has the PostgreSQL command-line client installed, you can test the connection by typing in this command at the terminal. However, substitute postgresql-host with the IP or domain name of your Postgres server as follows:
$ psql -h postgresql-host -U dbuser testdb testdb=# q
If the connection is successful, you should arrive at the PostgreSQL prompt (testdb=#). Type q and press Enter to exit.
How it works…
In order to enable network access to the PostgreSQL database server, we needed to modify two configuration files. We edited the main configuration file (postgresql.conf) manually to instruct the server to listen for incoming network connections on all network interfaces. The second file, which was edited through Webmin’s interface, is the host-based authentication configuration (pg_hba.conf). This file instructs the server which users should be allowed to connect from which network hosts and how they should be required to authenticate.
Webmin added the following line to pg_hba.conf:
hostssl testdb dbuser 10.10.10.100 255.255.255.255 md5
The preceding line instructs the server to accept SSL connections to the testdb database by the dbuser user if the connection originated from the IP address 10.10.10.100. The user should be asked to provide an MD5-encrypted password for authentication.
Another line in pg_hba.conf can look like the following:
local all postgres peer
This line instructs the server to accept connections made locally over the Unix socket. These connections use the peer authentication method, which checks the username of the system account running the connecting client program. If the system username matches a Postgres account name, then the connection is considered authenticated. Password checking is not performed in peer authentication. The preceding line of code will allow the system account postgres to access all databases.
