In this exercise, some role managing and security operations are performed. Firstly, two accounts with login credentials are added, one of which will only be valid until a certain time.
CREATE ROLE carto_01 LOGIN PASSWORD 'xxxxxxxxxxxxxxxx';
CREATE ROLE intern_01 LOGIN PASSWORD 'xxxxxxxxxxxxxxxx' VALID UNTIL '2021-03-01'; 
The usernames have been created now, both the passwords were set by the administrator. To force the user to set a new password, we could ask them to log on via console and run the password command. The user will be queried for a new password and asked to confirm the password, which will not be visible for the administrator. The admin can only access the md5 hash, which over the length of 10 characters are almost uncrackable if the password does not appear on any password lists.
postgres=# \password
Enter new password:
Enter it again:
postgres=# 
The users are then given read-only permissions with the following command.
Now, when trying to perform database operations such as adding or deleting entries, the database throws an error because the user does not have the required permissions.
GRANT SELECT ON salzburg_buildings TO carto_01;
GRANT SELECT ON salzburg_buildings TO intern_01; 
To allow less privileged users to perform certain queries, a view with predefined operations is created. The columns which the users are allowed to see are chosen, therefore the other secret columns remain hidden in the view.
CREATE OR REPLACE VIEW intern_view AS
SELECT
b.id,
b.name,
b.geom,
b.osm_id,
b.code,
b.fclass,
b.type
FROM
salzburg_buildings AS b;
GRANT SELECT ON intern_view To intern_01; 
Next, the query is additionally restricted in order to only give the intern the possibility to view objects from the buildings dataset within a certain geographic area. A box is created and intersected with the whole database. In the end, only the buildings within the box are returned.
CREATE OR REPLACE VIEW intern_view AS
SELECT
b.id,
b.name,
b.geom,
b.osm_id,
b.code,
b.fclass,
b.type
FROM
salzburg_buildings AS b,
(SELECT geom FROM security_geofence WHERE project = 'intern') AS i
WHERE
st_intersects(b.geom, i.geom); 
Now, the view is additionally restricted, returning only those buildings where the security_level column is set to ‘public’.
CREATE OR REPLACE VIEW intern_view AS
SELECT
b.id,
b.name,
b.geom,
b.osm_id,
b.code,
b.fclass,
b.type
FROM
salzburg_buildings AS b,
(SELECT geom FROM security_geofence WHERE project = 'intern') AS i
WHERE
st_intersects(b.geom, i.geom) AND b.security_level='public'; 
Performing this filtering of course also eliminates the building footprints. Since only the other columns are sensitive, we can keep the geometries but hide these columns from the interns.
The following command basically created two tables, once for all geometries containing only the id, name and geom and once for the columns the intern is allowed to see in general, which are then combined using the UNION statement. The table where all but the public rows are included is filled up with null values in order to have the same column numbers, which is necessary to complete the union statement.
The final result is a view that returns all building geometries of the geofenced area, but the table only contains the sensitive information of the public records.
CREATE OR REPLACE VIEW intern_view AS
SELECT
b.*
FROM
(SELECT
b.id,
b.name,
b.geom,
b.osm_id,
b.code,
b.fclass,
b.type
FROM
salzburg_buildings AS b
WHERE
b.security_level = 'public'
UNION
SELECT
n.id, n.name, n.geom, null, null, null, null
FROM
salzburg_buildings AS n
WHERE
n.security_level != 'public') AS b,
(SELECT geom FROM security_geofence WHERE project = 'intern') AS i
WHERE
st_intersects(b.geom, i.geom); There are of course many scenarios where the measures implemented in this task are not sufficient, some of them are listed below.
- For now we only defined viewing rights, which is not always helpful. If we want the interns create new records, we need to prevent them from altering or dropping unrelated entries.
- Maybe applications need to and are allowed to view certain entries/tables without being signed in at all, for example app users.
- When new tables are created, the view needs to be adapted or a new one created. Maybe the intern should be able to access all tables within a certain geofence?
