How to access PostgreSQL pg_log folder without being the postgres user and running into permission issues?

Question

On my mac os x development machine, I have postgres 9.1 installed to /Library/PostgreSQL/9.1 and the data directory for PostgreSQL is in /Library/PostgreSQL/9.1/data I have configured logging so I get good logs out of postgres about what sql queries are executing on the server .... etc such that these are written to /Library/PostgreSQL/9.1/data/pg_log I am using the enterprise db pre packaged postgres binary.

The problem is that $PGDATA is owned by the postgres user which is running the postgres processes. When I changed the permissions on the data directory with chmod o+xr data postgres refused to start and tells me that it can not start with the postgres directory having permissions to anything but the postgres user.

FATAL:  data directory "/Library/PostgreSQL/9.1/data" has group or world access
DETAIL:  Permissions should be u=rwx (0700)

Having to sudo in and out of the postgres dir just to get at the log files is annoying.

What is a good way to configure postgres logging on Mac OS X so that it is easy to access the log files from any user that is logged in, rather than having to use sudo or be the postgres user?

UPDATE See my answer below, for the exact recipe I used to make it work.

Answer 1

The simple fact is that PostgreSQL won't let you do this. However, there is a better solution, which is to make sure the following are set in the postgresql.conf:

This one is the default. If it is commented out and not otherwise set, that's ok:

log_destination = 'stderr' 

This needs to be set on:

logging_collector = on  

You can then point this somewhere else:

log_directory = 'pg_log'  

You could point this somewhere you can access it. For example, give postgres appropriate access and point to:

log_directory = '/var/log/postgresql'

Then as long as it isn't in the data directory you can set whatever permissions you want.

Answer 2

Steps I used to make it work.

• Create a directory /var/pg_log and make it world readable and writable chmod o+wxr

• Set the following values in postgresql.conf file

  log_directory = '/var/pg_log'

  log_file_mode = 0506

• restart postgres

• /var/pg_log should have postgres .log files that you can monitor with the mac os x, console utility. no sudo required to see what is going on in there.

Note Postgres refused to allow any permission above 511 on the log file.