Running a PostgreSQL Database Server

Exclusive offer: get 50% off this eBook here
Webmin Administrator's Cookbook

Webmin Administrator's Cookbook — Save 50%

Over 100 recipes to leverage the features of Webmin and master the art of administering your web or database servers with this book and ebook

€19.99    €10.00
by Michał Karzyński | March 2014 | Cookbooks Networking & Telephony Open Source Web Development

This article by Michal Karzynski, the author of this book Webmin Administrator's Cookbook, will cover tasks related to setting up and running your database server. We'll demonstrate how Webmin can be used to create and edit databases, back them up and manage database users.

(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:

  1. 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.

  2. Click the Refresh Modules link at the bottom of Webmin's main menu and reload the browser to update the menu.
  3. 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.
  4. 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.

  5. 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:

  1. Navigate to Servers | PostgreSQL Database Server.
  2. Click the icon of the default database, postgres.
  3. Click the Execute SQL button.
  4. Enter the following SQL command in the provided text area:

    SHOW config_file;

  5. 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:

  1. Allow incoming TCP traffic to port 5432 through your firewall.
  2. 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.
  3. 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.

  4. 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:

  1. Navigate to Servers | PostgreSQL Database Server | PostgreSQL Users.
  2. Click the Create new user link.
  3. Set Username to dbuser and assign a strong password in the Password field.
  4. Answer No to the Can create databases? and Can create users? questions.
  5. Set Valid until to Forever:

  6. Click the Create button.

Perform the following steps to create a database:

  1. Navigate to Servers | PostgreSQL Database Server
  2. Click the Create a new database link.
  3. Set Database name to testdb.
  4. Set Owned by user to dbuser.
  5. Set Template database to template1:

  6. Click the Create button.

Perform the following steps to grant a user remote access to the database:

  1. Navigate to Servers | PostgreSQL Database Server.
  2. Click the Allowed Hosts icon.
  3. Click the Create a new allowed host link.
  4. 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.

  5. 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.

  6. Set Database to testdb.
  7. Set Users to Listed users and enter dbuser.
  8. Set Authentication mode to MD5 encrypted password:

  9. Click the Create button.
  10. 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.

Webmin Administrator's Cookbook Over 100 recipes to leverage the features of Webmin and master the art of administering your web or database servers with this book and ebook
Published: March 2014
eBook Price: €19.99
Book Price: €32.99
See more
Select your format and quantity:

Accessing the PostgreSQL server over an SSH tunnel

If your server is hosting a website on the Internet and running a database system on the same machine, it is safer to disable remote network access to the database. On the other hand, you may still want to manage your databases remotely. You can do so by tunneling PostgreSQL traffic over an SSH connection.

One of the most important aspects of a database system is the speed with which it can find and return the data that you ask for. Tunneling traffic over SSH will add significant overhead to this communication. This solution is great for intermittent management tasks, but not suitable as a replacement for a direct connection to your database system.

Getting ready

Before you can access the PostgreSQL server through an SSH tunnel, you will need to make sure that an allowed hosts entry exists in the pg_hba.conf file. This entry should allow users from the loopback IP 127.0.0.1 to authenticate using MD5-encrypted passwords. Take a look at the recipe, Allowing access to PostgreSQL over the network, for more information. This is what the appropriate line in pg_hba.conf would look like:

# IPv4 local connections: host all all 127.0.0.1/32 md5

The server you want to connect to must run both the PostgreSQL server and an SSH server. The remote client machine must have an SSH client and PostgreSQL client software installed. Make a note of the IP address or domain name of the server (postgresql-host), the SSH username (ssh-user), the PostgreSQL user (postgresql-user), and the database name (database-name). Substitute them in the following recipe.

How to do it...

In order to create an SSH tunnel for PostgreSQL, follow these steps:

  1. Issue the following command on your client machine:

    $ ssh -N -L 15000:localhost:5432 ssh-user@postgresql-host

    This creates a tunnel between port 15000 on your client machine and port 5432 of the server. You can now access the remote database by making a PostgreSQL connection to your client computer's local port 15000.

  2. Issue the following command on the client system to test the connection:

    $ psql -h 127.0.0.1 -p 15000 -U postgresql-user database-name

How it works...

The SSH client acts as an intermediary in the communication between the PostgreSQL client running on your machine and the remote server. It opens port 15000 on the client machine and listens for incoming connections. All packets arriving at port 15000 are encrypted and forwarded over SSH to the server. On the server side, SSH receives the packets, decrypts them, and sends them to port 5432. Answers are sent back in the opposite direction over the same channel.

Creating a new database

Creating a new PostgreSQL database through Webmin's interface is very quick and simple.

How to do it...

Follow these steps to create a database:

  1. Navigate to Servers | PostgreSQL Database Server.
  2. Click the Create a new database link.
  3. Enter a Database name, for instance, new_db.
  4. Select the user who will have administrative rights to the database from the Owned by user dropdown.
  5. Leave Character set encoding and Database file path set to Default.
  6. Set Template database to template1.
  7. Click the Create button.

How it works...

Webmin takes the information you provide and creates a new database by connecting to the PostgreSQL server and executing the following command:

CREATE DATABASE new_db WITH OWNER="dbuser" TEMPLATE = template1;

Postgres creates the new database by making a copy of a selected template. The database, template1, is installed by default to serve as a source of default settings for newly created databases. If you want new databases to have different settings, for instance, character set and collation, you can introduce these changes to your template database.

Another way to create a database is to execute the createdb command as the user, postgres, for instance:

postgres@postgresql-host:~$ createdb --owner dbuser new_db

Creating users and granting permissions

Creating PostgreSQL users through Webmin is very simple. Users can be designated as owners of newly created databases and will have complete access and administrative rights to the databases they own. Users may also be granted limited privileges on specific database tables.

How to do it...

In this recipe, we will create a new user called dbuser and grant selected privileges on a table named dbtable in a database called testdb.

Perform the following steps to create a user:

  1. Navigate to Servers | PostgreSQL Database Server | PostgreSQL Users.
  2. Click the Create new user link.
  3. Set Username to dbuser and set a strong password in the Password field.
  4. Answer No to the Can create databases? and Can create users? questions.
  5. Set Valid until to Forever.
  6. Click the Create button.

Perform the following steps to grant user privileges on a database table:

  1. Navigate to Servers | PostgreSQL Database Server.
  2. Click the Granted Privileges icon.
  3. Click the name of the database object for which you want to modify permissions (for instance, the table name, dbtable).
  4. Select the user, dbuser, from the User dropdown.
  5. Mark the checkboxes next to the privileges you would like to grant:

  6. Click the Save button.

How it works...

Webmin creates a new database user by connecting to the PostgreSQL server and executing the following command:

CREATE USER 'dbuser' WITH PASSWORD '***' NOCREATEDB NOCREATEUSER;

Another way to create a database user is to execute the createuser command as the user postgres, for instance:

postgres@postgresql-host:~$ createuser dbuser Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n

Privileges are assigned to users through the GRANT command, for instance:

GRANT SELECT,UPDATE,INSERT,DELETE ON "public"."dbtable" to 'dbuser';

The PostgreSQL manual provides the following definitions of privileges:

Privilege

Definition

SELECT

This allows SELECT from any column of the specified table.

UPDATE

This allows UPDATE of any column of the specified table.

INSERT

This allows INSERT of a new row into the specified table.

DELETE

This allows DELETE of a row from the specified table.

RULE

This allows the creation of a rule on the table.

REFERENCES

To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced tables.

TRIGGER

This allows the creation of a trigger on the specified table.

There's more...

PostgreSQL does not make it easy to grant privileges to an entire database. In order to grant the user named dbuser access to all the tables defined in the public schema, execute the following command:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO 'dbuser';

Postgres databases may be subdivided into schemas. Each schema contains its own set of tables independent of other schemas and may use different user privileges. By default, each database contains only one schema called public, and all tables are assigned to it.

Inserting new objects also requires access to sequence objects, which may be granted as follows:

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dbuser;

Unfortunately, when you add new tables to the database or add another schema, you will have to execute the commands again. Another option is to set default permissions for the objects by using the ALTER DEFAULT PRIVILEGES command:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON
TABLES TO 'dbuser';
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON
SEQUENCES TO 'dbuser';

Summary

The article covered tasks related to setting up and running your database server. We also discussed how to create new database. Also the article covered how to create users and grant permissions to database.

Resources for Article:


Further resources on this subject:


Webmin Administrator's Cookbook Over 100 recipes to leverage the features of Webmin and master the art of administering your web or database servers with this book and ebook
Published: March 2014
eBook Price: €19.99
Book Price: €32.99
See more
Select your format and quantity:

About the Author :


Michał Karzyński

Michał Karzyński, with a scientific research background in the areas of molecular biology and bioinformatics, has been running Unix-like operating systems since 2002. He works as a web application developer, programming in dynamic languages such as JavaScript, Python, Perl, and PHP. He specializes in designing programming interfaces between servers and client applications based on the HTTP protocol. He has been using Webmin for over five years to assist in setting up and managing servers. He is currently employed as a project manager at the Gdańsk University of Technology in Poland. His blog can be found at http://michal.karzynski.pl.

Books From Packt


 SELinux System Administration
SELinux System Administration

 Scalix: Linux Administrator's Guide
Scalix: Linux Administrator's Guide

Web Penetration Testing with Kali Linux
Web Penetration Testing with Kali Linux

 Kali Linux - Assuring Security By Penetration Testing
Kali Linux - Assuring Security By Penetration Testing

 Website Development with PyroCMS
Website Development with PyroCMS

Getting Started with PhantomJS
Getting Started with PhantomJS

Moodle Administration
Moodle Administration

Kali Linux Social Engineering
Kali Linux Social Engineering


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software