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.
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.