db-schema-auth.md

EDR auth Schema Documentation

Overview

The auth schema holds tables, views and functions used to manage user identity, authentication and authorisation.

Figure auth schema tables.

Tables and views

auth.aggregated__authorisation [view]

Aggregates operators into arrays according to manage their JWT authenticated access rights on the register as granted against the register or its ancestors. Operators that have been denied access to the register, or one of its ancestors, are removed from the arrays.

Column	Data type	Definition
register_id	uuid	UUID if the register the operators are authorised to access/maintain.
register_type	text	The type of register.
register_admin	uuid[]	Array of operators with admin access rights (excludes denied operators).
register_edit	uuid[]	Array of operators with edit access rights (excludes denied operators).
register_read	uuid[]	Array of operators with read access rights (excludes denied operators).

auth.aggregated__authorisation__labelled [view]

As per auth.aggregated__authorisation but with human (and developer) friendly labels in place of UUIDs.

Column	Data type	Definition
register	text	Name of the register the operators are authorised to access/maintain.
register_type	text	The type of register (register or dataset).
register_admin	text	Array of operators with admin access rights (excludes denied operators).
register_edit	text	Array of operators with edit access rights (excludes denied operators).
register_read	text	Array of operators with read access rights (excludes denied operators).

auth.dataset__authorisation [table]

Relates a dataset to authorised operators (see auth.operator) and the access rights they have.

Column	Data type	Key	FK References	Null	Default	Definition
dataset_id	uuid	PK		NO		UUID if the dataset the operator is authorised to access/maintain.
operator_id	uuid	PK		NO		UUID of the authorised operator.
operator_access	USER-DEFINED			NO		The access rights granted to the authorised operator.

Index	Type	Columns
pk_dataset__authorisation	unique; btree	dataset_id, operator_id

auth.operator [table]

Operators - row level security users as authenticated by an authentication service - permitted access to the EDR. The default service is MWLR's authentication service.

Column	Data type	Key	FK References	Null	Default	Definition
id	uuid	PK		NO		The Operator UUID as allocated by the authentication service.
domain	text			NO	'ansis'	The domain/authentication service that allocated the ID.
label	text			NO		The display label, typically the operator's name, for the operator.
active	boolean			NO	true	Marks whether the user is active or not. Inactive operators may not interact with the database and will therefore by ignored for row level security approval.

Index	Type	Columns
pk_operator	unique; btree	id

auth.register__authorisation [table]

Relates a register to authorised operators (see auth.operator) and the access rights they have.

Column	Data type	Key	FK References	Null	Default	Definition
register_id	uuid	PK		NO		UUID if the register the operator is authorised to access/maintain.
operator_id	uuid	PK		NO		UUID of the authorised operator.
operator_access	USER-DEFINED			NO		The access rights granted to the authorised operator.

Index	Type	Columns
pk_register__authorisation	unique; btree	register_id, operator_id

Functions

Function	Type	Arguments	Returns	Description
operator__entity_access	function	_entity_id; _claimed_access	integer	Checks if the operator identified by the current JWT in the request.jwt.claims system setting has the claimed access rights (_claimed_access) against the specified entity (_entity_id). If the claimed rights have been granted to the anonymous user (5a4031c0-2136-411f-a80f-960e14a6d68e) then the input user will have those rights. Returns an HTTP status code: 200, access is ok; 403, access is forbidden to an authorised user (JWT exists); 401, access forbidden to an unauthorised user (anonymous/no JWT); 404, the specified register was not found.
operator__jwt	function		uuid	Returns the operatorUuid value extracted from a JWT captured in the setting request.jwt.claims. Assumes the client, or a DB function supporting the client, has set this value using set_config('request.jwt.claims',...) when accessing the database. If not, the id of the database's anonymous user is returned.
operator__label	function	_operator_id	text	Returns the label for the operator (_operator_id).
operator__object_access	function	_object_id; _claimed_access	integer	Checks if the operator identified by the current JWT in the request.jwt.claims system setting has the claimed access rights (_claimed_access) against the specified object (_object_id). If the claimed rights have been granted to the anonymous user (5a4031c0-2136-411f-a80f-960e14a6d68e) then the input user will have those rights. Returns true/false.
operator__register_access	function	_register_id; _claimed_access	integer	Checks if the operator identified by the current JWT in the request.jwt.claims system setting has the claimed access rights (_claimed_access) against the specified register (_register_id). If the claimed rights have been granted to the anonymous user (5a4031c0-2136-411f-a80f-960e14a6d68e) then the input user will have those rights. Returns an HTTP status code: 200, access is ok; 403, access is forbidden to an authorised user (JWT exists); 401, access forbidden to an unauthorised user (anonymous/no JWT); 404, the specified register was not found.