-- Reader

CREATE ROLE reader
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
COMMENT ON ROLE reader IS 'Read-only access to the database to do data analysis';

GRANT USAGE ON SCHEMA public TO reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO reader;

GRANT USAGE ON SCHEMA gis TO reader;
GRANT SELECT ON ALL TABLES IN SCHEMA gis TO reader;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA gis TO reader;
GRANT SELECT ON ALL TABLES IN SCHEMA publish TO reader;


CREATE ROLE a_reader LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION PASSWORD 'airbnb5432';
GRANT reader TO a_reader;
COMMENT ON ROLE a_reader IS 'For data analytics use';

-- Editor
CREATE ROLE editor
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
COMMENT ON ROLE editor IS 'DML use to read and write data';

GRANT USAGE ON SCHEMA public TO editor;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO editor;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO editor;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO editor;

GRANT USAGE ON SCHEMA gis TO editor;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA gis TO editor;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA gis TO editor;

CREATE ROLE an_editor LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION PASSWORD 'airbnb5432';
GRANT editor TO an_editor;
COMMENT ON ROLE an_editor IS 'For data collection and application';
