I am starting to set up PostgreSQL as a DBMS for ESRI SDE instances. I am looking for recommendations for setting up the roles/schemas for this.

My architecture is:

  • Virtual private server with Windows 2012
  • ESRI's ArcGIS for Server 10.3 installed on VPS
  • ESRI's Web Adapter 10.3 installed on VPS
  • PostgreSQL 9.3 installed on VPS (intranet only)
  • IIS hosting with sites, internet via proxy
  • Web service data, internet via proxy

Ideally, I probably shouldn't have everything together on the same VPS, but I don't get to make those choices. So, I am trying to find a way to make everything work well together as is.

Here is how we want to use PostgreSQL in this scenario although it may not be entirely feasible/optimal.

  • It will hold our internal GIS data for numerous projects. This includes an array of feature classes, tables, and raster data. This data needs to be accessible to GIS personnel and other company users within our intranet. GIS personnel will have editing capability and versioning is desired. Other company personnel would be allow to view and download data, but they are not able to make changes to the hosted copy.
  • It will hold our data, which will be published to map/feature services for outside consumption. In some instances, this will be a lot of the same information in 1. Web services may eventually be editing data, but for the time being it is just displaying it.

I was looking at this answer. It was suggested not to use Feature Datasets to break down the information. My original thought was to create 1 SDE instance and categorize our project data using feature datasets. I thought that would be better than 1 SDE with a data mixed in from a variety of projects.

I thought about creating a new SDE instance for every project where data will be stored as an alternative. Would this eventually cause performance problems with PostgreSQL or ESRI having multiple SDE databases within a DBMS? It would mean different database connections (via Catalog) for each database, but I think this would probably be easier to secure.

If I go with the latter option...here is the workflow I thought might work.

  • I am assuming I can create specific schemas for each of those instances and a role to administer that instance?
  • A database administrator would then be the only one with access to create new datasets in that instance if they were the account authorized for the schema.
  • I would need separate roles for the purposes of editing, consuming, or other restrictive tasks that could be standard user roles for that particular instance.
  • For users that are actually using anything contained within, I would have their user role inherit the pre-established base roles?

I have been looking into setting role memberships. I am looking for examples or suggestions on how to set this up to scale, be restrictive, and be able to easily backup and administer.

Edit... I don't need a lot of information, but just a general direction to topics that I can start researching. The more I read into it, the more it seems like using 1 generic schema and creating base roles and inheriting those via individual user roles for each different purpose is the way to go.

Edit 2

I know this is a rather broad question, but I think even the opinionated answers would benefit the community. This seems more of a general RDBMS DBA problem. ESRI provides a good amount of information on how to create SDE databases, but they seem to fail at providing ideal structures.

So, in accordance with trying to keep this open... My main question would be that given the information provided, what would be more optimal: 1 SDE database with multiple schemas to define permissions by group schemas or multiple SDE databases and permissions granted to individual users.

I have been researching DBA answers to this, and it seems to lean towards multiple databases. When it comes to multiple connections being a problem, each application using the database will only use 1 database. If that changes, then the application will be written in a way to accommodate the slow-down.

closed as too broad by Vince, PolyGeo Dec 21 '15 at 20:12

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    You've got a breadth issue here. I'd cover most of that content on a 3-5 day "jump-start" deployment, but it's a bit much for one GIS SE question. I think "Pick any two" of "good, cheap, fast" fame would also apply to "scale, restrictive, and easy to administer". – Vince Dec 21 '15 at 17:54
  • I don't particularly need details but a general idea that I can run with. I am leaning more towards establishing roles and then inheriting those for individual users, which I could script together for each new SDE. – Branco Dec 21 '15 at 17:59
  • 2
    IIRC the propose of feature datasets isn't to categorize data, it is to facilitate other functionality such as tolopology or networks so I wouldn't go this route. I also think multiple databases is not necessary for the requirements you list (plus it adds overhead). I would use a single database and multiple schemas if you want to keep data separated out. Even this isn't strictly necessary. If you have a set of clearly defined roles you can easily control who can see what. – Dowlers Dec 21 '15 at 18:52
  • 1
    That's how I've approached what I think your setup is in the past, I think you are going to get hit with high RAM usage. Map services take up a lot of RAM and many database connections take up a lot of RAM. A single database will keep database overhead down. – Dowlers Dec 21 '15 at 19:29
  • 1
    To prevent this question being too broad, my advice would be to write most of it as background information, and then ask a single focussed question (i.e. one question mark in the whole post). As always, by doing this you may well gain some of the additional information that you seek as a by product. – PolyGeo Dec 21 '15 at 23:00