[FOLIO-1935] Service creating ROLE and SCHEMA on tenant initialization Created: 03/Apr/19  Updated: 22/Sep/23

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

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

Issue links:
Duplicate
is duplicated by RMB-360 Tenant postgres db initialization fai... Closed
Relates
relates to MODFISTO-214 Illegal cross-module *_mod_finance_st... Closed
relates to FOLIO-595 Superuser Closed
relates to MODFISTO-239 Illegal cross-module *_mod_orders_sto... Closed
relates to MODFISTO-236 Migration scripts cross module databa... Closed
relates to FOLIO-3182 Module ROLE and module-tenant ROLEs Draft
relates to RMB-651 Change Database objects ownership pol... Open
relates to FOLIO-1794 SPIKE: db connection provision and po... Open
relates to FOLIO-2031 Remove tenant name and mod_ prefix fr... Open
relates to FOLIO-2862 Stop using superuser rights on the da... Open
relates to MODFISTO-214 Illegal cross-module *_mod_finance_st... Closed
relates to FOLIO-2551 SPIKE: System and Tenant Level Users... Closed
relates to MODORDSTOR-213 Illegal cross-module *_mod_finance_st... Closed
relates to MODFISTO-239 Illegal cross-module *_mod_orders_sto... Closed
relates to MODFISTO-236 Migration scripts cross module databa... Closed
relates to UXPROD-1819 FOLIO database architecture improvements Draft
Sprint: CP: R3 2022 roadmap, CP: Roadmap backlog
Development Team: Core: Platform

 Description   

When Okapi enables a module for a new tenant Okapi invokes that module's tenant API and the module creates a database ROLE and a database SCHEMA for that combination of module and tenant in PostgreSQL.

The module needs advanced database privileges to create the ROLE and the SCHEMA. But this also allows the module to access data of all other modules and all other tenants. This is a security issue. Each module should have access to its own data but not to other module's data.

A possible solution:

On tenant initialization Okapi calls a service that creates ROLE and SCHEMA for that combination of module and tenant. Okapi then passes the ROLE's credentials to the module. When Okapi invokes the tenant API for tenant creation the module can create the tables etc. in the SCHEMA using the ROLE, no advanced database privileges needed.
Required changes:

  • A service that creates a ROLE (including credentials) and SCHEMA for a combination of module and tenant. This service is the only place with advanced database privileges (only privileges for creating ROLEs and SCHEMAs, no superuser privilege required).
  • A storage that saves the credentials of all ROLEs.
  • A way to inject the ROLE credentials to a module. Needed for tenant initialization and on module restart.

Note: This should be combined with the module-tenant ROLEs described in FOLIO-3182 Draft .



 Comments   
Comment by Jakub Skoczen [ 02/Jun/21 ]

Julian Ladisch Adam Dickmeiss It's a good proposal, inline with that we have discussed earlier. I wonder if, instead of a standalone service this should be part of Okapi, to simplify the init procedure. I also wonder if there are existing solutions that could make managing Postgres credentials simpler (e.g a proxy of some sort mapping module IP addresses to creds, etc).

Comment by Adam Dickmeiss [ 02/Jun/21 ]

I think it's a terrible idea to move knowledge abt db structures into Okapi. Until now a module could be tested and executed wo okapi. What abt purge ? Requre okapi too?

Comment by jroot [ 04/Jun/21 ]

As I see it, there are two levels here:

  • One is the creds needed to connect to the DB in the first place.
  • The next is a role with certain permissions and creds that gets created specifically for each module’s schema when that module is init’d for the tenant.

 

As it is now the Folio storage modules use that initial DB connection foo (folio_admin, password, along with the DB URL and port) to create the role, level of access, and creds for the schema/schemas in its domain.

Going forwards, each storage module's code would need to be re-written to take these changes into account, as they essentially connect with what is a DB superuser (folio_admin) when they spin up.

Don't forget that this is the same DB superuser, regardless of tenant, in a multi-tenant environment using the same instance of Folio.

I can only assume these "Okapi Service" creds are stored in something that a secret store would use (a dedicated DB outside of Folio’s DB? Some Secret passed in to Okapi?) This will be the most tricky part... How would you first spin up a module, and edit or change what env vars it would use after these DB access creds are created upon tenant init? Seems like a very chicken and egg type of situation...

 

Bootstrapping the Folio platform has always proven problematic... Perhaps it's better that a module is given some sort of higher level bootstrap access (a user in the DB that can create roles, etc...) then restarts its service connecting using only the needed user/roles/permissions/password to do what it needs to do in its schema?

The proposal here is a major re-architect of the system... And quite honestly, at this stage in the project - If you're going to rewrite how these things are bootstrapped in you might as well rewrite to account for the rest of the bootstrap issues Folio has! (Creating supertenant admin for securing Okapi, tenant admin users, what is considered a system user of Okapi/Folio platform vs a Patron)

Comment by Craig McNally [ 24/Sep/21 ]

Jakub Skoczen there hasn't been movement on this in a while, the security team is wondering if the core-platform team made any progress on this, or the related issues?  Do you have any idea when this will be prioritized by the CP team?

Comment by Craig McNally [ 14/Oct/21 ]

Discussed with Jakub Skoczen and the Security team... We agreed that we need to sort out the details here and generate a formal proposal that can be reviewed by the TC/security team.  The end goal being a formal decision.

Comment by Craig McNally [ 03/Feb/22 ]

We agreed that we need to sort out the details here and generate a formal proposal

Jakub Skoczen / VBar is this something either the Solution architects or the core platform team would be willing to take on?

Comment by Jakub Skoczen [ 14/Feb/22 ]

Craig McNally I think this is something that the Core Platform could take on but it won't be started until the Token Expiration/System users work is concluded.

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