Postgres

Basics

Connect to remote psql

Bash

psql -h <REMOTE HOST> -p <REMOTE PORT> -U <DB_USER> <DB_NAME>

Create user and database

Bash

psql -U <user> -W

Bash

create database mydb;

Bash

create user myuser with encrypted password 'mypass';

Bash

grant all privileges on database mydb to myuser;

example with schema:

PostgreSQL Console (Psql)

CREATE ROLE gitea WITH LOGIN PASSWORD 'gitea';
CREATE DATABASE gitea WITH OWNER gitea TEMPLATE template0 ENCODING UTF8 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF8';
CREATE SCHEMA gitea;
GRANT ALL ON SCHEMA gitea TO gitea;
ALTER USER gitea SET search_path=gitea;