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.
