· 4 years ago · May 22, 2021, 11:40 PM
1-- Created by Vertabelo (http://vertabelo.com)
2-- modified by s18827
3-- .pgsql extension needed to initialize proper psql db import
4
5-- DROP DATABASE abdtutorial02;
6-- ROLE
7/*
8FIRST, in order to import this database
9 type the following into the command line:
10
11psql -U postgres
12CREATE DATABASE abdtutorial02;
13\q
14psql -U postgres -d abdtutorial02 < abdtutorial02.pgsq
15*/
16
17-- TASK 1
18
19-- schemas
20CREATE SCHEMA acc;
21CREATE SCHEMA dea;
22CREATE SCHEMA tea;
23
24/*
25USEFUL THING:
26- to make \dt show all relations (tables) from acc, dea and tea;
27[Has to be envoked by user having read permission to all of those schemas]
28[Unfortunately with each \c switch this command has to be called again]
29*/
30SET search_path TO acc, dea, tea;
31
32-- tables
33-- Table: Account
34CREATE TABLE acc.Account (
35 IdAccount int NOT NULL,
36 Number varchar(100) NOT NULL,
37 CreatedAt date NOT NULL,
38 Student_IdStudent int NOT NULL,
39 CONSTRAINT Account_pk PRIMARY KEY (IdAccount)
40);
41
42-- Table: Classes
43CREATE TABLE tea.Classes (
44 IdClasses int NOT NULL,
45 Date date NOT NULL,
46 Time time NOT NULL,
47 IdTeacher int NOT NULL,
48 IdSubject int NOT NULL,
49 CONSTRAINT Classes_pk PRIMARY KEY (IdClasses)
50);
51
52-- Table: Grade
53CREATE TABLE tea.Grade (
54 IdGrade int NOT NULL,
55 Value decimal(5,2) NOT NULL,
56 CreatedAt date NOT NULL,
57 IdClasses int NOT NULL,
58 IdStudent int NOT NULL,
59 CONSTRAINT Grade_pk PRIMARY KEY (IdGrade)
60);
61
62-- Table: Payment
63CREATE TABLE acc.Payment (
64 IdPayment int NOT NULL,
65 Amount decimal(8,2) NOT NULL,
66 CreatedAt date NOT NULL,
67 IdAccount int NOT NULL,
68 CONSTRAINT Payment_pk PRIMARY KEY (IdPayment)
69);
70
71-- Table: Person
72CREATE TABLE dea.Person (
73 IdPerson int NOT NULL,
74 FirstName varchar(100) NOT NULL,
75 LastName varchar(100) NOT NULL,
76 Email varchar(100) NOT NULL,
77 Address varchar(100) NOT NULL,
78 CONSTRAINT Person_pk PRIMARY KEY (IdPerson)
79);
80
81-- Table: Semester
82CREATE TABLE dea.Semester (
83 IdSemester int NOT NULL,
84 Name varchar(100) NOT NULL,
85 CONSTRAINT Semester_pk PRIMARY KEY (IdSemester)
86);
87
88-- Table: SemesterEntry
89CREATE TABLE dea.SemesterEntry (
90 IdSemesterEntry int NOT NULL,
91 IdStudent int NOT NULL,
92 IdStudies int NOT NULL,
93 IdCurrentSemester int NOT NULL,
94 IdStartSemester int NOT NULL,
95 CreatedAt timestamp NOT NULL,
96 CONSTRAINT SemesterEntry_pk PRIMARY KEY (IdSemesterEntry)
97);
98
99-- Table: Student
100CREATE TABLE dea.Student (
101 IdStudent int NOT NULL,
102 IndexNumber varchar(100) NOT NULL,
103 CONSTRAINT Student_pk PRIMARY KEY (IdStudent)
104);
105
106-- Table: Student_Classes
107CREATE TABLE tea.Student_Classes (
108 IdStudent int NOT NULL,
109 IdClasses int NOT NULL,
110 CONSTRAINT Student_Classes_pk PRIMARY KEY (IdClasses,IdStudent)
111);
112
113-- Table: Studies
114CREATE TABLE dea.Studies (
115 IdStudies int NOT NULL,
116 Name varchar(100) NOT NULL,
117 Description varchar(100) NOT NULL,
118 NumberOfSemesters int NOT NULL,
119 CONSTRAINT Studies_pk PRIMARY KEY (IdStudies)
120);
121
122-- Table: Subject
123CREATE TABLE tea.Subject (
124 IdSubject int NOT NULL,
125 Name varchar(100) NOT NULL,
126 IdStudies int NOT NULL,
127 CONSTRAINT Subject_pk PRIMARY KEY (IdSubject)
128);
129
130-- Table: Teacher
131CREATE TABLE dea.Teacher (
132 IdTeacher int NOT NULL,
133 Position varchar(100) NOT NULL,
134 CONSTRAINT Teacher_pk PRIMARY KEY (IdTeacher)
135);
136
137
138-- foreign keys
139-- Reference: Account_Student (table: Account)
140ALTER TABLE acc.Account ADD CONSTRAINT Account_Student
141 FOREIGN KEY (Student_IdStudent)
142 REFERENCES dea.Student (IdStudent)
143 NOT DEFERRABLE
144 INITIALLY IMMEDIATE
145;
146
147-- Reference: Classes_Subject (table: Classes)
148ALTER TABLE tea.Classes ADD CONSTRAINT Classes_Subject
149 FOREIGN KEY (IdSubject)
150 REFERENCES tea.Subject (IdSubject)
151 NOT DEFERRABLE
152 INITIALLY IMMEDIATE
153;
154
155-- Reference: Classes_Teacher (table: Classes)
156ALTER TABLE tea.Classes ADD CONSTRAINT Classes_Teacher
157 FOREIGN KEY (IdTeacher)
158 REFERENCES dea.Teacher (IdTeacher)
159 NOT DEFERRABLE
160 INITIALLY IMMEDIATE
161;
162
163-- Reference: Grade_Classes (table: Grade)
164ALTER TABLE tea.Grade ADD CONSTRAINT Grade_Classes
165 FOREIGN KEY (IdClasses)
166 REFERENCES tea.Classes (IdClasses)
167 NOT DEFERRABLE
168 INITIALLY IMMEDIATE
169;
170
171-- Reference: Grade_Student (table: Grade)
172ALTER TABLE tea.Grade ADD CONSTRAINT Grade_Student
173 FOREIGN KEY (IdStudent)
174 REFERENCES dea.Student (IdStudent)
175 NOT DEFERRABLE
176 INITIALLY IMMEDIATE
177;
178
179-- Reference: Payment_Account (table: Payment)
180ALTER TABLE acc.Payment ADD CONSTRAINT Payment_Account
181 FOREIGN KEY (IdAccount)
182 REFERENCES acc.Account (IdAccount)
183 NOT DEFERRABLE
184 INITIALLY IMMEDIATE
185;
186
187-- Reference: SemesterEntry_Semester1 (table: SemesterEntry)
188ALTER TABLE dea.SemesterEntry ADD CONSTRAINT SemesterEntry_Semester1
189 FOREIGN KEY (IdCurrentSemester)
190 REFERENCES dea.Semester (IdSemester)
191 NOT DEFERRABLE
192 INITIALLY IMMEDIATE
193;
194
195-- Reference: SemesterEntry_Semester2 (table: SemesterEntry)
196ALTER TABLE dea.SemesterEntry ADD CONSTRAINT SemesterEntry_Semester2
197 FOREIGN KEY (IdStartSemester)
198 REFERENCES dea.Semester (IdSemester)
199 NOT DEFERRABLE
200 INITIALLY IMMEDIATE
201;
202
203-- Reference: SemesterEntry_Student (table: SemesterEntry)
204ALTER TABLE dea.SemesterEntry ADD CONSTRAINT SemesterEntry_Student
205 FOREIGN KEY (IdStudent)
206 REFERENCES dea.Student (IdStudent)
207 NOT DEFERRABLE
208 INITIALLY IMMEDIATE
209;
210
211-- Reference: SemesterEntry_Studies (table: SemesterEntry)
212ALTER TABLE dea.SemesterEntry ADD CONSTRAINT SemesterEntry_Studies
213 FOREIGN KEY (IdStudies)
214 REFERENCES dea.Studies (IdStudies)
215 NOT DEFERRABLE
216 INITIALLY IMMEDIATE
217;
218
219-- Reference: Student_Person (table: Student)
220ALTER TABLE dea.Student ADD CONSTRAINT Student_Person
221 FOREIGN KEY (IdStudent)
222 REFERENCES dea.Person (IdPerson)
223 NOT DEFERRABLE
224 INITIALLY IMMEDIATE
225;
226
227-- Reference: Subject_Studies (table: Subject)
228ALTER TABLE tea.Subject ADD CONSTRAINT Subject_Studies
229 FOREIGN KEY (IdStudies)
230 REFERENCES dea.Studies (IdStudies)
231 NOT DEFERRABLE
232 INITIALLY IMMEDIATE
233;
234
235-- Reference: Table_13_Classes (table: Student_Classes)
236ALTER TABLE tea.Student_Classes ADD CONSTRAINT Table_13_Classes
237 FOREIGN KEY (IdClasses)
238 REFERENCES tea.Classes (IdClasses)
239 NOT DEFERRABLE
240 INITIALLY IMMEDIATE
241;
242
243-- Reference: Table_13_Student (table: Student_Classes)
244ALTER TABLE tea.Student_Classes ADD CONSTRAINT Table_13_Student
245 FOREIGN KEY (IdStudent)
246 REFERENCES dea.Student (IdStudent)
247 NOT DEFERRABLE
248 INITIALLY IMMEDIATE
249;
250
251-- Reference: Teacher_Person (table: Teacher)
252ALTER TABLE dea.Teacher ADD CONSTRAINT Teacher_Person
253 FOREIGN KEY (IdTeacher)
254 REFERENCES dea.Person (IdPerson)
255 NOT DEFERRABLE
256 INITIALLY IMMEDIATE
257;
258
259
260-- TASK 2
261
262-- revoke all permissions on public schema
263REVOKE ALL ON SCHEMA public FROM public;
264
265
266-- TASK 3
267
268-- 22:22 a. reports role (read role)
269CREATE ROLE reports;
270GRANT CONNECT ON DATABASE abdtutorial02 TO reports;
271
272GRANT USAGE ON SCHEMA acc TO reports;
273GRANT SELECT ON ALL TABLES IN SCHEMA acc TO reports;
274ALTER DEFAULT PRIVILEGES IN SCHEMA acc GRANT SELECT ON TABLES TO reports;
275
276GRANT USAGE ON SCHEMA dea TO reports;
277GRANT SELECT ON ALL TABLES IN SCHEMA dea TO reports;
278ALTER DEFAULT PRIVILEGES IN SCHEMA dea GRANT SELECT ON TABLES TO reports;
279
280GRANT USAGE ON SCHEMA tea TO reports;
281GRANT SELECT ON ALL TABLES IN SCHEMA tea TO reports;
282ALTER DEFAULT PRIVILEGES IN SCHEMA tea GRANT SELECT ON TABLES TO reports;
283
284-- 23:30 b. admin role (read/write role)
285CREATE ROLE admin;
286GRANT CONNECT ON DATABASE abdtutorial02 TO admin;
287
288GRANT USAGE ON SCHEMA acc TO admin;
289GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA acc TO admin;
290ALTER DEFAULT PRIVILEGES IN SCHEMA acc GRANT SELECT, INSERT , UPDATE, DELETE ON TABLES TO admin;
291GRANT USAGE ON ALL SEQUENCES IN SCHEMA acc TO admin;
292ALTER DEFAULT PRIVILEGES IN SCHEMA acc GRANT USAGE ON SEQUENCES TO admin;
293
294GRANT USAGE ON SCHEMA dea TO admin;
295GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA dea TO admin;
296ALTER DEFAULT PRIVILEGES IN SCHEMA dea GRANT SELECT, INSERT , UPDATE, DELETE ON TABLES TO admin;
297GRANT USAGE ON ALL SEQUENCES IN SCHEMA dea TO admin;
298ALTER DEFAULT PRIVILEGES IN SCHEMA dea GRANT USAGE ON SEQUENCES TO admin;
299
300GRANT USAGE ON SCHEMA tea TO admin;
301GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tea TO admin;
302ALTER DEFAULT PRIVILEGES IN SCHEMA tea GRANT SELECT, INSERT , UPDATE, DELETE ON TABLES TO admin;
303GRANT USAGE ON ALL SEQUENCES IN SCHEMA tea TO admin;
304ALTER DEFAULT PRIVILEGES IN SCHEMA tea GRANT USAGE ON SEQUENCES TO admin;
305
306-- c. deanery_write
307CREATE ROLE deanery_write;
308GRANT CONNECT ON DATABASE abdtutorial02 TO deanery_write;
309
310GRANT USAGE ON SCHEMA dea TO deanery_write;
311GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA dea TO deanery_write;
312ALTER DEFAULT PRIVILEGES IN SCHEMA dea GRANT SELECT, INSERT , UPDATE, DELETE ON TABLES TO deanery_write;
313GRANT USAGE ON ALL SEQUENCES IN SCHEMA dea TO deanery_write;
314ALTER DEFAULT PRIVILEGES IN SCHEMA dea GRANT USAGE ON SEQUENCES TO deanery_write;
315
316-- d. teacher
317CREATE ROLE teacher;
318GRANT CONNECT ON DATABASE abdtutorial02 TO teacher;
319
320GRANT USAGE ON SCHEMA tea TO teacher;
321GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tea TO teacher;
322ALTER DEFAULT PRIVILEGES IN SCHEMA tea GRANT SELECT, INSERT , UPDATE, DELETE ON TABLES TO teacher;
323GRANT USAGE ON ALL SEQUENCES IN SCHEMA tea TO teacher;
324ALTER DEFAULT PRIVILEGES IN SCHEMA tea GRANT USAGE ON SEQUENCES TO teacher;
325
326GRANT USAGE ON SCHEMA dea TO teacher;
327GRANT SELECT ON TABLE dea.Student TO teacher;
328-- ALTER DEFAULT PRIVILEGES IN SCHEMA dea GRANT SELECT ON TABLE dea.Student TO teacher;
329-- ^this doesn't make sense bc TABLE dea.Student cannot be created in the future (it already exists!)
330
331-- e. accounting
332CREATE ROLE accounting;
333GRANT CONNECT ON DATABASE abdtutorial02 TO accounting;
334
335GRANT USAGE ON SCHEMA acc TO admin;
336GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA acc TO admin;
337ALTER DEFAULT PRIVILEGES IN SCHEMA acc GRANT SELECT, INSERT , UPDATE, DELETE ON TABLES TO admin;
338GRANT USAGE ON ALL SEQUENCES IN SCHEMA acc TO admin;
339ALTER DEFAULT PRIVILEGES IN SCHEMA acc GRANT USAGE ON SEQUENCES TO admin;
340
341
342-- TASK 4
343
344CREATE USER reporter1 WITH PASSWORD 'reporter1';
345GRANT reports TO reporter1;
346
347CREATE USER admin1 WITH PASSWORD 'admin1';
348GRANT admin TO admin1;
349
350CREATE USER deaner1 WITH PASSWORD 'deaner1';
351GRANT deanery_write TO deaner1;
352
353CREATE USER teacher1 WITH PASSWORD 'teacher1';
354GRANT teacher TO teacher1;
355
356CREATE USER accountant1 WITH PASSWORD 'accountant1';
357GRANT accounting TO accountant1;
358
359/*
360CHECKING if all roles have been created correctly and
361 permissions have been assigned correctly:
362
363SET search_path TO acc, dea, tea;
364
365-- add new tables to each schema to check wheater
366-- existing users have permissions to read or write their contents too
367\c abdtutorial02 postgres;
368CREATE TABLE acc.AccTestTable;
369CREATE TABLE dea.DeaTestTable;
370CREATE TABLE tea.TeaTestTable;
371
372-- checking reports role
373\c abdtutorial02 reporter1;
374SELECT * FROM acc.Account;
375SELECT * FROM dea.Student;
376SELECT * FROM tea.Classes;
377SELECT * FROM acc.AccTestTable;
378
379-- checking admin role
380\c abdtutorial02 admin1;
381INSERT INTO acc.Account () VALUES ();
382INSERT INTO acc.AccTestTable () VALUES ();
383 -- and so on ...
384
385-- checking deanery_write role:
386
387-- chekcing teacher role:
388
389--checking accounting role:
390
391*/
392
393-- End of file.
394
395