create1.sql

-- The owner of all database objects
-- This user can and will change the database schema
CREATE ROLE meetphil WITH NOSUPERUSER NOCREATEDB NOCREATEROLE LOGIN;

-- The group which all regular users will be part of
CREATE ROLE mpusers WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOLOGIN;

-- The user which the web application connects as
-- Has limited rights by itself
CREATE ROLE mpwebui WITH NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN IN ROLE mpusers;

-- The regular people
CREATE ROLE francois WITH LOGIN INHERIT IN ROLE mpusers;
CREATE ROLE rene WITH LOGIN INHERIT IN ROLE mpusers;
CREATE ROLE colette WITH LOGIN INHERIT IN ROLE mpusers;

-- Create the application database itself
CREATE DATABASE meetphil WITH
    owner = meetphil
    template = template0
    encoding = 'UTF-8'
    lc_ctype = 'en_US.UTF-8'
    lc_collate = 'en_US.UTF-8';

-- Grant privileges
GRANT CONNECT, TEMPORARY ON DATABASE meetphil TO mpwebui, mpusers;

\connect meetphil

ALTER DEFAULT PRIVILEGES FOR ROLE meetphil
  GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER
  ON TABLES
  TO mpusers;

ALTER DEFAULT PRIVILEGES FOR ROLE meetphil
  GRANT EXECUTE
  ON FUNCTIONS
  TO mpusers;

ALTER DEFAULT PRIVILEGES FOR ROLE meetphil
  GRANT SELECT, UPDATE, USAGE
  ON SEQUENCES
  TO mpusers;

create2.sql

-- Execute as user meetphil, in database meetphil
-- psql -U meetphil -d meetphil
SET client_min_messages TO warning;

CREATE TABLE parties(
    party_id serial not null primary key
);

CREATE TABLE party_names(
    party_id int not null references parties
  , surname text not null
  , rest_of_name text
  , valid_starting_on date not null default current_date

  , unique(party_id, valid_starting_on, surname, rest_of_name)
  , constraint surname_not_empty check(length(trim(surname)) > 0)
  , constraint surname_is_trimmed check(trim(surname) = surname)
  , constraint rest_of_name_is_trimmed check((rest_of_name is not null and trim(rest_of_name) = rest_of_name) or rest_of_name is null )
);

create3.sql

-- Execute as mpwebui in the meetphil database
-- psql -U mpwebui -d meetphil
SET ROLE TO francois;

-- Results in:
-- ERROR:  permission denied to set role "francois"

INSERT INTO parties(party_id) VALUES (default) RETURNING party_id;

drop.sql

-- Execute as database superuser (postgres), in any database
-- psql -U postgres -d postgres

ALTER DEFAULT PRIVILEGES FOR ROLE meetphil
  REVOKE SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER
  ON TABLES FROM mpusers;

ALTER DEFAULT PRIVILEGES FOR ROLE meetphil
  REVOKE EXECUTE
  ON FUNCTIONS FROM mpusers;

ALTER DEFAULT PRIVILEGES FOR ROLE meetphil
  REVOKE USAGE
  ON SEQUENCES FROM mpusers;

DROP DATABASE meetphil;

DROP USER francois;
DROP USER rene;
DROP USER colette;
DROP USER mpusers;
DROP USER mpwebui;