Permanently change permissions of /var/run/postgresql

Question

On Linux Mint 17.0 Cinnamon Edition, I want to use /var/run/postgresql as the unix_socket_directories option for all of my postgres databases.

Whenever I run the command pg_ctl -D postgres-data -o '-F -p 33311' start to start my local database, I get the error FATAL: could not create lock file "/var/run/postgresql/.s.PGSQL.33311.lock": Permission denied.

As many answers across AskUbuntu, StackOverflow and various forums suggest, I have to run sudo chmod 777 /var/run/postgresql or sudo chown -R $USER:$USER /var/run/postgresql to change the permissions of the directory so that I can write to it. Then I am able to start my database. However, every time I reboot, the permissions go away; the directory is no longer world-writable and the user postgres-xc reclaims user and group ownership over the directory.

Neither sudo adduser $USER postgres or sudo adduser $USER postgres-xc alleviates the permissions issue.

I tried doing a clean install as described here, but I still have the issue on reboot.

I've tried changing the value of unix_socket_directories to /tmp in ./postgres-data/postgresql.conf (the local database config file), and this allows me to start that particular database without having to update permissions on every reboot. However, I also have a script which uses pg_ctl -D $DIR initdb && pg_ctl -D $DIR start to setup and start databases on the fly. As such, a default postgresql.conf config file is used for these databases, so unix_socket_directories is the unwritable /var/run/postgresql and I get permission denied errors when running that script. Manually/programmatically editing these configs on-the-fly to use unix_socket_directories = '/tmp' is... undesirable.

I know that I could use the -o flag with pg_ctl to override the unix_socket_directories option to /tmp, but this script is shared by other developers who do not use Ubuntu, so I would not necessarily want to restrict everyone to using the /tmp directory, especially if their configurations differed slightly. I would prefer everyone use the default location for their installation. Personally, I would also prefer to keep the default directory for general compatibility with other Ubuntu packages; e.g., pgadmin looks at /var/run/postgresql for a lock file by default.

I have also tried editing /etc/postgresql/9.3/main/pg_ctl.conf in an attempt to automatically pass set these options whenever I use pg_ctl:

# Automatic pg_ctl configuration
# This configuration file contains cluster specific options to be passed to
# pg_ctl(1).

pg_ctl_options = '-o "-c unix_socket_directories=/tmp -c unix_socket_group=jackson -c unix_socket_permissions=0777"'

But that did not have any effect.

Please advise on how I can use /var/run/postgresql as my unix_socket_directories option for all of my postgres databases without having to run sudo chown -R $USER:$USER /var/run/postgresql every time I restart my system. Thanks.

Answer 1

Permissions for /var/run/postgresql are taken from /usr/lib/tmpfiles.d/postgresql.conf

Answer 2

Run the following to reclaim the directory (immediately and on subsequent boots):

service='reclaim-postgresql'
service_file="/etc/init.d/${service}"

echo "#!/bin/sh
set -e

### BEGIN INIT INFO
# Provides:          ${service}
# Required-Start:    \$postgres-xc
# Required-Stop:     \$postgres-xc
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Reclaim /var/run/postgresql
### END INIT INFO

chown -R ${USER}:${USER} /var/run/postgresql" \
    | sudo tee "${service_file}"
sudo chmod +x "${service_file}"
sudo update-rc.d "${service}" defaults
sudo service "${service}" start

Note that when running sudo apt-get upgrade to update postgres, you may need to temporarily sudo chown -R postgres:postgres /var/run/postgresql in order for the upgrade to succeed.

Answer 3

Depending on your init system, add the chown/chmod command in the relevant init script for PostgreSQL such as /etc/init.d/postgresql.

Answer 4

use pg_ctl -D ... -o "... -k /tmp" start to create the socket in /tmp