Testing Row-Level Security (RLS) Policies with Atlas

Question

How to test PostgreSQL Row-Level Security (RLS) policies using Atlas.

Answer

Users can use Atlas schema testing to check the configuration of their RLS policies and verify that they are applied and enforced correctly.

For example, suppose our application schema includes a table called assets with a tenant_name column and the following RLS policy:

ALTER TABLE "assets" ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON "assets"
  USING ("tenant_name" = current_setting('app.current_tenant', false))
  WITH CHECK ("tenant_name" = current_setting('app.current_tenant', false));

We want to ensure that only rows matching the current tenant name are accessible, and that the application passes the correct tenant name to the database on each query.

Let's go through the steps to define and test the RLS policies.

We defined the RLS policy using SQL syntax, so if our schema is defined externally (not in HCL or SQL), for example, using an ORM that Atlas reads the schema from, we can use the composite_schema data source to combine the ORM schema with the SQL snippet defined above:

data "external_schema" "sqlalchemy" {
  program = [
    "atlas-provider-sqlalchemy",
    "--path", "./models",
    "--dialect", "postgresql"
  ]
}

data "composite_schema" "full_schema" {
  schema "public" {
    url = data.external_schema.sqlalchemy.url
  }
  schema "public" {
    url = "file://schemasql"
  }
}

After defining the schema, we can write a test case to verify that the RLS policies are configured correctly. Since superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table (which is not the behavior we want to test), we'll create a non-privileged user to properly verify the tenancy logic.

The exec and cleanup blocks will be used to set up and tear down the test environment.

Additionally, since we want our program code to test its logic alongside the RLS policies, we'll use the external block to execute the test program, which will connect to the database using the non-privileged user and validate its interaction with the assets table.

schema.test.hcl

variable "working_dir" {
  type = string
}

test "schema" "tenant_isolation" {
  # Create a non-privileged user for testing.
  exec {
    sql = <<-SQL
      CREATE ROLE testing_user LOGIN PASSWORD 'pass';
      GRANT USAGE ON SCHEMA public TO testing_user;
      GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO testing_user;
    SQL
  }
  # Drop the user.
  cleanup {
    sql = <<-SQL
      DROP OWNED BY testing_user;
      DROP ROLE testing_user;
    SQL
  }
  # Test the RLS policy using Go (can be any language).
  external {
    program = [
      "go", "test",
      "-run", "Test_TenantIsolation",
      "--dev-url", urluserinfo(self.dev_url, "testing_user", "pass"),
    ]
    working_dir = var.working_dir
  }
}

The test program will connect to the database using the provided --dev-url flag, which includes the username and password of the non-privileged user created during the test.

tenant_test.go

var dev = flag.String("dev-url", "", "URL to the dev-database used by the tests")

func TestMain(m *testing.M) {
	flag.Parse()
	os.Exit(m.Run())
}

func Test_TenantIsolation(t *testing.T) {
	if dev == nil || *dev == "" {
		t.Skip("no dev-url was set, run 'atlas schema test --env testing' instead")
	}
	c, err := ent.Open(dialect.Postgres, *dev)
	require.NoError(t, err)
	defer c.Close()
	// ...
	// Test logic goes here.
	// ...
}

Then, we can run atlas schema test --env local to verify everything works as expected:

-- PASS: tenant_isolation (2.27s)
PASS

The test begins by applying our schema on a temporary database, setting up the testing_user, and running a test program that validates the application logic against the RLS policies. This ensures full coverage of the RLS lifecycle, from schema definition to application enforcement.

For more examples, check out the schema testing documentation.