Supabase•7d ago

Using RLS for CLS

Hi, I wanted to know if it is acceptable to use RLS for CLS. Basically, I create an UPDATE policy that makes sure other columns remain the same as old record except only allowing 2 certain columns to be different. In effect, I am enforcing column level security. I do understand the fact that i will have to fetch the row each time I want to update. But this trade-off should be acceptable right?

10 Replies

silentworks•7d ago

Yes you can and you can also use a trigger function for that too.

garyaustin•7d ago

Trigger function is what I use.

Napolean_SoloOP•7d ago

alright thanks is this fine?

-- One time:
CREATE EXTENSION IF NOT EXISTS hstore;

-- Generic trigger function.
-- Pass a comma-separated list of columns that ARE allowed to change.
CREATE OR REPLACE FUNCTION enforce_mutable_only(mutable_cols text)
RETURNS trigger
LANGUAGE plpgsql AS
$$
DECLARE
  allow text[] := string_to_array(mutable_cols, ',');
BEGIN
  -- Compare OLD vs NEW after removing allowed columns from both sides.
  IF (hstore(NEW) - allow) <> (hstore(OLD) - allow) THEN
    RAISE EXCEPTION
      'Immutable columns changed on %. Blocked by trigger.',
      TG_TABLE_NAME
      USING ERRCODE = 'insufficient_privilege';
  END IF;

  RETURN NEW;
END;
$$;

-- Example: only `description`, `metadata`, and `updated_at` may change.
CREATE TRIGGER user_tools_immutable_cols
BEFORE UPDATE ON user_tools
FOR EACH ROW
EXECUTE FUNCTION enforce_mutable_only('description,metadata,updated_at');

-- One time:
CREATE EXTENSION IF NOT EXISTS hstore;

-- Generic trigger function.
-- Pass a comma-separated list of columns that ARE allowed to change.
CREATE OR REPLACE FUNCTION enforce_mutable_only(mutable_cols text)
RETURNS trigger
LANGUAGE plpgsql AS
$$
DECLARE
  allow text[] := string_to_array(mutable_cols, ',');
BEGIN
  -- Compare OLD vs NEW after removing allowed columns from both sides.
  IF (hstore(NEW) - allow) <> (hstore(OLD) - allow) THEN
    RAISE EXCEPTION
      'Immutable columns changed on %. Blocked by trigger.',
      TG_TABLE_NAME
      USING ERRCODE = 'insufficient_privilege';
  END IF;

  RETURN NEW;
END;
$$;

-- Example: only `description`, `metadata`, and `updated_at` may change.
CREATE TRIGGER user_tools_immutable_cols
BEFORE UPDATE ON user_tools
FOR EACH ROW
EXECUTE FUNCTION enforce_mutable_only('description,metadata,updated_at');

silentworks•7d ago

You just need to set existing columns to the old value and allow the new columns to be updated. This looks like you are raising an exception which will stop all execution henceforth.

Napolean_SoloOP•7d ago

can you elaborate? I am not the DB guy tbh what's wrong with that script? I shouldn't raise an exception if the values differ you mean?

-- One-time
CREATE EXTENSION IF NOT EXISTS hstore;

-- Trigger function
CREATE OR REPLACE FUNCTION enforce_mutable_allowlist(mutable_cols text)
RETURNS trigger
LANGUAGE plpgsql AS
$$
DECLARE
  allow text[] := string_to_array(mutable_cols, ',');
  new_h hstore;
  old_h hstore;
  k text;
BEGIN
  -- Convert rows to hstore for key-by-key manipulation
  new_h := hstore(NEW);
  old_h := hstore(OLD);

  -- For every key not in the allow-list, copy the OLD value back into NEW
  FOR k IN SELECT key FROM each(new_h) LOOP
    IF NOT (k = ANY (allow)) THEN
      NEW := (NEW #= hstore(k, old_h -> k));  -- set NEW.k = OLD.k
    END IF;
  END LOOP;

  RETURN NEW;
END;
$$;

-- Example: only these may change
CREATE TRIGGER user_tools_mutable_columns
BEFORE UPDATE ON user_tools
FOR EACH ROW
EXECUTE FUNCTION enforce_mutable_allowlist('description,metadata,updated_at');

-- One-time
CREATE EXTENSION IF NOT EXISTS hstore;

-- Trigger function
CREATE OR REPLACE FUNCTION enforce_mutable_allowlist(mutable_cols text)
RETURNS trigger
LANGUAGE plpgsql AS
$$
DECLARE
  allow text[] := string_to_array(mutable_cols, ',');
  new_h hstore;
  old_h hstore;
  k text;
BEGIN
  -- Convert rows to hstore for key-by-key manipulation
  new_h := hstore(NEW);
  old_h := hstore(OLD);

  -- For every key not in the allow-list, copy the OLD value back into NEW
  FOR k IN SELECT key FROM each(new_h) LOOP
    IF NOT (k = ANY (allow)) THEN
      NEW := (NEW #= hstore(k, old_h -> k));  -- set NEW.k = OLD.k
    END IF;
  END LOOP;

  RETURN NEW;
END;
$$;

-- Example: only these may change
CREATE TRIGGER user_tools_mutable_columns
BEFORE UPDATE ON user_tools
FOR EACH ROW
EXECUTE FUNCTION enforce_mutable_allowlist('description,metadata,updated_at');

this is fine?

silentworks•7d ago

https://stackoverflow.com/a/56083750/811799

Stack Overflow

PostgreSQL trigger to avoid update in a column

I have a PostgreSQL database with a table called product. In this table I have an autoincrement column named auxId (not the table's primary key). I want to avoid any update on this column. How can ...

silentworks•7d ago

Do note that using a trigger function will block updating on that table for these columns no matter what method you use to update. So if you try and update these columns with the dashboard they won't update, if you try to do so with the service_role key they won't update and even with SQL they won't update. So you should really think if this is the desired outcome you are after as we've only shared a solution based on the limited information you shared here.

Napolean_SoloOP•7d ago

ah, i think it's better to stick to RLS as it seems better to me

silentworks•7d ago

RLS will do the same as your raising of an exception in the trigger function with the caveat that you can bypass it via the dashboard or using the service_role key. But as I stated above you gave a limited amount of information so the answers we provide are based on that. There are ways to disable triggers temporarily.

Napolean_SoloOP•7d ago

okay got it!

Gaming

Programming

Using RLS for CLS

Did you find this page helpful?