DEV Community

Cover image for PostgreSQL: Create database with custom owner
Sergio Peris
Sergio Peris

Posted on • Originally published at sertxu.dev

PostgreSQL: Create database with custom owner

#tutorial #postgres

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";
Enter fullscreen mode Exit fullscreen mode

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

CREATE USER "username" WITH PASSWORD 'super-secret-password';
Enter fullscreen mode Exit fullscreen mode

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

ALTER DATABASE "project" OWNER TO "username";
Enter fullscreen mode Exit fullscreen mode

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

GRANT CONNECT, TEMP, CREATE ON DATABASE "project" TO "username";
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)