(For more resources related to this topic, see here.)
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.
Perform the following steps to install the PostgreSQL database server:
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.
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.
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
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.
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.
Follow these steps to locate PostgreSQL’s main configuration file on your system:
SHOW config_file;
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.
The location of other configuration files and the values of other settings can also be displayed using the SQL SHOW command.
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; |
You can also reveal the values of all settings by issuing the following command:
SHOW all;
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.
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.
The steps in this recipe will be divided into five sections:
Perform the following steps to instruct the PostgreSQL server to listen for network connections:
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.
Your PostgreSQL server will now listen for incoming network connections on port 5432.
Perform the following steps to create a new user:
Perform the following steps to create a database:
Perform the following steps to grant a user remote access to the database:
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.
You can shave off a little performance overhead by not using SSL, but you should only do that on entirely trusted networks.
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.
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.
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…