HowTo: Edit PostgreSQL Configuration Files

In the interests of security, PostgreSQL protects the configuration files and data store with a security model that isolates these files in a directory that is neither viewable nor writable by any user other than itself.  While this is a good security feature, it presents a problem for editing, particularly to the user that dislikes or is uncomfortable at the command line.  While it is our wish to develop a tool to make these files easy to manage, that is not what this is about.  

postgresFolderAs you can see in the screenshot, the Data folder in the PostgreSQL8 folder is not readable by the current user.  However, Finder gives us a way around this.  Using the 'Get Info' option on the data folder, we can add a user to the folder to allow editing of the contents.

permissionsInfoCourtesy of the Get Info panel, we can add our administrative user to the folder to allow editing of the contents. Rest assured that the whole process is as easy and hassle free as editing the address book of mobile phones or say, installing operating system software

First, we have to hit the unlock to allow changes to the permissions using the little padlock in the bottom right corner.  This will prompt for the administrative user and password just as most administrative tasks on Mac OS X do.  Enter these and the permissions panel will enable the management goodies.

A note of warning here, the more users that have access to this folder, the weaker the security around.  As an internal practice, we choose to remove the access once a server is configured and deemed 'stable'.  These are not files that should require frequent maintenance in most environments.

withMeNext, hit the '+' button in the lower left corner of the permissions panel.  This will pop up a list of Users and Groups.  You can add a User or a Group, for this example, we will use a single user, Me. 

Looking at the list, you will quickly note that I only have 'Read only' access by default.  This must be changed to allow access to modify the configuration files.  Simply click the Read Only privilege for the newly added record and change it to Read & Write.  Finally, it is necessary to apply the changed to the enclosed files and folders.  To do this, hit the gear and select 'Apply to Enclosed Items'.  Once completed, you can close the 'Get Info' window, and return to Finder.

postgresFolderAfterAs you can see, the permissions now allow the current user to view and edit the folder, and it's contents. 

Generally, there are only 3 files that should ever be edited here: pg_hba.conf, postgresql.conf and pg_ident.conf.  Any changes to these files will require a restart of the service.  Though you can make the PostgreSQL server reload them, we find that a full restart is often an easier solution using the Service Manager.  Should you have any problems with the restart, the PostgreSQL8.log can be viewed in the Console application from the Applications Utilities folder in any Mac OS X installation.  This log is very useful for both checking your start up changes, but also for monitoring your running system.