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

Basics

To access the default PostgreSQL user, use:

sudo -i -u postgres

This is required for some commands, such as the createuser linux terminal command.

The PostgreSQL Shell

Note: The sudo -u postgres command can be used to invoke any postgresql shell command you want, without having to enter the PostgreSQL shell.

To access the PostgreSQL shell for the first time, invoke the postgres user:

sudo -u postgres psql

Exit the shell with:

\q

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