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] [ [ = ]] 3 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,
and replace it with
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!