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.
lionserver:~ serveradmin Usage: serveradmin [-dhvx] [list | start | stop | status | fullstatus | settings | command] [ [ = ]] -h, --help display this message -v, --version display version info -d, --debug print command -x, --xml print output as XML plist Examples: serveradmin list --Lists all services serveradmin start afp --Starts afp server serveradmin stop ftp --Stops ftp server serveradmin status web --Returns current status of the web server serveradmin fullstatus web --Returns more complete status of the web server serveradmin settings afp --Returns all afp configuration parameters serveradmin settings afp:guestAccess --Returns afp guestAccess attribute serveradmin settings afp:guestAccess = yes --Sets afp guestAccess to true serveradmin settings --Takes settings commands like above from stdin serveradmin command afp:command = getConnectedUsers --Used to perform service specific commands serveradmin command --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.
lionserver:~ serveradmin list serveradmin must be run as root
That's ok, we just need to run this command with a
lionserver:~ sudo serveradmin list Password: accounts addressbook afp bonjour calendar certs config devicemgr dhcp dirserv dns filebrowser info ipfilter jabber mail nat netboot network nfs notification pcast pcastlibrary postgres radius sharing signaler smb swupdate vpn web wiki xgrid 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.
lionserver:~ sudo serveradmin start postgres 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
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
template1=# CREATE ROLE username WITH superuser password 'QEGNRWXvxewJ42LdD'; 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
psql command without fiddling around with the
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
wordpressuser with the command
createuser and then login to the database.
lionserver:~ createdb wordpressdb lionserver:~ createuser Enter name of role to add: wordpressuser 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 lionserver:~ psql -d wordpressdb
Once in PostgreSQL we'll give
wordpressuser permissions on
and assign a randomly generated password.
wordpressdb=# GRANT ALL privileges ON DATABASE wordpressdb TO wordpressuse; wordpressdb=# ALTER ROLE wordpressuser password 'NvUrYgnz4DRfbXm7y'; wordpressdb=# \q
And we’re done!