Programming/PostgreSQL/Setup

Installation

Arch Linux

Basic installation can be performed with:

sudo pacman -Syu postgresql
sudo -u postgres initdb --locale=en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data
sudo systemctl start postgresql.service
sudo systemctl enable postgresql.service

Ubuntu

Basic installation can be performed with:

sudo apt install postgresql postgresql-contrib

User Management

Roles in PostreSQL are the equivalent of MySQL's Users.

However, documentation online (and even PostgreSQL's shell commands) seem inconsistent regarding which name it uses.

Due to habit, the rest of this wiki will probably refer to roles as users.

List All Users

From the PostgreSQL shell:

\du

Create User

Via Linux Shell

First, invoke a system user that has PostgreSQL role privileges.

Create a new user via an interactive GUI:

createuser --interactive

Alternatively, create a minimal user and enable permissions with flags via:

createuser <flags> <user_name>

Via PostgreSQL Shell

First, invoke a PostgreSQL shell that has PostgreSQL role privileges:

CREATE USER <user_name> WITH ENCRYPTED PASSWORD '<password>';

Remove User

From the PostgreSQL shell:

DROP OWNED BY <user_name>;
DROP USER <user_name>;

Change User Password

From the PostgreSQL shell:

ALTER USER <user_name> WITH ENCRYPTED PASSWORD '<password>';

Change User Permissions

From the PostgreSQL shell:

ALTER ROLE <user_name> WITH <options>;

For all current available user permissions, see https://www.postgresql.org/docs/current/sql-createrole.html