-- Generated from microservices.pdf at https://momjian.us/presentations
-- This is intended to be run by psql so backslash commands are processed.

-- setup
\pset footer off
\pset null (null)



-- create role
CREATE ROLE service;

-- create schema
CREATE SCHEMA service;

ALTER SCHEMA service OWNER TO service;

-- become 'service' user
SET SESSION AUTHORIZATION service;

-- create table
CREATE TABLE service.employee (
        emp_id SERIAL PRIMARY KEY,
        name TEXT UNIQUE NOT NULL,
        salary numeric(10,2)
);

-- all services have full access
-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN IN ROLE service;

CREATE ROLE service2 WITH LOGIN IN ROLE service;

-- create per-service schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

-- become 'service1' user
SET SESSION AUTHORIZATION service1;

-- create table
CREATE TABLE service1.employee (
        emp_id SERIAL PRIMARY KEY,
        name TEXT UNIQUE NOT NULL,
        salary numeric(10,2)
);

-- give all services full access
GRANT ALL ON SCHEMA service1 TO service;

GRANT ALL ON service1.employee TO service;

-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN IN ROLE service;

CREATE ROLE service2 WITH LOGIN IN ROLE service;

-- create per-service schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

-- become 'service1' user
SET SESSION AUTHORIZATION service1;

-- create table
CREATE TABLE service1.employee (
        emp_id SERIAL PRIMARY KEY,
        name TEXT UNIQUE NOT NULL,
        salary numeric(10,2)
);

-- give other services read-only access
GRANT USAGE ON SCHEMA service1 TO service;

GRANT SELECT ON service1.employee TO service;

-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN IN ROLE service;

CREATE ROLE service2 WITH LOGIN IN ROLE service;

-- create per-service schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

-- become 'service1' user
SET SESSION AUTHORIZATION service1;

-- create table
CREATE TABLE service1.employee_v1 (
        emp_id SERIAL PRIMARY KEY,
        name TEXT UNIQUE NOT NULL,
        salary numeric(10,2)
);

-- give other services read-only access
GRANT USAGE ON SCHEMA service1 TO service;

GRANT SELECT ON service1.employee_v1 TO service;

-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN IN ROLE service;

CREATE ROLE service2 WITH LOGIN IN ROLE service;

-- create per-service schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

-- become 'service1' user
SET SESSION AUTHORIZATION service1;

-- create table
CREATE TABLE service1.employee (
        emp_id SERIAL PRIMARY KEY,
        name TEXT UNIQUE NOT NULL,
        salary numeric(10,2)
);

-- create view
CREATE VIEW service1.employee_v1 AS
        SELECT emp_id, name FROM service1.employee;

-- give other services read-only access to two columns
GRANT USAGE ON SCHEMA service1 TO service;

GRANT SELECT ON service1.employee_v1 TO service;

-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN IN ROLE service;

CREATE ROLE service2 WITH LOGIN IN ROLE service;

-- create private schema
CREATE SCHEMA service1_private;

ALTER SCHEMA service1_private OWNER TO service1;

-- create public schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

-- become 'service1' user
SET SESSION AUTHORIZATION service1;

-- create private table
CREATE TABLE service1_private.employee (
        emp_id SERIAL PRIMARY KEY,
        name TEXT UNIQUE NOT NULL,
        salary numeric(10,2)
);

-- create view
CREATE VIEW service1.employee_v1 AS
        SELECT emp_id, name FROM service1_private.employee;

-- give other services read-only access to two columns
GRANT USAGE ON SCHEMA service1 TO service;

GRANT SELECT ON service1.employee_v1 TO service;

-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN IN ROLE service;

CREATE ROLE service2 WITH LOGIN IN ROLE service;

-- create private schema
CREATE SCHEMA service1_private;

ALTER SCHEMA service1_private OWNER TO service1;

-- create public schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

-- become 'service1' user
SET SESSION AUTHORIZATION service1;

-- create private table
CREATE TABLE service1_private.employee (
        emp_id SERIAL PRIMARY KEY,
        name TEXT UNIQUE NOT NULL,
        salary numeric(10,2)
);

-- create view
CREATE VIEW service1.employee_v1 AS
        SELECT emp_id, name FROM service1_private.employee;

-- give other services read-only access to two columns
GRANT USAGE ON SCHEMA service1 TO service;

GRANT SELECT ON service1.employee_v1 TO service;

-- create setter function for salary
-- use a transaction block so PUBLIC permission is never 
BEGIN WORK;

CREATE FUNCTION service1.set_employee_salary_v1
        (emp_id service1_private.employee.emp_id%TYPE,
         new_salary service1_private.employee.salary%TYPE)
        RETURNS VOID
AS $$
        UPDATE service1_private.employee
        SET salary = new_salary
        WHERE service1_private.employee.emp_id = emp_id
$$
LANGUAGE sql SECURITY DEFINER;


GRANT EXECUTE ON FUNCTION service1.set_employee_salary_v1 TO 
service;

REVOKE EXECUTE ON FUNCTION service1.set_employee_salary_v1 FROM 
PUBLIC;

COMMIT WORK;

-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN IN ROLE service;

CREATE ROLE service2 WITH LOGIN IN ROLE service;

-- create private schema
CREATE SCHEMA service1_private;

ALTER SCHEMA service1_private OWNER TO service1;

-- create public schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

-- become 'service1' user
SET SESSION AUTHORIZATION service1;

-- create private table
CREATE TABLE service1_private.employee (
        emp_id SERIAL PRIMARY KEY,
        name TEXT UNIQUE NOT NULL,
        salary numeric(10,2)
);

-- create view
CREATE VIEW service1.employee_v1 AS
        SELECT emp_id, name FROM service1_private.employee;

-- give other services read-only access to two columns
GRANT USAGE ON SCHEMA service1 TO service;

GRANT SELECT ON service1.employee_v1 TO service;

-- create setter function for salary
-- use a transaction block so PUBLIC permission is never 
BEGIN WORK;

CREATE FUNCTION service1.set_employee_salary_v1
        (emp_id service1_private.employee.emp_id%TYPE,
         new_salary service1_private.employee.salary%TYPE)
        RETURNS VOID
AS $$
        UPDATE service1_private.employee
        SET salary = new_salary
        WHERE service1_private.employee.emp_id = emp_id
$$
LANGUAGE sql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION service1.set_employee_salary_v1 TO 
service;

REVOKE EXECUTE ON FUNCTION service1.set_employee_salary_v1 FROM 
PUBLIC;

COMMIT WORK;

-- connect to service2 database server
\connect - - - 5433
-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN IN ROLE service;

CREATE ROLE service2 WITH LOGIN IN ROLE service;

-- create private schema
CREATE SCHEMA service1_private;

ALTER SCHEMA service1_private OWNER TO service1;

-- create public schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

CREATE SERVER postgres_fdw_service1
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port '5432', dbname 'test');

CREATE USER MAPPING FOR PUBLIC
SERVER postgres_fdw_service1
OPTIONS (user 'service1', password '');

IMPORT FOREIGN SCHEMA service1_private LIMIT TO (employee)
FROM SERVER postgres_fdw_service1 INTO service1_private;

-- create view
CREATE VIEW service1.employee_v1 AS
        SELECT emp_id, name FROM service1_private.employee;

-- give other services read-only access to two columns
GRANT USAGE ON SCHEMA service1 TO service;

GRANT SELECT ON service1.employee_v1 TO service;

-- create setter function for salary
-- use a transaction block so PUBLIC permission is never 
BEGIN WORK;

CREATE FUNCTION service1.set_employee_salary_v1
        (emp_id service1_private.employee.emp_id%TYPE,
         new_salary service1_private.employee.salary%TYPE)
        RETURNS VOID
AS $$
        UPDATE service1_private.employee
        SET salary = new_salary
        WHERE service1_private.employee.emp_id = emp_id
$$
LANGUAGE sql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION service1.set_employee_salary_v1 TO 
service;

REVOKE EXECUTE ON FUNCTION service1.set_employee_salary_v1 FROM 
PUBLIC;

COMMIT WORK;

-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN IN ROLE service;

CREATE ROLE service2 WITH LOGIN IN ROLE service;

-- create private schema
CREATE SCHEMA service1_private;

ALTER SCHEMA service1_private OWNER TO service1;

-- create public schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

-- become 'service1' user
SET SESSION AUTHORIZATION service1;

-- create private table
CREATE TABLE service1_private.employee (
        emp_id SERIAL PRIMARY KEY,
        name TEXT UNIQUE NOT NULL,
        salary numeric(10,2)
);

-- create view
CREATE VIEW service1.employee_v1 AS
        SELECT emp_id, name FROM service1_private.employee;

-- give other services read-only access to two columns
GRANT USAGE ON SCHEMA service1 TO service;

GRANT SELECT ON service1.employee_v1 TO service;

-- connect to service2 database server
\connect - - - 5433
-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN IN ROLE service;

CREATE ROLE service2 WITH LOGIN IN ROLE service;

-- create private schema
CREATE SCHEMA service1_private;

ALTER SCHEMA service1_private OWNER TO service1;

-- create public schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

CREATE SERVER postgres_fdw_service1
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port '5432', dbname 'test');

CREATE USER MAPPING FOR PUBLIC
SERVER postgres_fdw_service1
OPTIONS (user 'service1', password '');

IMPORT FOREIGN SCHEMA service1_private LIMIT TO (employee)
FROM SERVER postgres_fdw_service1 INTO service1_private;

-- create materialized view
CREATE MATERIALIZED VIEW service1.employee_v1 AS
        SELECT emp_id, name FROM service1_private.employee;

-- give other services read-only access to two columns
GRANT USAGE ON SCHEMA service1 TO service;

GRANT SELECT ON service1.employee_v1 TO service;

-- needed for refresh
CREATE UNIQUE INDEX i_employee_v1 ON service1.employee_v1 
(emp_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY service1.employee_v1;

-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN REPLICATION IN ROLE service;

CREATE ROLE service2 WITH LOGIN REPLICATION IN ROLE service;

-- create private schema
CREATE SCHEMA service1_private;

ALTER SCHEMA service1_private OWNER TO service1;

-- create public schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

-- become 'service1' user
SET SESSION AUTHORIZATION service1;

-- create private employee table
CREATE TABLE service1_private.employee (
        emp_id SERIAL PRIMARY KEY,
        name TEXT UNIQUE NOT NULL,
        salary numeric(10,2)
);

-- create read-only queue table
CREATE TABLE service1_private.queue (
        emp_id INTEGER,
        new_salary numeric(10,2),
        entry_date TIMESTAMP WITH TIME ZONE
);

-- create view
CREATE VIEW service1.employee_v1 AS
        SELECT emp_id, name FROM service1_private.employee;

-- give other services read-only access to two columns
GRANT USAGE ON SCHEMA service1 TO service;

GRANT SELECT ON service1.employee_v1 TO service;

-- create setter function for salary
-- use a transaction block so PUBLIC permission is never 
BEGIN WORK;

CREATE FUNCTION service1.set_employee_salary_v1
        (emp_id service1_private.employee.emp_id%TYPE,
         new_salary service1_private.employee.salary%TYPE)
        RETURNS VOID
AS $$
        INSERT INTO service1_private.queue VALUES (emp_id, 
new_salary, CURRENT_TIMESTAMP);
$$
LANGUAGE sql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION service1.set_employee_salary_v1 TO 
service;

REVOKE EXECUTE ON FUNCTION service1.set_employee_salary_v1 FROM 
PUBLIC;

COMMIT WORK;

CREATE FUNCTION service1_private.queue_trigger_func()
        RETURNS TRIGGER
AS $$
        BEGIN
        UPDATE service1_private.employee
        SET salary = new.new_salary
        WHERE service1_private.employee.emp_id = new.emp_id;
        RETURN new;
        END
$$
LANGUAGE plpgsql;

CREATE TRIGGER queue_trigger
BEFORE INSERT ON service1_private.queue
FOR EACH ROW
EXECUTE FUNCTION service1_private.queue_trigger_func();

-- force trigger to run on replica
ALTER TABLE service1_private.queue ENABLE ALWAYS TRIGGER 
queue_trigger;

-- become superuser
RESET SESSION AUTHORIZATION;

-- create publication
CREATE PUBLICATION pub_employee FOR TABLE 
service1_private.employee;

-- connect to service2 database server
\connect - - - 5433
-- create roles
CREATE ROLE service WITH NOLOGIN;

CREATE ROLE service1 WITH LOGIN REPLICATION IN ROLE service;

CREATE ROLE service2 WITH LOGIN REPLICATION IN ROLE service;

-- create private schema
CREATE SCHEMA service1_private;

ALTER SCHEMA service1_private OWNER TO service1;

-- create public schema
CREATE SCHEMA service1;

ALTER SCHEMA service1 OWNER TO service1;

-- become 'service1' user
SET SESSION AUTHORIZATION service1;

-- create read-only employee table
CREATE TABLE service1_private.employee (
        emp_id INTEGER PRIMARY KEY,
        name TEXT UNIQUE NOT NULL,
        salary numeric(10,2)
);

-- create write-only queue table
CREATE TABLE service1_private.queue (
        emp_id INTEGER,
        new_salary numeric(10,2),
        entry_date TIMESTAMP WITH TIME ZONE
);

-- create view
CREATE VIEW service1.employee_v1 AS
        SELECT emp_id, name FROM service1_private.employee;

-- give other services read-only access to two columns
GRANT USAGE ON SCHEMA service1 TO service;

GRANT SELECT ON service1.employee_v1 TO service;

-- create setter function for salary
-- use a transaction block so PUBLIC permission is never 
BEGIN WORK;

CREATE FUNCTION service1.set_employee_salary_v1
        (emp_id service1_private.employee.emp_id%TYPE,
         new_salary service1_private.employee.salary%TYPE)
        RETURNS VOID
AS $$
        INSERT INTO service1_private.queue VALUES (emp_id, 
new_salary, CURRENT_TIMESTAMP);
$$
LANGUAGE sql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION service1.set_employee_salary_v1 TO 
service;

REVOKE EXECUTE ON FUNCTION service1.set_employee_salary_v1 FROM 
PUBLIC;

COMMIT WORK;

-- become superuser
RESET SESSION AUTHORIZATION;

-- create publication
CREATE PUBLICATION pub_queue FOR TABLE service1_private.queue;

-- create subscription
CREATE SUBSCRIPTION sub_service1_private_employee
        CONNECTION 'dbname=test host=localhost port=5432 
user=service1'
        PUBLICATION pub_employee;

-- connect to service1 database server
\connect - - - 5432
-- create subscription
CREATE SUBSCRIPTION sub_service1_private_queue
        CONNECTION 'dbname=test host=localhost port=5433 
user=service1'
        PUBLICATION pub_queue;

INSERT INTO service1_private.employee
        VALUES (DEFAULT, 'Sam', 100);

SELECT * FROM service1_private.employee;

SELECT service1.set_employee_salary_v1(1, 200);

-- the queue
SELECT * FROM service1_private.queue;

SELECT * FROM service1_private.employee;

-- connect to service2
\connect - - - 5433
SELECT * FROM service1_private.employee;

-- using public API
SELECT * FROM service1.employee_v1;

SELECT service1.set_employee_salary_v1(1, 300);

SELECT * FROM service1_private.queue;

SELECT * FROM service1_private.employee;

