Row Level Security using Go and PostgreSQL

What is Row Level Security (RLS)?

Row level security (RLS for short) is a PostgreSQL security feature provided by the PostgreSQL database. It allows database administrators to define policies to control how specific rows of data display and operate for one or more user roles.

You can create row level security policies for specific commands like SELECT, INSERT, UPDATE, and DELETE or you can specify it for ALL commands.

What to consider when implementing RLS

Enable RLS on a table using ALTER TABLE ... ENABLE ROW LEVEL SECURITY. Without this, policies won’t be applied.
Define RLS policies with the CREATE POLICY command for each table.
Policies control specific operations like SELECT, UPDATE, and DELETE.
They apply only to rows that meet a specified SQL condition.
USING clauses filter existing rows based on the policy condition.
WITH CHECK clauses validate new rows before insertion or modification.
Policies can share the same name across multiple tables, allowing reuse.
Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table.

Important to note

Enabling and disabling row security, as well as adding policies to a table, is always the privilege of the table owner only.
Each policy has a name and multiple policies can be defined for a table.
When multiple policies apply to a given query, they are combined using either OR (for permissive policies, which are the default) or using AND (for restrictive policies).
A given role has the privileges of all roles that they are a member of.

RLS Example (using Go and GORM)

package main

import (
    "fmt"
    "gorm.io/gorm"
    "gorm.io/driver/postgres"
)

// Tenant represents a customer organization
type Tenant struct {
    gorm.Model
    Name string
}

// User represents an application user associated with a tenant
type User struct {
    gorm.Model
    Email     string
    TenantID  uint
    Tenant    Tenant
}

// Data represents any tenant-specific data
type Data struct {
    gorm.Model
    Content   string
    TenantID  uint
    Tenant    Tenant
}

// TenantMiddleware wraps the database connection with tenant context
type TenantMiddleware struct {
    DB *gorm.DB
    CurrentTenantID uint
}

// Initialize RLS policies and create necessary functions
func InitializeRLS(db *gorm.DB) error {
    // Create the security policy function
    _, err := db.Raw(`
        CREATE OR REPLACE FUNCTION tenant_security_policy(tenant_id bigint)
        RETURNS boolean AS $$
        BEGIN
            RETURN (current_setting('app.current_tenant_id')::bigint = tenant_id);
        END;
        $$ LANGUAGE plpgsql;
    `).Exec()
    if err != nil {
        return fmt.Errorf("failed to create security policy function: %v", err)
    }

    // Enable RLS on the data table
    _, err = db.Raw(`
        ALTER TABLE data ENABLE ROW LEVEL SECURITY;
    `).Exec()
    if err != nil {
        return fmt.Errorf("failed to enable RLS: %v", err)
    }

    // Create the RLS policy
    _, err = db.Raw(`
        CREATE POLICY tenant_isolation_policy ON data
        FOR ALL
        USING (tenant_security_policy(tenant_id));
    `).Exec()
    if err != nil {
        return fmt.Errorf("failed to create RLS policy: %v", err)
    }

    return nil
}

// NewTenantMiddleware creates a new middleware instance
func NewTenantMiddleware(db *gorm.DB) *TenantMiddleware {
    return &TenantMiddleware{
        DB: db,
    }
}

// SetTenant sets the current tenant context
func (tm *TenantMiddleware) SetTenant(tenantID uint) error {
    // Set the tenant ID in the PostgreSQL session
    _, err := tm.DB.Raw(
        "SET app.current_tenant_id = ?",
        tenantID,
    ).Exec()

    if err != nil {
        return fmt.Errorf("failed to set tenant context: %v", err)
    }

    tm.CurrentTenantID = tenantID
    return nil
}

// GetDB returns a database connection scoped to the current tenant
func (tm *TenantMiddleware) GetDB() *gorm.DB {
    return tm.DB.Where("tenant_id = ?", tm.CurrentTenantID)
}

// Example usage
func main() {
    // Connect to database
    dsn := "host=localhost user=postgres password=postgres dbname=multitenantdb port=5432 sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect to database")
    }

    // Auto-migrate the schemas
    db.AutoMigrate(&Tenant{}, &User{}, &Data{})

    // Initialize RLS
    if err := InitializeRLS(db); err != nil {
        panic(fmt.Sprintf("failed to initialize RLS: %v", err))
    }

    // Create tenant middleware
    tm := NewTenantMiddleware(db)

    // Example of using the tenant-scoped database
    if err := tm.SetTenant(1); err != nil {
        panic(fmt.Sprintf("failed to set tenant: %v", err))
    }

    // All operations will now be scoped to tenant 1
    tenantDB := tm.GetDB()

    // Create data for tenant 1
    data := Data{
        Content: "Tenant 1's data",
        TenantID: 1,
    }
    tenantDB.Create(&data)

    // Query data (will only return tenant 1's data)
    var results []Data
    tenantDB.Find(&results)
}

Limitations and Considerations

RLS may not be suitable for cases requiring complex cross-tenant operations
Additional care is needed when performing bulk operations
Backup and restore operations need to account for RLS policies