Gobierto data database setup

Gobierto data is the Gobierto module that implements an open data portal. It uses a separated database to store datasets and data. The preferred data storage for tabular data is PostgreSQL (>= 12). It offers a public API to query the database with three levels of access:

read only access
write access
draft read access

The following SQL script creates the three kinds of users in a database called gobierto_data (the name of the database and the name of the users could be changed):

read only user is called gobierto_data_user
read/write user is called gobierto_data_user_write
read only user for draft schema is called gobierto_data_user_draft

(notice that the passwords for the users should be replaced)

-- Create write user
\c postgres
CREATE ROLE gobierto_data_user_write ENCRYPTED PASSWORD '{{write_user_password}}' NOCREATEROLE NOCREATEDB NOSUPERUSER LOGIN;
CREATE DATABASE gobierto_data OWNER gobierto_data_user_write;
-- Connect to specific database
\c gobierto_data
-- Create users
CREATE ROLE gobierto_data_user ENCRYPTED PASSWORD '{{read_user_password}}' NOCREATEROLE NOCREATEDB NOSUPERUSER LOGIN;
CREATE ROLE gobierto_data_user_draft ENCRYPTED PASSWORD '{{draft_user_password}}' NOCREATEROLE NOCREATEDB NOSUPERUSER LOGIN;
-- Create schema draft
CREATE SCHEMA IF NOT EXISTS draft AUTHORIZATION gobierto_data_user_write;

-- Revoke all permissions
REVOKE ALL ON schema public FROM public;

-- Grant all on schema public to write user
GRANT ALL ON schema public TO gobierto_data_user_write;

-- Grant usage on schema public to read only users
GRANT USAGE ON SCHEMA public TO gobierto_data_user;
GRANT USAGE ON SCHEMA public TO gobierto_data_user_draft;

-- Revoke all permissions to all users
REVOKE ALL ON schema draft FROM public;

-- Grant all permissions to write user
GRANT ALL ON schema draft TO gobierto_data_user_write;

-- Disable statement_timeout on write user
ALTER ROLE gobierto_data_user_write SET statement_timeout=3000;

-- Grant usage permissions on schema draft
GRANT USAGE ON SCHEMA draft TO gobierto_data_user_draft;

-- Grant SELECT permissions on existing databases and tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO gobierto_data_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO gobierto_data_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO gobierto_data_user_draft;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO gobierto_data_user_draft;
GRANT SELECT ON ALL TABLES IN SCHEMA draft TO gobierto_data_user_draft;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA draft TO gobierto_data_user_draft;

-- Create function to be executed in a trigger CREATE TABLE
CREATE OR REPLACE FUNCTION grant_permission_to_gobierto_data_user()
RETURNS event_trigger AS $$
BEGIN
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO gobierto_data_user;
  GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO gobierto_data_user;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO gobierto_data_user_draft;
  GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO gobierto_data_user_draft;
  GRANT SELECT ON ALL TABLES IN SCHEMA draft TO gobierto_data_user_draft;
  GRANT SELECT ON ALL SEQUENCES IN SCHEMA draft TO gobierto_data_user_draft;
END
$$
LANGUAGE plpgsql;

CREATE EVENT TRIGGER
on_create_table ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE grant_permission_to_gobierto_data_user();