Using PostgreSQL included with Lion Server

Taming the Beast

Feb 29, 2012 | image credit

In Lion Server Apple has moved from MySQL to PostgreSQL. You may find yourself in a situation as I have where you wish to use a web application that requires a database (WordPress for example), but don’t wish to install MySQL when the PostgreSQL server you have will do fine. There are a number of sources out there on how to get this done, but the information seems to be scattered across different corners of the internet. In particular these two links contain all the information included in this post, but sifting through them was a bit of a pain for me. For this reason I thought I would give my take on how to get this done.

In short we are going to get PostgreSQL up and running, create a super user, create a new database to be used with an application, create a user for that application, and finally give that user access to the database. A quick note, throughout the code in this article you will find entries that are prepended by either lionserver:~ or databasename=#, these are command prompts and are not meant to be typed in, but rather serve to distinguish commands from output.

First up we need to make sure that the PostgreSQL server is running. We’re going to do this with the command serveradmin. serveradmin is a command line tool that allows you to control the various services that Lion Server has to offer. In case you haven’t used this tool before lets get a flavor of how it works.

 1 lionserver:~ serveradmin
 2 Usage: serveradmin [-dhvx] [list | start | stop | status | fullstatus | settings | command] [ [ =  ]]
 4   -h, --help     display this message
 5   -v, --version  display version info
 6   -d, --debug    print command
 7   -x, --xml      print output as XML plist
 8 Examples:
 9 serveradmin list
10         --Lists all services
11 serveradmin start afp
12         --Starts afp server
13 serveradmin stop ftp
14         --Stops ftp server
15 serveradmin status web
16         --Returns current status of the web server
17 serveradmin fullstatus web
18         --Returns more complete status of the web server
19 serveradmin settings afp
20         --Returns all afp configuration parameters
21 serveradmin settings afp:guestAccess
22         --Returns afp guestAccess attribute
23 serveradmin settings afp:guestAccess = yes
24         --Sets afp guestAccess to true
25 serveradmin settings
26         --Takes settings commands like above from stdin
27 serveradmin command afp:command = getConnectedUsers
28         --Used to perform service specific commands
29 serveradmin command
30         --Takes stdin to define generic command that requires other parameters

Ok that’s great now we see that there is a list function lets try that.

1 lionserver:~  serveradmin list
2 serveradmin must be run as root

That’s ok, we just need to run this command with an sudo.

 1 lionserver:~ sudo serveradmin list
 2 Password:
 3 accounts
 4 addressbook
 5 afp
 6 bonjour
 7 calendar
 8 certs
 9 config
10 devicemgr
11 dhcp
12 dirserv
13 dns
14 filebrowser
15 info
16 ipfilter
17 jabber
18 mail
19 nat
20 netboot
21 network
22 nfs
23 notification
24 pcast
25 pcastlibrary
26 postgres
27 radius
28 sharing
29 signaler
30 smb
31 swupdate
32 vpn
33 web
34 wiki
35 xgrid
36 xsan

There it is the service is called postgres. Remember when we took a look how the command worked? The usage info told us that we can start services, let’s try that.

1 lionserver:~ sudo serveradmin start postgres
2 postgres:state = "RUNNING"

And now PostgreSQL is running! Great, now comes the tricky part. Lion Server has a special user that has access to the PostgreSQL server, the user name is \_postgres. But personally I don’t want to have to remember to log in as this other user every time I want to manipulate PostgreSQL. So let’s create a PostgreSQL user for our username. First we need to log into PostgreSQL

1 lionserver:~ sudo -u \_postgres psql template1

Then once in PostgreSQL we are going to create a role with the same username as our login, for the purposes of this tutorial we will use username and a randomly generated password QEGNRWXvxewJ42LdD. Then we will exit out of PostgreSQL with the \q command.

1 template1=# CREATE ROLE username WITH superuser password 'QEGNRWXvxewJ42LdD';
2 template1=# \q

Next we want to add our login username to the PostgreSQL Users Group. The easiest way to do this is to download the Server Admin Tools package. Launch the tool and launch the Workgroups application, in the menu bar click View → Workgroups. You will need to login with an administrator account. Then in the Workgroups application you will click View → Show System Records. This will expand the visible list to show everything. Find your login user, and then click the Groups Tab. Find the PostgreSQL Users group and add it. Now you can execute the psql command without fiddling around with the \_postgres user.

By default, Lion Server will launch the postgres service with the option listen\_addresses="", but our web app needs to connect to the service through an IP address. So fire up your favorite text editor and point it towards /System/Library/LaunchDaemons/org.postgresql.postgres.plist. In this file you want to hunt down the line,

1 listen\_addresses=

and replace it with

1 listen\_addresses=""

Finally we are ready to create our database and application user. Your particular web application may have its own directions on this, if so you should be done with this tutorial. But we will continue to complete a database setup for WordPress.

We’ll create our database with the convenient command createdb, create wordpressuser with the command createuser and then login to the database.

1 lionserver:~ createdb wordpressdb
2 lionserver:~ createuser
3 Enter name of role to add: wordpressuser
4 Shall the new role be a superuser? (y/n) n
5 Shall the new role be allowed to create databases? (y/n) n
6 Shall the new role be allowed to create more new roles? (y/n) n
7 lionserver:~ psql -d wordpressdb

Once in PostgreSQL we’ll give wordpressuser permissions on wordpressdb and assign a randomly generated password.

1 wordpressdb=# GRANT ALL privileges ON DATABASE wordpressdb TO wordpressuse;
2 wordpressdb=# ALTER ROLE wordpressuser password 'NvUrYgnz4DRfbXm7y';
3 wordpressdb=# \q

And we’re done!