fKZHaMQW

1DROP DATABASE IF EXISTS db_name;
2DROP ROLE IF EXISTS foo;
3DROP ROLE IF EXISTS bar;
4
5CREATE ROLE foo WITH 
6    SUPERUSER
7    LOGIN
8    PASSWORD 'foo';
9
10CREATE ROLE bar WITH 
11    LOGIN
12    PASSWORD 'bar';
13
14CREATE DATABASE db_name OWNER = foo;
15
16--disconect as postgres from postgres and connect as foo to db_name
17
18DROP SCHEMA IF EXISTS schema_name CASCADE;
19CREATE SCHEMA schema_name;
20ALTER SCHEMA schema_name OWNER TO foo;
21ALTER ROLE foo SET search_path = schema_name, public;
22ALTER ROLE project_worker SET search_path = schema_name, public;
23ALTER ROLE bar SET search_path = schema_name, public;
24
25GRANT CONNECT ON DATABASE db_name TO bar;
26
27GRANT USAGE ON SCHEMA schema_name TO bar;
28
29GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO bar;
30
31
32ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON ALL TABLES TO bar;
33
34CREATE TABLE project(
35    project_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY ,
36    project_name TEXT NOT NULL,
37    project_code TEXT NOT NULL,
38    project_number TEXT NOT NULL
39
40)TABLESPACE pg_default;