This page in other versions: 9.1 / 9.2 / 9.3 / 9.4 / current (9.5)  |  Development versions: devel  |  Unsupported versions: 8.2 / 8.3 / 8.4 / 9.0

17.1. The PostgreSQL User Account

As with any other server daemon that is accessible to the outside world, it is advisable to run PostgreSQL under a separate user account. This user account should only own the data that is managed by the server, and should not be shared with other daemons. (For example, using the user nobody is a bad idea.) It is not advisable to install executables owned by this user because compromised systems could then modify their own binaries.

To add a Unix user account to your system, look for a command useradd or adduser. The user name postgres is often used, and is assumed throughout this book, but you can use another name if you like.


Feb. 23, 2008, 11:10 p.m.

[This comment is a rewrite of my comment for PostgreSQL 8.1. The latest version of Mac OS X, Leopard, saw the removal of the NetInfo subsystem, and all the following instructions have been changed accordingly.]

Mac OS X 10.5 does not have the useradd or adduser commands, since user management is handled by Directory Services. You can instead create a user account with the dscl command from the Terminal application. You will need sudo or root access to create the user account.

Although the manual does not mention groups, it is a good idea to give the user account its own group as well. This prevents any files in the database cluster with group write-access from being modified by other users.

To create the user account and group from the Terminal application, first find an unused group ID and an unused user ID. To see the IDs that are currently in use, type

$ sudo dscl . -list /Groups PrimaryGroupID
$ sudo dscl . -list /Users UniqueID

or if it helps you to see just a sorted list of IDs, type

$ sudo dscl . -list /Groups PrimaryGroupID | cut -c 32-34 | sort
$ sudo dscl . -list /Users UniqueID | cut -c 20-22 | sort

Assume that group ID 50 and user ID 100 are not in use. First create the group _postgres by typing

$ sudo dscl . -create /Groups/_postgres
$ sudo dscl . -create /Groups/_postgres PrimaryGroupID 50
$ sudo dscl . -append /Groups/_postgres RecordName postgres

(Leopard precedes daemon names with an underscore. The last command created an alias without the underscore, though, so that you can forget the underscore exists.)

Then create the user account _postgres by typing

$ sudo dscl . -create /Users/_postgres
$ sudo dscl . -create /Users/_postgres UniqueID 100
$ sudo dscl . -create /Users/_postgres PrimaryGroupID 50
$ sudo dscl . -create /Users/_postgres UserShell /bin/bash
$ sudo dscl . -create /Users/_postgres RealName "PostgreSQL Server"
$ sudo dscl . -create /Users/_postgres NFSHomeDirectory /usr/local/pgsql
$ sudo dscl . -append /Users/_postgres RecordName postgres

The user account is now created. It is not given a password intentionally. This prevents anyone but root from logging in as postgres. To use the postgres user account, type

$ sudo su - postgres

When the database cluster is initialised, you want the cluster to not only be owned by the postgres user, but also by the postgres group. Replace the chown line in Section 17.2 with

root# chown postgres.postgres /usr/local/pgsql/data

April 25, 2008, 1:27 p.m.

You can check the user variables with this command:
$ sudo dscl . -read /Users/_postgres

May 28, 2008, 2:21 p.m.

Using MacPorts, do a

$ sudo dscl . -create /Users/_postgres NFSHomeDirectory /opt/local/var/db/postgresql83/

Oct. 8, 2008, 8:02 p.m.

Installing 8.3.1 on OS10.5.5 from the binary .dmg distribution created a 'postgres' account with the user variable UserShell set to "/usr/bin/false", which seems to prevent su'ing to that account.

Updated the property using:
$ sudo dscl . -change /Users/postgres UserShell /usr/bin/false /bin/bash

This now allows su access.

June 6, 2009, 8:30 p.m.

You want to be careful when making changes (like changing or adding a password) to the default "postgres" account because if you do, it may prevent your operating system from launching the PostgreSQL server daemon on startup.

What you should do instead is make database accounts -inside- the pgSQL database when su'd into the default system account.

Privacy Policy | About PostgreSQL
Copyright © 1996-2016 The PostgreSQL Global Development Group