PostgreSQL Setup on Debian

Warning: This post is outdated. It is here for reference purposes only.

This post will go over how to install, setup and configure a functional PostgreSQL database on a Debian 7 server. I will be doing this on a virtual machine in VirtualBox, but these steps should be valid for any Debian server. If you haven't ever setup a server like this, first read my 3-part series that covers setting up a basic Debian 7 virtual machine. The first step is to install Postgres, which is very easy, then we will move on to configuring the server, creating databases and users, and even making it possible to connect to the server remotely.

I can't go over every possible option in this post, but will do my best to give a pretty decent way to setup a database server. You should evaluate all of my recommendations with your needs and policies in mind.

Installing PostgreSQL

Using apt-get, it is very easy to install PostgreSQL.

sudo apt-get install postgresql

Once it is installed, you can confirm that is installed and running by attempting to start the postgresql service.

sudo service postgresql start

Configure the Server

Now that PostgreSQL is installed the next step is to configure it. While the default settings are pretty good (IMO) you should always browse through the default settings just to make sure everything looks as expected. There are two configuration files you will want to become familiar with: pg_hba.conf and postgresql.conf. My configuration files are at /etc/postgresql/9.1/main/ but your actual path may vary. These files in a default installation have loads of helpful explanations included in the default configuration files and I highly recommend that you read through them.

Configure Accepted Connections

The pg_hba.conf file controls access to the database. You determine what connection method (local, host, hostssl, or hostnossl), database(s), user(s), and authentication method are accepted in this file. The short example below is very similar to what I use on most of my virtual machines on my laptop. Take a look, and I'll explain each line next.

local all postgres peer  
local all adminuser peer  
local demo_jira jira_user md5

It would be a rare circumstance that you would want to disable the first line. The comments in the default pg_hba.conf file explain why, so you should check it out there for more details. The second line allows the user "adminuser" in Debian to have access to all databases on the localhost without requiring a password. That means when I SSH into the server (which is authenticated by my public/private key pair) as the Unix user adminuser, I am able to type psql and not have to type a password. The third line will allow a not-yet existing jira_user access to a not-yet created demo_jira database from the localhost using md5 authentication. We will create both the database and database users (roles) after we finish up configuring the server.

With the way this is currently setup, no connections from external networks will be accepted.

Always remember the principle of least privilege!

Remaining Server Configuration

That's one file taken care of, next let's look at the postgresql.conf file. The first few options set the path for the data files, define the pg_hba.conf file path, and the pg_ident.conf file. All of these changes require restarting the postgresql service.

data_directory = '/var/lib/postgresql/9.1/main'  
hba_file = '/etc/postgresql/9.1/main/pg_hba.conf'  
ident_file = '/etc/postgresql/9.1/main/pg_ident.conf'

The next section defines what IP address and port to listen on. If the database server is only intended to be accessed by another server, the listen_addresses allows you to only allow specific IP addresses if that's a possibility for you. Unless you need the default port (5432) I recommend changing this to another random value. Changing the ports from defaults won't save your server if someone is actively targeting it, but it will vastly reduce the amount of automated attacks against it. You should review the rest of the settings to see what they are and if they apply to you, but unless you have a problem with the defaults, or have a reason to change them, you're done with these files for now.

Create Users and Databases

Right now the only user that can actually connect to the database is the postgres Unix user that is created when you install PostgreSQL Even though we configured the server to accept a connection from the users adminuser and jira_user, neither user exists in the database yet. To remedy this, switch to the postgres user.

$ sudo su - postgres

Now you can create the user for your user account.

createuser adminuser

I want this adminuser to be a superuser so I don't have to su to the postgres user all the time, but most users should not be a superuser. Because I've already setup the config (hb_pga.conf) to trust the user adminuser, I don't need to set a password if I will only be connecting from the local host.

Exit out of the postgres user to back to adminuser and test your new access. The createdb command will create a new database with the default settings. The last command connects to the psql terminal and connects to the test database that was created. If you get errors here, something wasn't done quite right in one of the prior steps.

$ exit  
$ createdb test  
$ psql -d test

Obviously, we don't really want a database named test, so let's quit the psql console and drop that database. Then we'll create a database for an application, such as Atlassian's Jira. While we're at it, we'll create a user for the application to access the database.

test=# \q  
$ dropdb test  
$ created demo_jira  
$ createuser -P jira_user

Using the -P flag will prompt you to enter a password for the user followed by a few prompts. Answer "n" to the three prompts, this user shouldn't have any of that elevated access as that would be a security concern.

Now that the user is created, let's grant permissions for it to connect to the demo_jira database. Log in to psql again as the adminuser, this time we will connect to the demo_jira database. Once in psql, change the owner to the jira_user role to let the application control the database it needs. The user will be allowed to connect using it's password from the local host only because of the settings in the pg_hba.conf configuration file.

$ psql -d demo_jira  
demo_jira=# ALTER DATABASE demo_jira OWNER TO jira_user;  
demo_jira=# \q

Now for the moment of truth... test to see if we can access the demo_jira database as the jira_user.

psql -U jira_user -d demo_jira  
Password for user jira_user:

Hopefully everything worked out well to this point. Now for a few commands to explore the database, and to test that our jira_user isn't allowed to do too much. The \list command shows all databases on the server. Use the \c command to change databases. Your jira_user should get the same FATAL error that I show below.

demo_jira=>\list  
demo_jira=>\c postgres -- attempt to connect to the postgres database  
FATAL: Peer authentication failed for user "jira_user"  
Previous connection kept

Allowing Remote Connections

Let's say that I want my adminuser to be able to access the database remotely, which is fairly common. For example, I have PostgreSQL installed on a virtual machine but I want to access it from PgAdmin3 to more easily explore and query the data. Let's go through the steps to make this possible.

First we must edit the pg_hba.conf file once again to add a new connection option.

sudo nano /etc/postgresql/9.1/main/pg_hba.conf

You need to add one new line to the file:

host all adminuser 192.168.56.106 md5

Obviously your IP address will be different, but this will allow the connection for the adminuser only, using a password. Next, update the postgresql.conf to allow the connection to the server. Find this line:

listen_addresses = 'localhost'

And replace with:

listen_addresses = 'localhost, 192.168.56.106'

Save and close, and restart the postgresql service.

sudo service postgresql restart

If you remember back to creating the database user for adminuser, we did not provide a password so we will have to fix that now. While logged into the server I will pass this one line query to psql to set my password.

$ psql -d postgres -c "ALTER ROLE adminuser WITH PASSWORD 'PutSecurePasswordHere';"

Test Remote Connection

I'm going to fire up PgAdmin3 on my local machine and see if I can successfully connect to the database now. I create a new connection:

I expanded the Databases and Login Roles sections of this to demonstrate what our PostgreSQL server looks like with what we've done. Notice under the Databases, there is the default "postgres" database and the demo_jira database that we created. Inside the demo_jira database I expanded the Schemas group to show the public schema, the default schema in PostgreSQL. Down in the Login Roles section you can see the default postgres role along with the two roles we created: adminuser and jira_user.

Summary

At this point, the server is setup and ready to go. We set it up with a separate super user that is able to connect remotely to develop and administer any databases. Further, any application or language that supports PostgreSQL (be it packaged software, web app, or custom use) has a user that can manage its own database.

I have tried to include all the vital steps along the way, but feel free to let me know in the comments if you have a different experience than mine!