The adoption of multi-tenancy architecture has become increasingly prominent. Multi-tenancy, where a single software instance simultaneously serves multiple client organizations, is not just a technological choice but a strategic one for many SaaS businesses.
Recent trends in cloud computing demonstrate the growing importance and widespread adoption of multi-tenancy. In 2023, it's estimated that every organization either uses a public or private cloud. This shift towards cloud-first strategies is evident, with around 85% of companies expected to follow such an approach by 2025. Moreover, the public cloud market has seen significant revenue growth, increasing by 21% to reach approximately $592 billion in 2023.
Furthermore, the shift to cloud computing is seen as crucial for business survival and growth, especially in uncertain economic times. Enterprises are increasingly spending more on public cloud services compared to traditional IT solutions, with Gartner predicting that by 2025, enterprises will spend more on public cloud services than traditional IT solutions.
Incorporating the Row-Level Security policy in databases like PostgreSQL enhances the appeal of multi-tenancy by offering granular data security. RLS allows for controlled access to rows in a database table, which is essential in multi-tenant environments where data privacy and compliance are paramount.
What will you learn from this article:
- What is multi-tenancy and why is it easier to implement?
- Impact of multi-tenancy on SaaS model
- Row-Level-Security features and benefits
- Row-Level-Security in PostgreSQL
Table of Contents
What is Multi-tenancy?
Before diving into the row-level security we need to explain what Multi-tenancy is.
Multi-tenancy is an architectural concept predominantly used in cloud computing and software applications, where a single instance of software runs on a server, simultaneously serving multiple client organizations, or "tenants." In this model, while the application is common for all tenants, each tenant's data is kept separate and inaccessible to others.
Multi-tenancy efficiently utilizes computing resources and simplifies the overall management of the software, contrasting with single-tenancy architectures where each tenant would have their own software instance. While single-tenancy has its own potential benefits and specific use cases, the multi-tenant approach is easier to implement and less expensive to maintain.
Why Multi-tenancy is Great for SaaS Model?
In the SaaS model, multi-tenancy offers numerous advantages that align perfectly with the principles of cloud services. Firstly, it significantly reduces costs. When resources are shared, the cost of software development, maintenance, and infrastructure is spread across multiple tenants, making it economically advantageous.
Secondly, it simplifies updates and upgrades. Any update to the application instantly benefits all tenants, ensuring that everyone is on the latest version without requiring individual updates. This ease of management and maintenance is a substantial benefit for both the service provider and the clients.
Additionally, multi-tenancy inherently supports software scalability. As a business grows, the SaaS application can accommodate more tenants or more users per tenant without requiring major architectural changes or additional instances of the software. This scalability is crucial for businesses that anticipate growth or experience fluctuating demand.
What is Row-level Security?
There are various approaches to implementing multi-tenancy, each having its own benefits and drawbacks depending on the use case. Row-level security could be one solution.
Row-level security is a database security feature that controls access to rows in a database table. Tables can have row security policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands.
Unlike traditional security measures that control access at the database or table level, row-level security allows for more granular control. Policies dynamically apply restrictions to the database on the user executing a query. This means that different users may see different sets of data from the same table, depending on their permissions. Policies can be defined based on user attributes, such as their role, identity, or membership in a particular group. For example, a policy could ensure that a user only sees rows related to their department or project.
By implementing row-level security, organizations can enhance data security and privacy, ensuring that all data is scoped and users only access the data they are authorized to view. This is particularly important in multi-tenant environments where data from different tenants resides in the same database.
Row-level Security in PostgreSQL
Row-level security is typically implemented via roles or users inside the database. This, however, requires separate connections when executing queries and managing users in the database. SaaS applications, might not be straightforward and this could add unnecessary complexity. Thankfully, you can move this part to the application layer which makes things easier.
Your application will consist of two types of tables:
- shared tables/management tables (example: list of tenants, available plans, database meta tables like migrations, etc.)
- tenant tables - data with row-level security applied that is isolated from the rest
Each tenant table should have a `tenant_id` column that identifies to which tenant data belongs. For each of these tables, you need to enable row-level security and apply the policy to restrict data access.
As we moved setting the user to the application layer we need to have a way to set the current user automatically to avoid any mistakes when organizing the connections and restrict access.
Most of the applications or ORMs have some form of a connection pool that you can hook your code to. To simplify this, look at the diagram below:
We have a pool of connections and every time an application and the actual user want to connect to the database, it takes one connection from the pool. This way we can automatically set the tenant_id and once it is done, the tenant is reset and the connection is released back to the pool.
Such an approach reduces developer mistakes making it transparent to use.
In a code, this could look like this:
First, we need a shared tenants table (organizations/workspaces/brands/…):
CREATE TABLE tenants(
id uuid DEFAULT uuid_generate_v4(),
name character varying,
CONSTRAINT pk_tenants PRIMARY KEY (id)
)We also need to assign default privileges for separate users for accessing tenant data so it includes all future tables as well:
ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA public GRANT ALL ON TABLES TO app_user.Next we we can create tenant tables:
CREATE TABLE animals(
…
tenant_id uuid NOT NULL DEFAULT current_setting('rls.tenant_id', true)::uuid,
…
)Here, we apply the default current setting which must be set when acquiring the connection, and unset when releasing the connection back to the pool. Alternatively, this can be implemented via triggers. Now row-level security can be enabled and policy created:
ALTER TABLE animals ENABLE ROW LEVEL SECURITYCREATE POLICY rls_policy ON animals
FOR ALL USING (tenant_id = current_setting('rls.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('rls.tenant_id')::uuid)Here, we apply the default current setting which must be set when acquiring the connection, and unset when releasing the connection back to the pool. Alternatively, this can be implemented via triggers.
Now row-level security can be enabled and policy created:
ALTER TABLE animals ENABLE ROW LEVEL SECURITYCREATE POLICY rls_policy ON animals
FOR ALL USING (tenant_id = current_setting('rls.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('rls.tenant_id')::uuid)Managing policies and enabling row-level security every time for new tenant tables might be automated via event triggers if you prefer that over explicit RLS & policy management.
All you need to do is to read changes from `pg_event_trigger_ddl_commands` where the command tag is `CREATE TABLE` and execute these two queries in a trigger function. In the trigger function, you should also exclude all the shared tables that you don’t want to have RLS enabled.
Now, with a simple setting switch in your application code, you can ensure that you only access specific tenant data. The last thing to remember is that when you create unique constraints you should always include tenant_id as a first column - you want your data unique but within a specific tenant.
Benefits of Row-level Security
To effectively address data security and compliance challenges in modern web applications, implementing row-level security in PostgreSQL offers a robust and versatile solution to achieve an advanced security system. Here's an overview of the key benefits you will get when you apply row-level security - demonstrating its critical role in ensuring data integrity and regulatory compliance:
Secured by Default
Row-level security ensures data security by default, as it enforces access controls directly in the database. This means all data-based information is shielded at the most fundamental level, providing robust protection against sensitive data exposure and unauthorized access.
GDPR Ready
With row-level security, compliance with GDPR is improved. It allows for precise control over individual row data, making it straightforward to delete or anonymize personal data as required by GDPR, thus simplifying the management of the security mechanism of user data privacy.
Reduced Surface of Developer Mistakes
By implementing row-level security, the risk of developer errors leading to data breaches is significantly reduced. Access controls are managed at the database level, which minimizes the chances of accidental sensitive data exposure through application-level mistakes.
Low Performance Overhead
Row-level security is designed to be efficient, having minimal impact on database performance. This efficiency ensures that implementing stringent security policies and measures do not come at the cost of slowing down database-level operations.
ORM Agnostic
One of the key benefits of row-level security is its ORM (Object-Relational Mapping) agnosticism. This means it can work seamlessly with various ORM tools, providing a flexible and secure approach to data access control without being tied to any specific ORM technology.
Summary Row-level Security
The strategic adoption of multi-tenancy architecture and row-level security in PostgreSQL is reshaping how businesses approach SaaS models with a more advanced security system. These technological advancements are not just about the technicalities - they are about the transformation of business operations and data-level security in the cloud era.
With an increasing number of enterprises adopting cloud-first strategies, the relevance of multi-tenancy, row access policies, and sophisticated data security measures like row-level security in PostgreSQL has never been more critical.
This article focuses on the practical implications and strategic importance of these technologies in the modern business environment.
Additionally, the article sheds light on the implementation of row-level security in PostgreSQL, illustrating how this approach to data-level security can strengthen access control, reduce the risk of data breaches, and ensure data privacy in multi-tenant architectures.
