Secure PostgreSQL setup on Debian/Ubuntu

postgresqlPostgreSQL is an OpenSource database server that is sometimes slower, but more powerful when compared to MySQL for example. For typical MySQL user complicated authentication system of PostgreSQL may be a bit confusing (they expect just login/password) but it has some advantages over password-based mechanisms: security. I'll show you how to setup ident-based authentication for your applications (authentication method that uses underlying operating system mechanisms).

Installation, assuming version 8.4 is available for your OS (may be different):

$ sudo apt-get install postgresql-8.4

Switch to postgres operating system account for database management, all the following commands are executed from postgresql account:

$ sudo su - postgres

First, create database as a namespace for your tables:

postgres:$ createdb mydatabase

Secondly, create application PostgreSQL user. We do not want to grant too much permissions to this user:

postgres:$ createuser myuser --no-superuser --no-createdb --no-createrole

Right now we have to grant access to newly created database access to PostgreSQL user (just access to single database):

postgres:$ psql mydatabase -c 'GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser'

Finally we have to allow connections from operating system account darek to PostgreSQL account myuser:

/etc/postgresql/8.4/main/pg_ident.conf
 mymap  darek   myuser
/etc/postgresql/8.4/main/pg_hba.conf
 local   all         all                               ident map=mymap

In order the changes to have effect you have to reload PSQL configuration files:

postgres:$ /etc/init.d/postgresql reload

What is happening here requires a bit of explanation:

  • A mapping is created from local Linux user (darek) to PostgreSQL account (myuser) called "mymap"
  • Local connections by socket allow to authenticate user darek using mapping "mymap"

As a result "darek" can login as myuser from his account, let's test this:

$ psql mydatabase myuser -c "CREATE TABLE mytable(x int)"
 CREATE TABLE

As you probably have noticed no passwords were set. Authentication is done by operating system. This is much safer than storing passwords in configuration files of application.

This entry was posted in en and tagged , . Bookmark the permalink.