Programming/PostgreSQL/Setup

Installation

Windows

Download and install from: https://www.postgresql.org/download/windows/

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

Note: Unless otherwise specified, all commands from this section assume you're in the PostgreSQL shell.

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

\du

Create User

Via PostgreSQL Shell

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

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

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 user and enable permissions with flags via:

createuser <flags> <user_name>

See https://www.postgresql.org/docs/current/app-createuser.html for possible flags.

Remove User

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

Change User Password

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

Change User Permissions

ALTER ROLE <user_name> WITH <options>;

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