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.
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:
postgresql.conf. My configuration files are at
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
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
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.
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
\list command shows all databases on the server. Use the
to change databases. Your jira_user should get the same FATAL error that I
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
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.
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!
Published December 19, 2014
Last Updated April 13, 2019