public-schema.sql

-- create roles
CREATE ROLE service NOLOGIN;

CREATE ROLE service1 LOGIN IN ROLE service;
CREATE ROLE service2 LOGIN IN ROLE service;
CREATE ROLE service3 LOGIN IN ROLE service;

-- create private schema
CREATE SCHEMA service1_private AUTHORIZATION service1;

-- create public schema
CREATE SCHEMA service1 AUTHORIZATION service1;

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

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

-- create setter function for salary
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;

-- set view/function permissions
GRANT SELECT ON service1.employee_v1 TO service;
REVOKE EXECUTE ON FUNCTION service1.set_employee_salary_v1 FROM PUBLIC;
GRANT EXECUTE ON FUNCTION service1.set_employee_salary_v1 TO service;

-- set public schema permissions
GRANT USAGE ON SCHEMA service1 TO service;