example_data.sql

create schema api;
create schema datamart;

create table api.todos (
  id int primary key generated by default as identity,
  done boolean not null default false,
  task text not null,
  due timestamptz
);

insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back');
  
 select * from api.todos;
 
create table datamart.peoples (
  id int primary key generated by default as identity,
  name text not null
);

insert into datamart.peoples (name) values
  ('Alex'), ('Bob');

create role web_anon nologin;

grant usage on schema api to web_anon;

-- grant to specific tables
grant select, insert, update, delete on api.todos to web_anon;

-- grant to all tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA api TO web_anon; 

-- Ensures future tables also have the same privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO web_anon;

grant usage on schema datamart to web_anon;

-- grant to specific tables
grant select, insert, update, delete on datamart.peoples to web_anon;

-- grant to all tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA datamart TO web_anon; 

-- Ensures future tables also have the same privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA datamart GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO web_anon;

NOTIFY pgrst, 'reload schema';