PostgreSQL 16.8 : create schemas to separate/isolate data access

PierreLarondelle

On our ArcGIS Enterprise 11.5, I installed latest PostgreSQL 16.8 as geodatabase (with ST_geometry).

I've set several role/schemas to manage dedicated features datasets and classes (e.g. : Hydrology gets water, rainfalls, etc... ; Mobility gets road signs, one-way roads, etc...).

One common role/schema is called "basepack" and is dedicated to basics assets (datasets and tables) that every users may need, to help brainstorming their projects.
These assets should be added to usual user's projects but only editable by SDE (dedicated to GIS Management).

Summary :

- SDE => usage/manage users, schemas and basepack;

- USUAL USER => manage its own data + usage basepack;

- BASEPACK : as is

I've tried lots of config with security, privileges etc ... but everytime I connect users, they can see all tables from each others with the only limitation to be able to edit or stay read-only.
It's really confusing for the final users to have such a bunch of mixed assets, restricted or not.

It would be way much cleaner to have such a config :

- SDE : See all datasets, feature classes, etc ... (with full edit capabilities)

- USUAL USER : See their assets only (with full edit capabilities) + those of basepack (read only)

- BASEPACK : See it's own assets only (with full edit capabilities)

I would like to have SDE user/schema control over all users/schemas

Long story short : Is it possible and how do I configure it with roles/schemas : sde, mobilite and basepack for basic example ?

Any help would be appreciated.
Have a nice day.

VenkataKondepati

You can get exactly that behavior in PostgreSQL + ArcGIS Enterprise by locking down schema visibility and driving access with group roles (not per-user grants). What’s tripping you up now is that users still have USAGE on other schemas (often via PUBLIC), so they can “see” everyone else’s tables even if they can’t edit them.

Here’s a clean, repeatable setup (example schemas/roles: sde, mobilite, basepack):

1) Core principles

In Postgres, to even see objects in a schema you need USAGE on the schema.
To read/edit a table you need SELECT / INSERT / UPDATE / DELETE on that table (and USAGE on its schema).
By default, PUBLIC may have broad privileges—revoke them first.
Use group roles (e.g., role_mobilite_edit, role_basepack_read) and add users to those roles.

2) Create roles (group roles)

-- group roles
CREATE ROLE role_sde_admin;         -- full control everywhere
CREATE ROLE role_mobilite_edit;     -- mobilite editors
CREATE ROLE role_basepack_read;     -- everyone can read basepack

-- make SDE user the admin (and optionally basepack owner)
GRANT role_sde_admin TO sde;

3) Lock down schema visibility

-- remove overly-broad defaults
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE yourdb FROM PUBLIC;

-- for every “business” schema, start from zero
REVOKE ALL ON SCHEMA mobilite  FROM PUBLIC;
REVOKE ALL ON SCHEMA basepack  FROM PUBLIC;

-- let only the intended roles see schemas
GRANT USAGE ON SCHEMA mobilite TO role_mobilite_edit;
GRANT USAGE ON SCHEMA basepack TO role_basepack_read;
GRANT USAGE ON SCHEMA mobilite, basepack TO role_sde_admin;

4) Object-level privileges (existing objects)

-- mobilite editors: full DML on mobilite objects
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA mobilite TO role_mobilite_edit;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA mobilite TO role_mobilite_edit;

-- basepack is read-only to everyone (except SDE)
GRANT SELECT ON ALL TABLES IN SCHEMA basepack TO role_basepack_read;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA basepack TO role_basepack_read;

-- SDE admin full control everywhere
GRANT SELECT, INSERT, UPDATE, DELETE, TRIGGER, REFERENCES ON ALL TABLES IN SCHEMA mobilite, basepack TO role_sde_admin;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA mobilite, basepack TO role_sde_admin;

5) Default privileges (future objects)

Run these as the schema owners (the user that creates objects in the schema):

-- For future MOBILITE tables/sequences:
ALTER DEFAULT PRIVILEGES IN SCHEMA mobilite
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role_mobilite_edit;
ALTER DEFAULT PRIVILEGES IN SCHEMA mobilite
  GRANT USAGE, SELECT ON SEQUENCES TO role_mobilite_edit;

-- For future BASEPACK tables/sequences:
ALTER DEFAULT PRIVILEGES IN SCHEMA basepack
  GRANT SELECT ON TABLES TO role_basepack_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA basepack
  GRANT USAGE, SELECT ON SEQUENCES TO role_basepack_read;

-- Always grant SDE admin full control on new objects
ALTER DEFAULT PRIVILEGES IN SCHEMA mobilite
  GRANT SELECT, INSERT, UPDATE, DELETE, TRIGGER, REFERENCES ON TABLES TO role_sde_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA basepack
  GRANT SELECT, INSERT, UPDATE, DELETE, TRIGGER, REFERENCES ON TABLES TO role_sde_admin;

6) Add users to roles

-- typical users
GRANT role_mobilite_edit  TO user_alice;  -- sees/edits only mobilite
GRANT role_basepack_read  TO user_alice;  -- reads basepack

-- basepack data steward (if not sde)
-- GRANT role_basepack_owner TO basepack_owner_user; (optional owner role)

7) ArcGIS specifics (Pro/Server)

Don’t use sde as the data owner of business datasets. Let each schema’s owner create their own data; sde holds geodatabase admin and gets control via role_sde_admin.
In ArcGIS Pro, register enterprise data and use Data > Privileges to mirror the grants above if you prefer GUI.
If users still “see” other schemas, double-check:
- No lingering USAGE on those schemas for PUBLIC or their roles.
- Their database connection uses their own login (not shared).
- They’re not members of role_sde_admin or other broad roles.

Your target behavior, mapped

SDE: member of role_sde_admin → sees everything; full control.
USUAL USER: member of role_mobilite_edit and role_basepack_read →
- Sees mobilite (full edit) + basepack (read-only).
- Cannot even list other users’ schemas (no USAGE ⇒ invisible).
BASEPACK steward: either the schema owner or a dedicated owner role; everyone else only has SELECT.

This model stops the “mixed assets” problem in Catalog: users will only see their schema and basepack—nothing else while SDE retains full oversight.

PierreLarondelle

Huge thanks for the follow up and your detailed reply, I'll try this today and give feedback asap !

PierreLarondelle

Hi, tedious work done for our 24 schema, groups, logins.

I can connect my sde user, but none others through ArcGIS Pro 3.5.2.

In pgAdmin4 (v9.7), I can connect mobilite but can't create table (rights denied for mobilte schema) and the same applies to sde

When I try to connect 'mobilite' user in ArcGIS Pro, I can select my sde** database but if I try to validate the connection I get an "Insufficient permissions[]" message, as seen in attachment.

In pgAdmin 4
Connection = maintenance database : sde**; 
             username : 'mobilite'; 
             password : 'the_pass_for_mobilite';
             role : 'role_mobilite_edit'

-- SCHEMA: mobilite
-- DROP SCHEMA IF EXISTS mobilite ;

CREATE SCHEMA IF NOT EXISTS mobilite
AUTHORIZATION mobilite;

GRANT USAGE ON SCHEMA mobilite TO role_mobilite_edit;
GRANT USAGE ON SCHEMA mobilite TO role_sde_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE role_mobilite_edit IN SCHEMA mobilite
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLES TO role_mobilite_edit;

ALTER DEFAULT PRIVILEGES FOR ROLE role_mobilite_edit IN SCHEMA mobilite
GRANT DELETE, INSERT, REFERENCES, SELECT, TRIGGER, UPDATE ON TABLES TO role_sde_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE role_mobilite_edit IN SCHEMA mobilite
GRANT SELECT, USAGE ON SEQUENCES TO role_mobilite_edit;

ALTER DEFAULT PRIVILEGES FOR ROLE role_mobilite_edit IN SCHEMA mobilite
GRANT ALL ON SEQUENCES TO role_sde_admin;

Login

-- Role: mobilite
-- DROP ROLE IF EXISTS mobilite;

CREATE ROLE mobilite WITH
LOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION
NOBYPASSRLS;

GRANT role_mobilite_edit TO mobilite;

Group

-- Role: role_mobilite_edit
-- DROP ROLE IF EXISTS role_mobilite_edit;

CREATE ROLE role_mobilite_edit WITH
NOLOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION
NOBYPASSRLS;

Should I need to setup all things with postgres superuser instead of sde ?

Have a nice day,
Pierre

VenkataKondepati

Pierre — this is almost certainly permissions + “who ran the grants” order, not ArcGIS Pro itself.

What’s going wrong

The DB-level grants aren’t there. If mobilite doesn’t have CONNECT (and usually TEMP) on the database, Pro shows Insufficient permissions[] at connection time.
Default privileges were set for the wrong role.
ALTER DEFAULT PRIVILEGES FOR ROLE role_mobilite_edit … only affects objects created by role_mobilite_edit (a NOLOGIN role), not by the actual owner (mobilite). New tables created by mobilite won’t inherit those grants.
Geodatabase system schema (sde) access is missing. Regular users need at least USAGE on schema sde and SELECT on key SDE tables/functions (ArcGIS touches them on connect and edit).
Schema CREATE/USAGE: even though mobilite owns the schema, being explicit avoids surprises.

Do the following once as postgres (superuser) or the database owner (not sde, unless sde owns the DB).

-- 1) Database-level: let users connect (and create temp objects)
GRANT CONNECT, TEMP ON DATABASE "sde_dbname" TO mobilite;
GRANT CONNECT, TEMP ON DATABASE "sde_dbname" TO role_mobilite_edit;

-- 2) SDE system schema: allow Pro to read geodatabase metadata
GRANT USAGE ON SCHEMA sde TO mobilite, role_mobilite_edit;
GRANT SELECT ON ALL TABLES IN SCHEMA sde TO mobilite, role_mobilite_edit;

-- If you have functions in sde (e.g., ST_Geometry, GDB procs):
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA sde TO mobilite, role_mobilite_edit;

-- 3) Business schema visibility & create
GRANT USAGE, CREATE ON SCHEMA mobilite TO mobilite;          -- explicit
GRANT USAGE            ON SCHEMA mobilite TO role_mobilite_edit;

-- 4) Existing objects in mobilite
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA mobilite TO role_mobilite_edit;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA mobilite TO role_mobilite_edit;

-- 5) Future objects created by *mobilite* (not the group role!)
ALTER DEFAULT PRIVILEGES FOR ROLE mobilite IN SCHEMA mobilite
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO role_mobilite_edit;
ALTER DEFAULT PRIVILEGES FOR ROLE mobilite IN SCHEMA mobilite
  GRANT USAGE, SELECT ON SEQUENCES TO role_mobilite_edit;

-- (Optional) Admin role with full control everywhere
GRANT USAGE ON SCHEMA mobilite TO role_sde_admin;
GRANT SELECT, INSERT, UPDATE, DELETE, TRIGGER, REFERENCES
  ON ALL TABLES IN SCHEMA mobilite TO role_sde_admin;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA mobilite TO role_sde_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE mobilite IN SCHEMA mobilite
  GRANT SELECT, INSERT, UPDATE, DELETE, TRIGGER, REFERENCES ON TABLES TO role_sde_admin;

Also check

Search path for users:
ALTER ROLE mobilite IN DATABASE "sde_dbname" SET search_path = "$user", public, sde;
PUBLIC grants: if you previously REVOKEd from PUBLIC, ensure you explicitly granted what you need (above).
Test in psql/pgAdmin as mobilite:
- \c sde_dbname mobilite
- CREATE TABLE mobilite.t1(id int); (should succeed)
- SELECT count(*) FROM sde.sde_table_registry; (should succeed)

Do I need to run this as postgres?

Yes—database-level grants (CONNECT, TEMP) and blanket grants on sde objects generally require the DB owner or superuser. Run the block as postgres (or the DB owner). After that, Pro 3.5.2 should connect as mobilite without the “Insufficient permissions[]” error, and mobilite will be able to create/edit in its own schema while only seeing what it’s supposed to.

NOTE: I hope you are doing this on a test database and not on production.

PierreLarondelle

Sorry, replied to myself instead of whole thread ... 🙄