PostgreSQL: Create database with custom owner

When sharing a PostgreSQL server with multiple projects, you might want to create a database and user per project.

First, we must create the user:

CREATE USER "username" password 'secret-password';

Once we've created the user, we must create the database with the new user as owner:

CREATE DATABASE "project" OWNER "username";

Postgres by default allows public access to our database. To revoke it we should run the following query.

REVOKE ALL PRIVILEGES ON DATABASE "project" FROM PUBLIC;

If we list the databases using the \l+ command, the "Access privileges" column should display the following.

postgres=# \l project
                                                    List of databases
    Name    |   Owner    | ... |     Access privileges
------------+------------+-----+-----------------------------------
 project    | username   | ... | username=CTc/project

Once completed this steps, you'll have a new database on your Postgres server ready to use for your next project!