金仓 KingbaseES 多 GIS 地理数据库部署及用户隔离实施方案

金仓KingbaseES PG 模式下，一个实例下创建多个用户和多个库，用户之间需要进行隔离，不能访问其他库，且能正常使用GIS功能

1、创建用户和库，用户名和库名保持一致，回收public 权限，重新赋予connect和其他权限，实现用户隔离

#create_user_db.sh #!/bin/bash # 用户和密码数组 USERS=( "dychy:L#tBcW8z5q" "dechy:Mk@FgH7n3j" "dschy:S_4dUoR92v" ) echo "开始创建用户和数据库..." for USER_INFO in "${USERS[@]}"; do # 分割用户名和密码 IFS=':' read -r USERNAME PASSWORD <<< "$USER_INFO" echo "处理用户: $USERNAME" # 创建用户 ksql test system -c "CREATE USER \"$USERNAME\" WITH PASSWORD '$PASSWORD';" # 创建数据库（需要单独执行） ksql test system -c "CREATE DATABASE \"$USERNAME\" OWNER \"$USERNAME\";" # 设置数据库权限 ksql "$USERNAME" "$USERNAME" <<EOF REVOKE CONNECT ON DATABASE "$USERNAME" FROM PUBLIC; GRANT CONNECT ON DATABASE "$USERNAME" TO "$USERNAME"; GRANT ALL PRIVILEGES ON SCHEMA public TO "$USERNAME"; EOF echo "完成: $USERNAME" echo "---" done echo "所有用户和数据库创建完成！" -- 删除数据库 --DROP DATABASE IF EXISTS dychy; --DROP DATABASE IF EXISTS dechy; --DROP DATABASE IF EXISTS dschy; --删除用户（PostgreSQL/金仓不支持 CASCADE 选项） --DROP USER IF EXISTS dychy; --DROP USER IF EXISTS dechy; --DROP USER IF EXISTS dschy; --创建GIS需要的用户和库 ksql test system create user sde superuser; create schema sde; alter schema sde owner to sde; alter user sde password '123456'; create user rds_superuser superuser ; alter user rds_superuser password '123456'; create database postgres;

2、对应库下创建模式

ksql dychy dychy -c 'create schema dychy' ksql dechy dechy -c 'create schema dechy' ksql dschy dschy -c 'create schema dschy'

3、对应库创建GIS插件

\c dychy system create extension postgis; create extension postgis_raster; create extension postgis_sfcgal; create extension fuzzystrmatch; create extension postgis_tiger_geocoder; set exclude_reserved_words = 'level'; create extension postgis_topology; create extension address_standardizer; --修改参数 alter system set allow_system_table_dml = on; select pg_reload_conf(); select oid, proname, proargtypes, proisstrict from pg_proc where proname ilike '%addgeo%'; update pg_proc set proisstrict = false where oid = 16705; \c dechy system create extension postgis; create extension postgis_raster; create extension postgis_sfcgal; create extension fuzzystrmatch; create extension postgis_tiger_geocoder; set exclude_reserved_words = 'level'; create extension postgis_topology; create extension address_standardizer; create extension address_standardizer_data_us; --修改参数 alter system set allow_system_table_dml = on; select pg_reload_conf(); select oid, proname, proargtypes, proisstrict from pg_proc where proname ilike '%addgeo%'; update pg_proc set proisstrict = false where oid = 16705; \c dschy system create extension postgis; create extension postgis_raster; create extension postgis_sfcgal; create extension fuzzystrmatch; create extension postgis_tiger_geocoder; set exclude_reserved_words = 'level'; create extension postgis_topology; create extension address_standardizer; create extension address_standardizer_data_us; --修改参数 alter system set allow_system_table_dml = on; select pg_reload_conf(); select oid, proname, proargtypes, proisstrict from pg_proc where proname ilike '%addgeo%'; update pg_proc set proisstrict = false where oid = 16705;

4、arcMap 软件启用地理数据库 （需要有授权文件）
使用sde 登录进行启用地理数据库

5、修改search_path，启用后使用普通用户登录测试创建要素，要素数据集

ksql dychy system -c 'alter database dychy set search_path =dychy,"$user", public' ksql dechy system -c 'alter database dechy set search_path =dechy,"$user", public' ksql dschy system -c 'alter database dschy set search_path =dschy,"$user", public' ksql test system -c 'select pg_reload_conf()'