postgresql setup 2: authentication and roles

After you've created the database cluster, it's time to set up authentication and roles.

Authentication is handled through the pg_hba.conf (HBA stands for Host-Based Authentication) file. This configuration file supports seven formats of authentication rules:

local      database  user  auth-method  [auth-options]
host       database  user  CIDR-address  auth-method  [auth-options]
hostssl    database  user  CIDR-address  auth-method  [auth-options]
hostnossl  database  user  CIDR-address  auth-method  [auth-options]
host       database  user  IP-address  IP-mask  auth-method  [auth-options]
hostssl    database  user  IP-address  IP-mask  auth-method  [auth-options]
hostnossl  database  user  IP-address  IP-mask  auth-method  [auth-options]

For a standalone machine where local users are trusted, a rule of:

local      all       all                 trust

is sufficient. If password authentication is needed, several methods are supported, including MD5 hashing, Kerberos, LDAP, SSL Certificates, and PAM authentication. The PostgreSQL documentation explains authentication in detail.

The next step is to create user roles. Once the user logs in, PostgreSQL needs to know what to allow the user to do. A role can be assigned to a user or a group.

To begin assigning roles, log in to psql. As user postgres, you can simply type psql on the command line.

If your username is myuser, and you want to be able to log into PostgreSQL and create databases, and you want to encrypt the password in the database, you can create the role like this:

CREATE ROLE myuser WITH CREATEDB LOGIN ENCRYPTED PASSWORD 'password1'

Except, of course, you'll want to choose a stronger password.

If you'll be connectiong to PostgreSQL through Apache, you'll need to create a role for the apache user. There's no need for Apache to create databases, so you can leave off that option.

CREATE ROLE apache WITH LOGIN ENCRYPTED PASSWORD 'letmein'

The PostgreSQL documentation gives all the details on creating roles.

That should be enough to get PostgreSQL up and running. You can no exit the postgres user and try connecting from your own account.

postgresql setup 1: create a database cluster

There are a lot of things I like about PostgreSQL: It does a great job conforming to the SQL 92 and SQL 99 standards, while at the same time it supports more than a dozen procedural languages. It's robust and scalable. It grows with your needs. It's well documented.

But there's one thing I don't like: It's a pain to set up. Well, maybe pain is an overstatement. But PostgreSQL does not just work out of the box; it takes a little effort to get it set up.

I recently set up PostgreSQL on a new machine running Fedora, and it took a little research to find all the necessary steps. I'm going to try to pull everything together here.

Part 1: Create a database cluster

A database cluster is the collection of databases to be managed by a database server. The initdb command sets up a cluster with two default databases you'll need to have: postgres, which is used by many third party apps, and template1, which will be the template for the databases you create.

The cluster must have a home directory. This can be located anywhere on the machine; popular choices, according to the PostgreSQL documentation notes that /usr/local/pgsql/data and /var/lib/pgsql/data. You may have to create the directory first, then chown it to user postgres.

Next, you'll need to log in as user postgres in order to run initdb. Confession time: I've never gotten sudo to work, so I always su to the user I want to execute the command as, then exit when I'm done. So su me.

So, the postgres user has no password by default, which means it's impossible to su directly to postgres. The only way I've found to execute a command as this user is to su root, and then su postgres. If anyone has a better way, or if you can explain sudoers configuration to me, let me know in the comments.

Anyway, as postgres, execute this command:

initdb -D /usr/local/pgsql/data

The -D option tells initdb what home directory to use.

That's all there is to creating the cluster, but don't exit the postgres user yet.

Up next: authentication and roles