[FOLIO-3182] Module ROLE and module-tenant ROLEs Created: 01/Jun/21  Updated: 17/Jan/23

Status: Draft
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: Story Priority: P2
Reporter: Julian Ladisch Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: security, security-reviewed
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Relates
relates to FOLIO-1935 Service creating ROLE and SCHEMA on t... Draft
Sprint: CP: R3 2022 roadmap, CP: Roadmap backlog
Development Team: Core: Platform

 Description   

For a module use a module ROLE and for each tenant a module-tenant ROLE.

This allows to re-use a database connection for a different tenant but also ensures tenant separation.

Example:

create role _1_mod_foo noinherit;
create role diku_mod_foo noinherit role _1_mod_foo;
create role tamu_mod_foo noinherit role _1_mod_foo;
create schema diku_mod_foo authorization diku_mod_foo;
create schema tamu_mod_foo authorization tamu_mod_foo;
set role diku_mod_foo;
create table diku_mod_foo.t (id int);
set role tamu_mod_foo;
create table tamu_mod_foo.t (id int);

The module role _1_mod_foo can login but noinherit doesn't assign privileges:

set role _1_mod_foo;
select count(*) from diku_mod_foo.t;
ERROR:  42501: permission denied for schema diku_mod_foo
select count(*) from tamu_mod_foo.t;
ERROR:  42501: permission denied for schema tamu_mod_foo

The tenant role diku_mod_foo can only access the diku_mod_foo schema:

set role diku_mod_foo;
select count(*) from diku_mod_foo.t;
0
select count(*) from tamu_mod_foo.t;
ERROR:  42501: permission denied for schema tamu_mod_foo

The tenant role tamu_mod_foo can only access the tamu_mod_foo schema:

set role tamu_mod_foo;
select count(*) from diku_mod_foo.t;
ERROR: 42501: permission denied for schema diku_mod_foo
select count(*) from tamu_mod_foo.t;
0

This permission setup protects against accidental cross-tenant access.
It makes it very difficult for SQL injection and similar attacks: The SQL command "set role" must be executed before running the actual SQL query.

A key feature of a multi-tenant software is that multiple tenants share the same running instance of code. Using strict database schemas and role permissions ensures pretty good tenant separation. Multi-tenant systems like this are state of the art.

Organisations that want even more separation may choose to run one module instance per tenant for selected modules or all modules (with a single Okapi instance); or they may run multiple single tenant installations (= multiple Okapi instances).

Note:

This module ROLE and module-tenant ROLEs setup should be provided by the service described in FOLIO-1935 Draft "Service creating ROLE and SCHEMA on tenant initialization".



 Comments   
Comment by Craig McNally [ 14/Oct/21 ]

Discussed in context of https://folio-org.atlassian.net/browse/FOLIO-1935.  See comments there for additional details.

Comment by Craig McNally [ 03/Feb/22 ]

The security team reviewed this again and are looking for someone to generate a formal proposal.  See https://folio-org.atlassian.net/browse/FOLIO-1935

Generated at Thu Feb 08 23:26:13 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.