Row Level Security using Fluent NHibernate with Postgresql

Row-level security (RLS) is a security feature in some databases that allows you to control access to rows in a table based on the user who is accessing the data. In Postgres, you can use the SECURITY DEFINER option when creating a function to implement row-level security.

To use row-level security with Fluent NHibernate, you can create a custom Filter for your entity and apply it to your NHibernate configuration. The filter can be used to restrict access to rows based on the current user or any other criteria.

Here’s an example of how you could implement row-level security for a Person entity in Postgres using Fluent NHibernate:

public class Person
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Organization { get; set; }
}

public class PersonMap : ClassMap<Person>
{
    public PersonMap()
    {
        Table("person");
        Id(x => x.Id).Column("id");
        Map(x => x.Name).Column("name");
        Map(x => x.Organization).Column("organization");
    }
}

public class PersonFilter : FilterDefinition
{
    public PersonFilter()
    {
        WithName("organization_filter")
            .AddParameter("organization", NHibernateUtil.String)
            .WithCondition("organization = :organization");
    }
}

// In your NHibernate configuration
var config = new Configuration();
config.AddFilterDefinition(new PersonFilter());

// To enable the filter for a particular session
var session = sessionFactory.OpenSession();
var filter = session.EnableFilter("organization_filter");
filter.SetParameter("organization", "ACME Inc.");

This will apply a filter to the Person entity that restricts access to only those rows where the organization column is equal to “ACME Inc.”. You can set the value of the organization parameter dynamically based on the current user or any other criteria.

Multitenancy with Shared database scenario

In a multitenancy scenario with a shared database, row-level security can be used to restrict access to rows based on the tenant to which the data belongs. For example, you can create a filter that restricts access to rows where the tenant_id column matches the current tenant’s ID.

Here’s an example of how you could implement tenant-based row-level security in Postgres using Fluent NHibernate:

public class Person
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Organization { get; set; }
    public virtual int TenantId { get; set; }
}

public class PersonMap : ClassMap<Person>
{
    public PersonMap()
    {
        Table("person");
        Id(x => x.Id).Column("id");
        Map(x => x.Name).Column("name");
        Map(x => x.Organization).Column("organization");
        Map(x => x.TenantId).Column("tenant_id");
    }
}

public class TenantFilter : FilterDefinition
{
    public TenantFilter()
    {
        WithName("tenant_filter")
            .AddParameter("tenant_id", NHibernateUtil.Int32)
            .WithCondition("tenant_id = :tenant_id");
    }
}

// In your NHibernate configuration
var config = new Configuration();
config.AddFilterDefinition(new TenantFilter());

// To enable the filter for a particular session
var session = sessionFactory.OpenSession();
var filter = session.EnableFilter("tenant_filter");
filter.SetParameter("tenant_id", currentTenantId);

This will apply a filter to the Person entity that restricts access to only those rows where the tenant_id column matches the current tenant’s ID. You can set the value of the tenant_id parameter dynamically based on the current tenant’s ID.