wM775ZTL

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 and reconect as foo
17
18
19DROP SCHEMA IF EXISTS sch_name CASCADE;
20CREATE SCHEMA sch_name;
21ALTER SCHEMA sch_name OWNER TO foo;
22ALTER ROLE foo SET search_path = sch_name, public;
23ALTER ROLE bar SET search_path = sch_name, public;
24        
25            
26GRANT CONNECT ON DATABASE db_name TO bar;
27
28GRANT USAGE ON SCHEMA sch_name TO bar;
29
30GRANT SELECT         ON ALL TABLES IN SCHEMA sch_name TO bar;
31
32
33ALTER DEFAULT PRIVILEGES IN SCHEMA sch_name GRANT SELECT         ON TABLES TO bar;
34
35--Disconect from postgres and connect to db_name
36        
37CREATE TABLE project(
38    project_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY ,
39    project_name TEXT NOT NULL,
40    project_code TEXT NOT NULL,
41    project_number TEXT NOT NULL
42)TABLESPACE pg_default;