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

Linux ads?

The Linux Foundation has announced the winners of the "We're Linux" ad contest.

Consider me underwhelmed.

Don't get me wrong: I've been using Linux for nine years, it's my primary operating system at home, and I expect to keep using it for the foreseeable future. But the Linux Foundation ads don't impress me.

My first reaction was, Who is the target audience? The winning ad talks about freedom, but doesn't ever explain what that means. As one commenter on Reddit said, it sounds like an ad for a feminine napkin.

The first runner up is creepy. It looks too much like a trailer for an low budget horror film. Again, it doesn't say anything about the product. If you didn't know already, you would probably not guess it had something to do with computers.

The second runner up, in my opinion, is the best of the lot. (Or should that be, the least worst of the lot?) It connects the name Linux with the penguin logo, and shows the penguin operating on a PC. The penguin makes a joke about there being no difference between an apple and a window, suggesting who the competition is.

On the other hand, the operating room scene leaves the impression that Linux is something to try out on an older, dying machine.

So who is the target audience?

The first two ads seem to be targeting people looking for "freedom", however they define that. But let's face it: The real freedom does not come from the operating system kernel. For most users, freedom means being more productive with the applications they have to use. The people who want the freedom of a Unix-like operating system are already using one.

The third ad, on the other hand, seems to be aimed at people who have an older computer that won't run today's software.

If the point is to give users a reason to dump Windows or OS X, none of the ads succeed. If the point is to give Linux name recognition as an operating system, the third ad may be a start. But if the Linux Foundation wants to seriously promote this operating system, they should probably go with a professional ad agency. However, they probably don't the budget for it.

Who does have the budget? Maybe Red Hat, Novell, or some of the other vendors who offer enterprise distributions. But since they are targeting business customers, television ads may not have a good enough return on investment to be worth the expense. There are better ways to reach business customers.

So again, it comes back to target audience. Who, really, is the Linux Foundation trying to reach?