PostgreSQL Cheat Sheet - Admin

Glossary

hawara

Host where PostgreSQL server runs. It's named after a village in Egypt where the pyramid of Amenemhet III is located.

vosf

My userid.

Terminal login

vosf@hawara:~$ sudo -s
[sudo] password for vosf:
root@hawara:~# su - postgres
postgres@hawara:~$

Psql login + quit

postgres@hawara:~$ psql -d template1
template1=#

...
template1=# \q
postgres@hawara:~$

Datamodel

Figure 1: Main objects

Roles and Users

Users are Roles with login. Grants for Roles to Schemas apply to Users too.

Use createuser command to create a role or user:

postgres@hawara:~$ createuser <role> --no-createrole --no-createdb --no-superuser --no-login
postgres@hawara:~$ createuser <user> --no-createrole --no-createdb --no-superuser --pwprompt

Typically create user <database>_sys as owner of database.

Typically create user <database>_<schema> with grant all on the schema.

List of roles:

template1=# SELECT rolname FROM pg_roles;
template1=# \dg

List of users:

template1=# SELECT usename FROM pg_user;
template1=# \du

Commands dg and du are identical. Views pg_roles and pg_user show the same identities. View pg_roles shows more and different columns.

Grant a role to a user:

template1=# GRANT <role> TO <user>;

Databases

Create database:

postgres@hawara:~$ createdb --encoding UTF8 --owner <user> <database> <description>

List databases:

template1=# \l

Schemas

Disallow the creation of tables in the public schema and create schema:

postgres@hawara:~$ psql -d <database>

database=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;

database=# CREATE SCHEMA <schema>;

Typical names for schemas when database is for specific application:

  • dev

  • test

  • accept

  • prod

Allow user to create objects in schema and make schema default schema for this user:

database=# GRANT ALL ON SCHEMA <schema> TO <user>;
database=# ALTER USER <user> SET search_path to '<schema>';

List schemas:

database=# \dn

Typical

Database = xwiki Schema = dev

Create User that owns database

postgres@hawara:~$ createuser xwiki_sys --no-createrole --no-createdb --no-superuser --pwprompt

Create Role with rights on schema 'dev'

postgres@hawara:~$ createuser xwiki_dev --no-createrole --no-createdb --no-superuser --no-login

Create User user_dev if this user does not yet exist

postgres@hawara:~$ createuser user_dev --no-createrole --no-createdb --no-superuser --pwprompt

Grant role xwiki_dev to user_dev

postgres@hawara:~$ psql -d template1

template1=# GRANT xwiki_dev TO user_dev;

Create Database

postgres@hawara:~$ createdb --encoding UTF8 --owner xwiki_sys xwiki

Make schema Public inaccessible

postgres@hawara:~$ psql -d xwiki

xwiki=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Create Schema 'dev' and make this default schema for user_dev

postgres@hawara:~$ psql -d xwiki

xwiki=# CREATE SCHEMA dev;
xwiki=# GRANT ALL ON SCHEMA dev TO xwiki_dev;
xwiki=# ALTER USER user_dev SET search_path to dev;