Mac OS X Server Tips: Configuring the PostgreSQL daemon to listen to the network

The following configuration sequence applies to Mac OS X Server 10.7 Lion Server configurations, using the installed PostgreSQL components.

This sequence will configure the PostgreSQL daemon to listen to the network from the local host, and start the database daemon.

You are about to make changes to your configuration, and you will want to have a path back in the event of an error or a corruption. HoffmanLabs recommends performing a full backup of your Mac OS X Server disk before proceeding.

Check your environment

First, determine if PostgreSQL is running, and if networking is enabled. Launch Terminal.app and enter the following commands:

$ sudo serveradmin list | grep -i postgres
$ sudo lsof -i -P | grep -i ':5432'

You will have to enter your administrative password for the sudo command. You may see that the PostgreSQL daemon has already been started, but you should see no information displayed from the lsof command.

Reconfigure the PostgreSQL Startup to allow Network Access

Create a backup copy of your PostgreSQL launchd configuration plist file:

$ cd  /system/library/LaunchDaemons/
$ sudo cp org.postgresql.postgres.plist org.postgresql.postgres.plist_save

You'll use this saved copy in a moment, so don't skip this step.

Now configure the launchd settings for PostgreSQL to allow the daemon to listen to connection requests from the localhost (127.0.0.1) host; from the local server:

$ sudo serveradmin stop postgres
$ sudo chmod o+w org.postgresql.postgres.plist
$ sudo sed 's:<string>listen_addresses=</string>:<string>listen_addresses=127.0.0.1</string>:'  \
<org.postgresql.postgres.plist_save  >org.postgresql.postgres.plist
$ sudo chmod o-w org.postgresql.postgres.plist
$ sudo serveradmin start postgres

If the listen_addresses setting is already configured, the sed command will cause no changes.

The above assumes that the org.postgresql.postgres.plist plist uses text format. If you can cat the file, then the see will work.

Confirm that PostgreSQL is now running (or is running again), and that there is a network connection socket (from the local host) that is now listening to the PostgreSQL TCP port:

$ sudo serveradmin list | grep -i postgres
postgres
$ sudo lsof -i -P | grep -i ':5432'
postgres_ 2019      _postgres    3u  IPv4 0x085e9874      0t0    TCP localhost:5432 (LISTEN)
$

The specific output of the lsof command will vary.

For additional details of the PostgreSQL server known to the launchd tool, issue the following command:

$ sudo serveradmin fullstatus postgres

If you need to enable remote access into the daemon from additional hosts within your network, shut down the daemon, replace the listen_addresses= or listen_addresses=127.0.0.1 line in the plist with listen_addresses=* and finally edit the file /var/pgsql/pg_hba.conf to specify one or more host lines for individual hosts or for subnets, with a host line similar to the following syntax:

host    all         all         10.20.30.0/24 trust

Additional details are included in the comments within the /var/pgsql/pg_hba.conf file.

Users and Groups; Enabling access within Mac OS X Server

The following command should show a user already available:

$ sudo dscl . -read /Users/_postgres
AppleMetaNodeLocation: /Local/Default
GeneratedUID: FFFFEEEE-DDDD-CCCC-BBBB-AAAA000000D8
NFSHomeDirectory: /var/empty
Password: *
PrimaryGroupID: 216
RealName:
 PostgreSQL Server
RecordName: _postgres
RecordType: dsRecTypeStandard:Users
UniqueID: 216
UserShell: /usr/bin/false

Some of the output may vary.

Launch Workgroup Manager, select View from the menus and ensure Show System Records is selected. Now select the user you wish to connect to the database, select groups for that user, and add the PostgreSQL Users user, group 216, shortname _postgres as a group. You can view whether the PostgreSQL User identifier has been granted to the current user with the following command:

$ id | grep -io postgres
postgres
$ 

If you see the string postgres returned, this user has the access.

You can need to add user www (or _www) to the group; to allow Apache to access the database.

If you do not have the user configured as a member of the PostgreSQL Users, you'll usually see an error similar to the following:

$ createdb foo
createdb: could not connect to database postgres: could not connect to server: Permission denied
	Is the server running locally and accepting
	connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
$

Roles; Enabling access within PostgreSQL

Now set up the roles for the PostgreSQL user hl, and allow the user to create a database:

$ sudo -u _postgres psql template1
Password:
psql (9.0.4)
Type "help" for help.

template1-# \h create role
Command:     CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:
    
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp' 
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

template1=# create role hl with login created;
CREATE ROLE
template1-# \q
could not save history to file "/var/empty/.psql_history": Permission denied
$ 

If the above is not in place, you can receive errors similar to the following:

$ createdb foo
createdb: could not connect to database postgres: FATAL:  role "hl" does not exist
$

To view the currently-configured roles for the specified user, use the \du command:

template1-# \du hl

For additional information on psql commands, issue the \? command.

Create your database

You can now invoke the convenience script creatdb to create a database, and other PostgreSQL commands.

createdb foo

This command creates a database named foo.

To delete the foo database:

$ dropdb foo

You should also now be able to use navicat, phpPgAdmin, or other database management tools.

Additional Resources

The PostgreSQL manuals are a reasonable starting point for learning about the database, and the PostgreSQL web site also has information on migrating from MySQL to PostgreSQL.

And here's to hoping that PostgreSQL support finds its way into the Sequel Pro tool.