(Postgis)基于几何体的权限

-- 创建角色和用户
CREATE ROLE admin;
CREATE ROLE user1;
CREATE ROLE user2;

CREATE USER admin_user WITH PASSWORD 'admin_password';
CREATE USER user1_user WITH PASSWORD 'user1_password';
CREATE USER user2_user WITH PASSWORD 'user2_password';

-- 分配角色
GRANT admin TO admin_user;
GRANT user1 TO user1_user;
GRANT user2 TO user2_user;

-- 定义几何体
CREATE TABLE spatial_data (
    id SERIAL PRIMARY KEY,
    geom GEOMETRY(Point, 4326),
    name VARCHAR(100)
);

-- 插入示例数据
INSERT INTO spatial_data (geom, name) VALUES (ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326), 'San Francisco');
INSERT INTO spatial_data (geom, name) VALUES (ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326), 'New York');

-- 设置基于几何体的权限
GRANT SELECT ON spatial_data TO user1;
GRANT SELECT, UPDATE ON spatial_data WHERE ST_Within(geom, ST_SetSRID(ST_MakePolygon(ST_MakePoint(-122.4194, 37.7749), ST_MakePoint(-122.4194, 37.7849), ST_MakePoint(-122.4094, 37.7849), ST_MakePoint(-122.4094, 37.7749)), 4326)) TO user2;