/* Code-Comments */

Getting Started With Postgres on OS X with Homebrew

November 24, 2019

Before you can connect to a Postgres database from the command line, it needs to be running.

If it’s been a while, it’s easy to forget how to do that.

When installing via Homebrew, there’s a useful reminder:

postgresql
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start

If this is the first time you’re starting Postgres, the next things you may want to do are:

  1. Create a database to connect to
  2. Create a user other than your superuser to handle routine management.1
  3. Adjust roles for the new user

Below is a quick tutorial on how to do all of those:

Create The Database

Once the postgres service is started, the first thing we’ll need is a database. We can create the database using createdb from bash:2

$ createdb <database_name>

After we have our first database, we can login and create databases from within the postgres shell, however, the first one needs to happen from the bash shell - or you’ll get the error:

FATAL: database does not exist psql: FATAL: database does not exist

Create The User

Now that we have a database, we will want to create the second (non super) user. For that, we have the createuser command from the terminal.2 Or, if we log into the database (using the name of the database we just created) we can create the user within the psql shell (which you’ll know you’re in because instead of the prompt beginning with $ for bash or # for zsh, you’ll see <database_name>=#).

Creating User In The Bash Shell

$ createuser <username> --pwprompt

The --pwprompt is to secure the new user with a password.

Creating A User Within The PSQL Shell

<database_name>=# create user <username> [with password '<password>']

Roles And Permissions

At this point, you should be able to see your new role using the \du command within the psql shell: Roles & Permissions

The thing to notice is that this does not apply a role to the user - which will limit the ability for them to do much within the database once they login.

Alter The Role

Postgres recommends having a user who can create databases and roles but is not a super user.

To do that, we’ll want to provide the attributes of createdb and createrole to our new role without making them a superuser.

From within the Postgres shell, we can use the ALTER ROLE command:4

<database_name>=# alter role onething_admin CREATEDB;
<database_name>=# alter role onething_admin createrole;

Alter role

Wrap Up

Confirm that your new user is created as expected by logging into them.

Exit the Postgres shell and try logging in as your new user.

If the command prompt is <database_name>=> - terminating with an > instead of the #, then you know you’re not signed in as a super user.

Regular user prompt

Footnotes


Stephen Weiss

Thanks for reading! My name's Stephen Weiss. I live in Chicago with my wife, Kate, and dog, Finn.
Click here to see the archives of my weeks in review and sign up yourself!