2011-02-13

The 2 Passwords for Postgres On Your Mac

There are 2 passwords involved with Postgres 9 on your Mac:

• The Postgres installer asks for your usual Mac admin account password, to get permission for 2 operations: (1) to install stuff on your computer and (2) to create a special Unix user account named (by default) 'postgres'.

The actual disk files that make up your databases, the files containing your data, are stored in a folder owned by that 'postgres' user rather than your normal Mac user account. The reason is security: If your usual user account is compromised, at least your data files remain behind the wall of that other user.

• The Postgres installer also prompts you to create a password for that 'postgres' user. 

Make it a good password (long, use digits etc. to avoid being simply dictionary words).

When you run the 'pgAdmin' app, it will prompt you for that 'postgres' password to connect to the database server as that 'postgres' user.

--Basil Bourque

The Back Door to Postgres

When you install Postgres 9 on your Mac, the installer automatically creates a user named "postgres" and prompts you to invent a password for that user. The goal is security, so that if your main user account is compromised, your database's contents may remain safely within folders controlled by that other 'postgres' user.

Normally we never need access to the actual files that make up the database. We have command line access via the 'psql' tool. We have GUI admin apps such as the bundled 'pgAdmin', and from countless 3rd-party vendors. We have the 'pgDump' and 'pgRestore' tools to backup and restore the database's contents. Because of all those tools, you may never need to actually pierce the veil to peer at the actual disk files that make up the database.

But just in case you ever do have such a need, or curiosity gets the better of you, you can use the Terminal.app this way:
sudo su postgres
Type that into Terminal, hit return, and use your regular Mac admin account password as prompted. The word 'postgres' is the name of the special user created by the Postgres installer. Be very careful – you will be working with all the "safeties" turned off, enabling you to take actions that are normally forbidden to protect you from yourself.

To leave that session, type:
exit