Postgres Creating new User & Database

One thing i regularly look up is how to create a new Postgres User & DB. So here are the two ways to create a User and Database.

The CLI Way

Postgres has many helpful Utilities for some common operations.

Like for creating a User (the -P will Prompt for a Password):

1sudo -u postgres createuser -P gitea

And creating a Database with assigned permissions:

1sudo -u postgres createdb --owner gitea gitea_db

You can add --echo to those commands to see the actual SQL that is send to the Database.

There also many other Utilities like for dropping Databases and Users for all binaries you can simply query RPM.

1rpm -q --list postgresql | grep bin

The SQL Way

Creating Users & Databases can also be done via SQL like in MariaDB just different…

To create a User:

1CREATE USER gitea WITH ENCRYPTED PASSWORD 'supersecretpassword';

Create a Database with the new User as Owner:

1CREATE DATABASE gitea OWNER gitea;

Or create and Assign the Permissions in separate steps:

1CREATE DATABASE gitea;
2GRANT ALL PRIVILEGES ON DATABASE gitea TO gitea;

Using SQL allows to easily automate that process for something like SQL-Init scripts used in CoreOS Bootstrap Ignition configuration.

• PostgreSQL