SSL Certificate for domain name validation. 

Problem statement: Recently, the certificate for a website I host by name https://booksonsoftware.com/algorithms had expired. When the certificate was renewed, it could not be used as the earlier ones as the error encountered from the nodeJs application stated: 

Error: error:0B080074:x509 certificate routines:X509_check_private_key:key values mismatch 

    at Error (native) 

    at Object.createSecureContext (_tls_common.js:85:17) 

    at Server (_tls_wrap.js:775:25) 

    at new Server (https.js:26:14) 

    at Object.exports.createServer (https.js:47:10) 

    at Object.<anonymous> (/home/ravi/myapp/algorithms/booksonsoftware/app.js:140:25) 

    at Module._compile (module.js:577:32) 

    at Object.Module._extensions..js (module.js:586:10) 

    at Module.load (module.js:494:32) 

    at tryModuleLoad (module.js:453:12) 

This article explains the resolution for this error. 

Solution: The certificate was already renewed from the cloud service portal by using the same private key as earlier. 

This was then exported from the Portal and uploaded to the host machine where the site was hosted. 

The certificate was in the pfx format so this required the following commands to export the certificate and private key 

1. openssl pkcs12 -in ravirajamanikeyvault-booksonsoftwared9e73889-3e5d-485b-81c7-28227725e796-20221108.pfx -nocerts -out booksonsoftware-private-key.pem -nodes 

to extract the private key. 

2. openssl pkcs12 -in ravirajamanikeyvault-booksonsoftwared9e73889-3e5d-485b-81c7-28227725e796-20221108.pfx -nokeys -out booksonsoftware-cacert.pem  
   to extract the certificate 

with this the app.js file can specify the certificate as: 

var https_options = { 

    key: fs.readFileSync('booksonsoftware-private-key.pem','utf8'), 

    cert: fs.readFileSync('booksonsoftware-cacert.pem', 'utf8') 

}; 

And 

The server can be started with: 

var httpsServer = https.createServer(https_options, app).listen(8083, function(){ 

  console.log('Express server listening on port 8083 '); 

}); 

Express server listening on port 8083 

Typical port values for https are 8443 but in this case, I wanted to use this port. 

When the key-values for the cert and the private key don’t match, the certificate may need to be rekeyed. 

Rekeying a certificate generates a new key and certificate with the same name and expiration date as a previously purchased certificate. This is performed at no charge by the cloud service provider that issued the certificate request. It is usually done when the keys are lost or compromised. 

It is easy to check if the private key and the certificate match with the following commands to display the hash: 

• openssl pkey -pubout -in <private-key-name> | openssl sha256 

• openssl x509 -pubkey -in <certificate-name> -noout | openssl sha256 

If the hash values don’t agree, then there is a mismatch. 

One of the reasons for the mismatch has been found to be the presence of chained certificates in the certificate file extracted from the pfx. If we edit this to just retain the contents between ----BEGIN CERTIFICATE---- and ----END CERTIFICATE----, the error disappears. 

 

Multitenant architecture patterns

 

Several patterns can help plan and build the data architecture for SaaS applications. A well-designed SaaS application can demonstrate scalability, configurability, zero downtime and multi-tenant efficiency. These qualities cannot be mutually exclusive. For example, optimizing for multitenant efficiency in a shared environment must not compromise the level of security safeguarding data access. A security pattern to resolve this conflict involves the use of “virtual isolation” mechanisms such as permission, SQL views and encryption.

Trusted database connections:

Access to data stored in databases is secured using one of two methods: impersonation and trusted subsystem account. The former enables users to access different database objects. The latter is for applications to connect to database using process identity and involves additional security to be implemented in the application itself. For multitenant applications where each tenant grants access to end user accounts, a hybrid approach is justified.

Secure database tables

This involves granting select, update, insert, delete on [TableName] for [UserName] and must be done once during the tenant provisioning process. It is appropriate for separate database and separate schema approaches.

Tenant View Filter:

SQL Views can be used to grant individual tenants access to some of the rows in each table, while preventing them from accessing other rows. A predicate is added to filter the records from say a SELECT statement. This predicate can use a built-in function to determine the security identifier of the user account accessing the database and matched with the column values corresponding to a tenant. Unlike secure database tables pattern, this uses shared schema with tenant qualification.

Tenant Data Encryption:

A way to further protect tenant data is by encrypting it within the database. Encryption can be done with both symmetric as well as asymmetric key. In symmetric cryptography, a key is used to encrypt and decrypt data. In asymmetric cryptography, two keys are used, namely, the private key and the public key. Data is encrypted with the public key but decrypted with the private key. Public key cryptography requires significant more computing power. A better approach might be to use a key wrapping system that combines the advantage of both systems.

Extensibility patterns include custom columns and preallocated fields.  Since different organizations have their own unique needs, some customizations are required. Preallocated fields is a technique to simply include a preset number of custom fields in every table. These additional fields are used differently by different tenants.

Custom fields are limited by their number. An alternative technique is to use tagging with name value pairs. When metadata defines separate labels and data types for each of the tenants’ custom fields, the data model can be extended arbitrarily. The main drawback is that it adds a level of complexity for database functions such as indexing, querying, and updating records.

Custom columns are those that can be added to the tenant’s tables directly. Custom rows can be added to a dedicated table without altering the data model for other tenants.

Data model extensions help only with the storage and not the operations. Any extension must be paired with a mechanism for integrating the additional fields into the application’s functionality.

Scalability patterns are useful for large scale enterprise software. Scalability is even more important because data belonging to all the customers must be supported. ISVs building on-premises software might be familiar with shifting minor leagues to majors, but the game also changes because the scope widens to supporting a vast user base. Databases can be scaled up or out and it is important to differentiate between scaling the application and scaling the data.

 

3 distinct Multitenant data architecture:

 

Introduction:

The earlier articles focused on developing a rigid definition and framework for evaluating and comparing different multitenant data architectures. A set of seventeen criteria were used to comprehensively articulate the effects of the choices made in the multitenant architectures and these choices manifested in combinations of application and database variations as (AD, DD), (AS, DD), (AI, DD), (AD, DS), (AS, DS), (AI, DS), (AD, DB), (AS, DB), (AI, DB), (AD, DC), (AS, DC), (AI, DC) where the notations are explained in the legend. This article talks about the best practices in terms of patterns for realizing the data architectures.

Description:

A number of patterns can help plan and build the data architecture for SaaS applications. A well designed SaaS application can demonstrate scalability, configurability, zero downtime and multi-tenant efficiency. These qualities cannot be mutually exclusive. For example, optimizing for multitenant efficiency in a shared environment must not compromise the level of security safeguarding data access. A security pattern to resolve this conflict involves the use of “virtual isolation” mechanisms such as permission, SQL views and encryption.

The table below lists some of these patterns:

Approach	Security Patterns	Extensibility Patterns	Scalability Patterns
Separate databases	Trusted database connections Secure database tables Tenant data encryption	Custom columns	Single Tenant scaleout
Shared database, Separate schema	Trusted database connections Secure database tables Tenant Data encryption	Custom columns	Tenant-based horizontal partitioning
Shared database, shared schema	Trusted database connections Tenant View Filter Tenant Data Encryption	Preallocated fields Name-Value pairs	Tenant-Based Horizontal Partitioning

Trusted database connections:

Access to data stored in databases is secured using one of two methods: impersonation and trusted subsystem account. The former enables users to access different database objects. The latter is for applications to connect to database using process identity and involves additional security to be implemented in the application itself. For multitenant applications where each tenant grants access to end user accounts, a hybrid approach is justified.

Secure database tables

This involves granting select, update, insert, delete on [TableName] for [UserName] and must be done once during the tenant provisioning process. It is appropriate for separate database and separate schema approaches.

Legend:

AD-  A dedicated application server is running for each tenant, and therefore, each tenant receives a dedicated application instance.

AS – a single application server is running for multiple tenants and each tenant receives a dedicated application instance.

AI – a single application server is running for multiple tenants and a single application instance is running for multiple tenants.

DD – a dedicated database server is running for each tenant and therefore the database is also isolated.

DS – a single database server is running for multiple tenants and each tenant gets an isolated database.

DB – a single database server and a single database is being used for multiple tenants

DC – a single database server is running for multiple tenants and data from multiple tenants is stored in a single database and in a single set of tables with same database schema but separation based on records.

 

Challenges with multitenant architecture choices:

Between single-tenancy and multitenancy, the challenges are different and somewhat more complex. A few are listed below and called out for their specificity. The architectural choices in terms of application and databases are:

AD- a dedicated application server is running for each tenant, and therefore, each tenant receives a dedicated application instance.

AS – a single application server is running for multiple tenants and each tenant receives a dedicated application instance.

AI – a single application server is running for multiple tenants and a single application instance is running for multiple tenants.

DD – a dedicated database server is running for each tenant and therefore the database is also isolated.

DS – a single database server is running for multiple tenants and each tenant gets an isolated database.

DB – a single database server and a single database is being used for multiple tenants

DC – a single database server is running for multiple tenants and data from multiple tenants is stored in a single database and in a single set of tables with same database schema, but separation based on records.

1.       Sharing of resources and higher than average hardware utilization, performance may be compromised. It must be ensured that all tenants get to consume resources. If one tenant clogs up resources, the performance of all other tenants may be compromised. This is specific to multitenancy and not single tenancy.  In a virtualized instances situation, this problem is solved by assigning an equal amount of resources to each instance. The solution may lead to very inefficient utilization of resources and may not suit all multitenant systems.

2.       Scalability: When tenants share the same application and database, scalability suffers. An assumption with single-tenancy is that tenants do not need more than one application or database but there are no such limitations that exist when placing multiple tenants on one server. Tenants from various geographies can use the same application which affects its scalability. In addition, geographies pose constraints, legislations and regulations. For example, EU mandates that invoices sent from within the EU must be stored within the EU. Additional constraints can be brought by tenant to place all the data on the same server to speed up queries.

3.       Security: When security is compromised, the risk for data stealing is high. In a multitenant environment, a security breach can result in the exposure of data to other, possibly competitive tenants. Data protection becomes an important challenge to tackle.

4.       Zero-downtime: Introducing new tenants or adapting to changing business requirements of existing tenants brings along the need for constant growth and evolution of a multi-tenant system.