PostgreSQL

My original reason for installing this database system was that I wanted to use SQL-Ledger. SQL-Ledger is a double-entry accounting system build on PostgreSQL.

The following tutorial will show you how to get PostgreSQL up and running with md5 password authentication.

Installation

To install PostgreSQL, cd into the latest version in your ports collection. At the time of writing, this was 8.1:

cd /usr/ports/databases/postgresql81-server
make install clean distclean

.....................................................................
.               Options for postgresql-server 8.1.0                  .
. .................................................................. .
. .[X] NLS               Use internationalized messages            . .
. .[ ] PAM               Build with PAM support (server only)      . .
. .[ ] MIT_KRB5          Build with MIT's kerberos support         . .
. .[ ] HEIMDAL_KRB5      Builds with Heimdal kerberos support      . .
. .[ ] OPTIMIZED_CFLAGS  Builds with compiler optimizations (-O3)  . .
. .[ ] LIBC_R            Link w/ libc_r, used by plpython (server) . .
. .[ ] THREADSAFE        make libpq thread safe                    . .
. .[ ] TESTS             Allows the use of a check target (server) . .
. .[ ] DEBUG             Builds with debugging symbols             . .
. .[ ] ICU               Use ICU for unicode collation (server)    . .
. .[ ] INTDATE           Builds with 64-bit date/time type (server). .
. .                                                                . .
. .                                                                . .
. .                                                                . .
. .                                                                . .
......................................................................
.                       [  OK  ]       Cancel                        .
......................................................................

Actually you need both a client and a server, but since the server depends on the client, it will be installed automatically. So after this, you'll see that you have these two packages installed:

pkg_info | grep postgres
postgresql-client-8.1.0 PostgreSQL database (client)
postgresql-server-8.1.0 The most advanced open-source database available anywhere

Add this line in /etc/rc.conf to make sure that PostgreSQL automatically starts at boot time:

postgresql_enable="YES"

Start PostgreSQL

The installation creates a new unix user named pqsql. In the home directory of this user, you'll find the PostgreSQL configuration files - see ~pgsql/data/ (which actually is /usr/local/pgsql/data/). Pay special attention to pg_hba.conf and postgresql.conf.

pg_hba.conf is controlling client authentication
postgresql.conf is the configuation file for the database server program (aka postmaster)

I will get back to those files later. First lets get the database up and running:

Execute the below command. This will initialize the database - it will not ask you any questions and it only takes a few seconds:

/usr/local/etc/rc.d/010.pgsql.sh initdb

You can then start PostgreSQL by running:

/usr/local/etc/rc.d/010.pgsql.sh start

pg_hba.conf - securing the system

The pgsql unix user mentioned earlier, is also created by default as a superuser in PostgreSQL. Superuser means a user with no restrictions.

At this point anyone on localhost can connect to the database using the pqsql user, for example by using the psql terminal:

psql postgres -U pgsql

This is a big security risk, especially if other people have access to your server. Because of this I recommend you set a password for the pgsql user and then tell the system to always prompt for a password.

Connect to PostgreSQL and generate an md5 password. Then set this password for the pgsql user. Important! The format of the md5 password that you're generation is <password><username>. So if you choose bigdog10 as the password for the pgsql user, you would pass the string 'bigdog10pgsql'. Replace 'passwordusername' in the below example with this string.

psql postgres -U pgsql

Welcome to psql 8.1.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# SELECT 'md5'||md5('passwordusername') as md5pass;
       md5pass
-------------------------------------
md55a231fcdb710d73268c4f44283487ba2
(1 row)

postgres=# alter role pgsql with encrypted password 'md55a231fcdb710d73268c4f44283487ba2';
ALTER ROLE

Adding a password is not enough, as mentioned before, you must also configure the system to prompt for the password. This is done by altering pg_hba.conf. The below configuration will allow all local users to connect, but only with password authentication. All remote connections are rejected.

Open ~pgsql/data/pg_hba.conf and locate this section:

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5
# Reject all remote connections
host    all         all         0.0.0.0/0             reject

The chapter "Client Authentication" in the PostgreSQL documentation has some excellent examples of these settings.

Remember to reload PostgreSQL to make the changes in the configuration file take effect (Note: you must su to be logged in as the pgsql user to do this).:

su - pgsql
pg_ctl reload

From now on, you will always be prompted for a password when connecting to your PostgreSQL database system. For example, the below command will connect you to the postgres database using the pgsql user:

psql postgres -U pgsql
Password for user pgsql: bigdog10

Replace bigdog10 with the password you've chosen for the pgsql user. The password will not show on the screen when you type, I've just presented it here for clarity.

postgres.conf - postmaster settings

For postmaster settings, see ~pgsql/data/postgresql.conf. I have not made any changes to this file.

Creating users

When you create users (aka roles), they are global to the database system. But this does not mean that a user can connect to and use any database. To use a database the user must own it or been given access to use it. This concept is called privileges, and this is not covered here. See 'Database Roles and Privileges' in the documentation.

In these examples I have written the passwords, but they actually will not show on the screen when you do this. I've only added them for clarity.

To create a user for the first time, you must use the pgsql user. For example:

su - pgsql
createuser john
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
Password: bigdog10
CREATE ROLE

Replace bigdog10 with the password you chose earlier for the pgsql user.

Note that this example shows how to create a PostgreSQL user from the unix command line with the createuser command. It is also possible to do it with SQL. The documentation describes both methods in detail.

The example above shows how to create a standard user with default values. Be aware that the defaults does not provide a password, and it is very important that the user has a password if you setup your system to use password authentication. If this is the case, you must do this instead:

Assuming you have chosen to authenticate with md5 passwords, you must first connect to the database system and generate an md5 password. Note that the string used for generating the md5 is the password followed by the username. In this example we will create an account for john with password blackcat. See a detailed explanation after the below example:

psql postgres -U pgsql
Password for user pgsql: bigdog10

postgres=# select 'md5'||md5('blackcatjohn') as md5pass;
               md5pass
-------------------------------------
 md540d55749b9de81abde4931805f545f57
(1 row)
postgres=# \q

createuser -E -P john

Enter password for new role: md540d55749b9de81abde4931805f545f57

Enter it again: md540d55749b9de81abde4931805f545f57

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

Password: bigdog10

CREATE ROLE

You start out by connecting to the psql terminal and create the md5 string. Then you exit psql with \q. You are now back in the unix commandline. Use the createuser command with the options -E and -P. The -E actually tells the system to encrypt the password, but it will recognize that it is already encrypted with md5, so it will not be encrypted again. The -P option ensures that you will be asked for the password. After this you are asked if the user is a superuser and/or allowed to create databases and other users. When all this is done, you are finally asked for the pgsql password.

To get a list of created users and their attributes...

psql postgres -U pgsql
Password for user pgsql: bigdog10
postgres=# select * from pg_roles;

When you hit the enter button, you'll be presented with the list of users.