Row level security policies

[fenos]

Hello!

I would love to see RLS added to Atlas!
I currently make a lot of use of them, especially when using Supabase!

The proposal would be to have the following spec:

table "example" {

	column "user_id" {
		type = uuid
	}

       row_level_security = true

	policy "admin" {
		select {
			using = "auth.uid() = user_id"
		}
		delete {
			using = "auth.uid() = user_id"
		}
		update {
			check = "auth.uid() = user_id"
			using = "auth.uid() = user_id"
			to = "authentication" // specify schema or default to table schema
		}
		insert {
			check = "auth.uid() == user_id"
		}
	}
}

• row_level_security (bool) would allow us to enable / disable RLS on the table
• policy would be an object that represents the policy rules. There can be many policies on a table

the name of the policy for each operation would be {table_name}_{policyname}_operation for instance, given the example above atals should migrate:

ALTER TABLE example ENABLE ROW LEVEL SECURITY;

CREATE POLICY example_admin_select ON example
    FOR SELECT
    USING (auth.uid() = user_id);

CREATE POLICY example_admin_delete ON example
    FOR DELETE
    USING (auth.uid() = user_id);

CREATE POLICY example_admin_update ON example
    FOR UPDATE
    WITH CHECK ( auth.uid() = user_id )
    USING (auth.uid() = user_id);

CREATE POLICY example_admin_insert ON example
    FOR INSERT
    WITH CHECK ( auth.uid() = user_id )

Any thoughts?

[rotemtam]

Hey @fenos
Thanks for the thorough suggestion. RLS (and more Postgres capabilities) are definitely on our mind. We are still deliberating what’s the best way to extend the drivers in a modular way. Keeping this issue open for tracking.

[unaimillan]

Any updates on this feature?

[unaimillan]

Probably it's worth mentioning @supabase here and try to draw attention of anyone interested in this feature and possible integration 🤔

[kyong0612]

@rotemtam
We are eagerly awaiting to migrate RLS 🙏

[mkarbo]

We're also missing this, keeping us from migrating to atlas

[net]

I would prefer a format like this which more accurately represents how policies are implemented in Postgres:

table "users" {
  ...
  policy "user_selects_users" {
    for = [SELECT]
    role = "user"
    using = "id = current_user_id()"
  }
}

[kav]

One further possible tweak here is it might be worth looking at the pattern for triggers as an excellent template. It's fairly frequently the case that you have a relatively consistent RLS policy across tables so being able to do

policy "user_selects_users" {
  for_each = [table.public.products, table.public.customers, table.public.projects]
  on = each.value
  for = [SELECT]
  role = "user"
  using = "user_id = current_user_id()"
}

would be really nice.

[meringu]

From the RLS docs:

Each policy has a name and multiple policies can be defined for a table. As policies are table-specific, each policy for a table must have a unique name. Different tables may have policies with the same name.

Not enforcing a naming convention here would be great for supporting existing databases with RLS.

[a8m]

Hey! This was added in the latest release and should be officially out later today/tomorrow.

Please, see: https://atlasgo.io/atlas-schema/hcl#row-level-security-policy and https://atlasgo.io/atlas-schema/hcl#row-level-security for examples.