9WQDskcf

1psql -h localhost -Upostgres -d collections
2
3CREATE TABLE testing_films (
4code        char(5) CONSTRAINT firstkey PRIMARY KEY,
5title       varchar(40) NOT NULL,
6date_prod   date
7);
8\dt
9
10************ create a script /scripts/create_readonly_user.sql ************
11DROP ROLE IF EXISTS readly;
12CREATE ROLE readly;
13GRANT USAGE ON SCHEMA public TO readly;
14GRANT SELECT ON ALL TABLES IN SCHEMA public TO readly;
15ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readly;
16CREATE USER readonly WITH PASSWORD 'xxxx';
17GRANT readly TO readonly;
18
19**** read the script *************
20psql -h localhost -Upostgres -d collections
21collections=# \i scripts/create_readonly_user.sql 
22
23
24test the user 'readonly'
25****************************** login in as readonly ****************************
26psql -h localhost -Ureadonly -d collections
27-> secret
28
29(1)
30collections=> \dt
31- visar alla tabeller inklusive 'films'
32collections=> select * from films;
33 code  |     title     | date_prod  
34-------+---------------+------------
35 a1    | korpen flyger | 2018-10-15
36(1 row)
37
38(A) Select * 
39collections=> select * from testing_films;
40 code | title | date_prod 
41------+-------+-----------
42(0 rows)
43
44(B) Try to Insert
45insert into testing_films values('a1','korpen flyger','1984-08-04');
46-> ERROR:  permission denied for relation testing_films
47
48(C) Try to drop the table
49collections=> drop table testing_films;
50ERROR:  must be owner of relation testing_films
51
52
53SELECT * from pg_user;
54SELECT * FROM pg_roles;