Postgres Creating new User & Database
Thomas Büttner
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.