(Postgis) Permission based on Geometry

Question

Is it possible to grant permissions to users valid only for a certain geometry on PostGIS? For example I have a database of many tables for whole USA and I want to limit a user to view and update a specific state. Is there a way -without adding state column- to achieve this? If not which setup (ie. middleware) do you suggest?

Answer 1

You can use Row Level Security:

ALTER TABLE mygeoms ENABLE ROW LEVEL SECURITY;
ALTER TABLE mygeoms FORCE ROW LEVEL SECURITY;

CREATE POLICY only_in_area ON mygeoms
   FOR ALL TO some_role
   USING (to_contains(GEOMETRY 'SRID=4326;POLYGON((...)), geom_col);

Then some_role can only be able to see and modify rows that satisfy the condition.

A GiST index on the geometry column can speed up processing.