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 create the database.

CREATE DATABASE "project";

Next, we create the user that will own the database we've just created.

CREATE USER "username" WITH PASSWORD 'super-secret-password';

Now, we should change the database owner to the custom user.

ALTER DATABASE "project" OWNER TO "username";

And we're going to explicitly set the permission for this user.

GRANT CONNECT, TEMP, CREATE ON DATABASE "project" TO "username";

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

REVOKE CONNECT, CREATE, TEMP ON DATABASE "project" FROM PUBLIC;

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

postgres=# \l

                                                    List of databases

    Name    |   Owner    | ... |     Access privileges

------------+------------+-----+-----------------------------------

 project    | username   | ... | username=CTc/project

Now you have a new database on your Postgres server ready to use for your next project!