Implement PostgreSQL RLS Support

Alex Johnson

-Nov 13, 2025

Implementing PostgreSQL Row-Level Security (RLS) with Atlas

PostgreSQL Row-Level Security (RLS) is a powerful feature that allows you to control data access at the row level. This means you can define policies that determine which users or roles can see, insert, update, or delete specific rows in a table. This is incredibly useful for implementing complex access control models, multi-tenancy, and data privacy requirements. This article provides a comprehensive guide to implementing RLS using Atlas, a schema-as-code tool, which streamlines the management of RLS policies.

Background and Atlas's Approach

Atlas Pro already supports PostgreSQL RLS, and the goal here is to bring this functionality to the open-source version. The core idea is to treat RLS policies as code, which offers numerous advantages: version control, easy change tracking, and the ability to consistently apply policies across different environments (development, staging, production). This approach simplifies the management of complex access rules and ensures that your data security policies are well-documented and reproducible.

Understanding Row-Level Security

Row-Level Security in PostgreSQL, at its heart, is about fine-grained control over data access. Instead of granting broad permissions on entire tables, RLS lets you specify precisely which rows a user can interact with based on their role, the current time, or any other criteria you define. This is achieved through policies that are attached to specific tables.

Key Components and Implementation

To implement RLS effectively with Atlas, several components are critical:

Schema Definition (HCL): Atlas uses its own HCL (HashiCorp Configuration Language) to define RLS policies. This declarative approach makes it easier to understand and manage policies compared to raw SQL.
- Policy Blocks: Policies are defined using a policy block within your Atlas schema files.
```
policy "employee_policy" {
  on    = table.employees
  for   = SELECT
  to    = [PUBLIC]
  using = "(current_user = employee_role)"
}

table "employees" {
  schema = schema.public
  row_security {
    enabled  = true
    enforced = false  # optional
  }
  # ... columns
}
```
- Policy Attributes:
  - on: Specifies the table to which the policy applies.
  - for: Defines the command type (SELECT, INSERT, UPDATE, DELETE, or ALL).
  - to: Specifies the roles the policy applies to.
  - using: An expression that determines which rows are accessible for SELECT/DELETE operations.
  - check: An expression that determines which rows are allowed for INSERT/UPDATE operations (optional).
  - as: Policy mode (PERMISSIVE or RESTRICTIVE – optional).
- Table Attributes:
  - row_security.enabled: Activates RLS for the table.
  - row_security.enforced: Forces RLS even for the table owner (optional).
Architecture Components:
- Schema Layer: This layer is responsible for parsing and representing the RLS policies in Atlas's internal data structures. It includes data structures to represent policies and conversion functions to translate the HCL definitions into schema objects. Specifically, the policy struct within the sql/postgres/ directory is crucial for this. It defines the attributes of an RLS policy. Functions like convertPolicies() and convertTableAttrs() handle the conversion of policy specifications into schema objects. However, in the open-source version, these are currently either unimplemented or return errors.
- HCL Codec Layer: This layer handles the parsing of HCL code to generate the internal schema representation, which will include the policies. The doc struct within the sql/postgres/ directory incorporates the policies. The Eval() function plays a key role, translating HCL code into the schema representation. It also uses functions like convertPolicies() during the evaluation of the HCL file.
Database Operations:
- Inspection: The atlas schema inspect command needs to query the PostgreSQL system catalogs (pg_policies, pg_class) to discover the existing RLS policies in the database.
- Diff Generation: The atlas schema diff command is essential to detect changes in the RLS policies. This involves comparing the current policies in the database with the desired state defined in the Atlas schema files.
- Migration Application: The atlas schema apply command (or atlas migrate apply) generates the necessary SQL to create, alter, or drop RLS policies, as needed. This includes statements like ALTER TABLE ... ENABLE ROW LEVEL SECURITY, CREATE POLICY, ALTER POLICY, and DROP POLICY.

Integration and Advanced Features

ORM Integration: Atlas Pro has guides that show how to integrate RLS policies with common ORMs (Object-Relational Mappers), such as GORM, Ent, SQLAlchemy, Prisma, and Sequelize. This simplifies the process of applying RLS to your data models.
Testing Support: Atlas provides schema testing capabilities to validate your RLS configuration, verify enforcement with different user roles, and test tenant isolation scenarios.
Computed Policies: Atlas supports computed policies using for_each which allows you to apply the same policy across multiple tables, especially useful in multi-tenant environments.

Implementation Tasks and Phases

The implementation process is broken down into several phases:

Phase 1: Core Support: Adding Policy and RowSecurity types, implementing RLS attribute parsing, defining HCL schema definitions, and implementing the necessary conversion functions (convertPolicies(), convertTableAttrs()).
Phase 2: Inspection: Querying the PostgreSQL system catalogs to inspect existing RLS policies and map them to schema objects.
Phase 3: Diff & Migration: Implementing policy diffing logic to detect changes, generating the appropriate SQL statements for migration, and adding migration tests.
Phase 4: Advanced Features: Supporting PERMISSIVE and RESTRICTIVE policies, handling policy modifications, validating policy expressions, and handling role references.
Phase 5: Documentation & Testing: Adding comprehensive documentation, integration tests, example schemas, and a migration guide.

PostgreSQL Version Support and Considerations

Minimum PostgreSQL Version: PostgreSQL 9.5+ (because RLS was introduced in this version).
Recommended PostgreSQL Version: PostgreSQL 15+ (because of NULLS DISTINCT support).

Additional Considerations: Security (validating SQL injection), performance impact, and error handling are crucial considerations during implementation.

Addressing Open Questions

Policy Inheritance/Templates: Should we provide the support to inherit/template policies?
Circular Dependencies: How do we manage the circular dependency between policies and roles?
Policy Expression Validation: Should the policy expressions be validated against table structures?
WITH CHECK: Should WITH CHECK be supported during migration?
Policy Conflicts: How do we handle conflicts when there are multiple policies?

By following these steps, you can effectively implement and manage PostgreSQL Row-Level Security using Atlas, resulting in robust, well-documented, and secure data access control.

For more in-depth information on related topics, you can check out the official PostgreSQL documentation on Row Security Policies.