Gobierto data database setup
How to configure gobierto data module database
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();
Updated over 3 years ago