-
Notifications
You must be signed in to change notification settings - Fork 0
/
2-users.sql
29 lines (24 loc) · 999 Bytes
/
2-users.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Create reader group
CREATE ROLE readaccess LOGIN;
GRANT USAGE ON SCHEMA bvb_accounts TO readaccess;
ALTER DEFAULT PRIVILEGES IN SCHEMA bvb_accounts GRANT SELECT ON TABLES TO readaccess;
-- Create Writer Group
CREATE ROLE writeaccess LOGIN;
GRANT USAGE ON SCHEMA bvb_accounts TO writeaccess;
ALTER DEFAULT PRIVILEGES IN SCHEMA bvb_accounts GRANT SELECT, INSERT, UPDATE ON TABLES TO writeaccess;
-- Create Deletion Group
CREATE ROLE deleteaccess LOGIN;
GRANT USAGE ON SCHEMA bvb_accounts TO deleteaccess;
ALTER DEFAULT PRIVILEGES IN SCHEMA bvb_accounts GRANT SELECT, DELETE ON TABLES TO deleteaccess;
-- bvb reader
DROP USER IF EXISTS bvb_reader;
CREATE USER bvb_reader WITH PASSWORD 'reader123';
GRANT readaccess TO bvb_reader;
-- bvb writer
DROP USER IF EXISTS bvb_writer;
CREATE USER bvb_writer WITH PASSWORD 'writer123';
GRANT writeaccess TO bvb_writer;
-- bvb remover
DROP USER IF EXISTS bvb_remover;
CREATE USER bvb_remover WITH PASSWORD 'remover123';
GRANT deleteaccess TO bvb_remover;