topic Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11 in Data Management Questions

Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

Anonymous User — Tue, 22 Mar 2011 16:46:11 GMT

Original User: MacJeremy

Heya,

having some troubles with a setup. I have an SDE server (9.3.1) up and running on a Linux 32bit machine. My PostgreSQL (8.3.11) database server is running on another Linux machine (64bit). I have already 2 different applications with two different sde services and databases running. Everything works perfectly. Now I wanted to do some housekeeping in regard to the databases. I wanted to start using one database with one sde service and many user schemas through SDE. Why do i want to do that? Because of security, maintanance and many other reasons. And it was stated somewhere that this is the preferred way for SDE and ORACLE. I found a howto somewhere on the support site of ESRI. On further reading I also found how to do this with POSTGRESQL, not through the instance but through the user options on sdesetup, but not fully explained. So here are the steps I took that took me the furthest:

In PostgreSQL CLI (creating databases which are functioning and creating the schemas are very similar. If you need the steps I created the database with, in which I then created the schema, I'll post them here):

CREATE ROLE <schema_name> LOGIN PASSWORD '<pass>' SUPERUSER NOINHERIT CREATEDB;
CREATE SCHEMA <schema_name> AUTHORIZATION <schema_name>;
GRANT ALL ON SCHEMA <schema_name> to <schema_name>;
GRANT ALL ON SCHEMA <schema_name> TO sde;
GRANT USAGE ON SCHEMA <schema_name> TO public;

sdesetup -o install -d POSTGRESQL -D <main_database_where_the_schema_resides> -u <schema_name>

But unfortunatly halfway through the setup it breaks. After that when I restart the above sdesetup statement it breaks again but at a later point in the install, and so on and so on. I recon that I could get to succesfully installing the geodatabase if I would repeat the sdesetup call numerous times, but ... I would prefer a method that would get me there without errors

Here is a little tryout. I created a database "gismain", created the sde schema, and ran sdesetup. This I can start through the SDE service and connect with ArcCatalog. No problem there. Now the SDE service of gismain is stopped (tried it with on, but there I had more difficulties than with the services off), and used the steps of creating the schema above for a schema called "gp".

But sdesetup breaks with:
***********************************************************************************
sdesetup -o install -d POSTGRESQL -D gismain -u gp
Enter DBA password:

ESRI ArcSDE Server Setup Utility Tue Mar 22 18:33:45 2011
----------------------------------------------------------------
Install or update ST_GEOMETRY, ArcSDE, GDB schema objects:
Are you sure? (Y/N): y
Creating ST_GEOMETRY and ArcSde schema.....
Successfully created ST_GEOMETRY and ArcSde schema.

Installing locators.....
Successfully installed locators.

Creating geodatabase schema.....
Error: DBMS object exists (-452).
Error: Error installing GDB schema.
Error: DBMS object exists (-452).
Error: Geodatabase schema object install not completed.
Check SDEHOME\etc\sde_setup.log or
SDEHOME\etc\sde_dc<rdbms>.log for more details..
********************************************************************************

Whole logfiles of sdesetup log and postgres log are attached, where you can see that sdesetup is writing to the gp schema. It created four tables, and if I would restart sdesetup it would create another 3 or 4 tables. I tried many approches to solving this, checked role privileges and many other stuff, but no avail.

Help is needed and very much appreciated.

THX, in advance....

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

Anonymous User — Thu, 11 Aug 2011 13:20:56 GMT

Original User: MacJeremy

Any help ... anyone ... maybe ... please ?

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

tKasiaTuszynska — Fri, 12 Aug 2011 17:32:30 GMT

MacJeremy,
It looks like you are tying to run sdesetup against a non sde schema.
If that is the case, it is not supported.
The sde repository tables have to be owned by the sde user. In ArcSDE the schema name of the owner has to match the name of the owner. So your cmd should look like this:
sdesetup -o install -d POSTGRESQL -D gismain -u sde

Sincerely,
Kasia

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

Anonymous User — Wed, 24 Aug 2011 05:55:10 GMT

Original User: MacJeremy

MacJeremy,
It looks like you are tying to run sdesetup against a non sde schema.
If that is the case, it is not supported.
The sde repository tables have to be owned by the sde user. In ArcSDE the schema name of the owner has to match the name of the owner. So your cmd should look like this:
sdesetup -o install -d POSTGRESQL -D gismain -u sde

Sincerely,
Kasia

Thx for the reply.

But this means that the Schema can only be SDE.
So this ---> http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/using_-2001045921.htm (Multiple geodatabases in one Oracle database) is not possible in PostgreSQL?

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

VinceAngelo — Wed, 24 Aug 2011 09:34:37 GMT

Oracle is the only ArcSDE-supported RDBMS that doesn't use a "multiple databases per server instance"
model, so storing multiple ArcSDE instances required multiple Oracle instances. User-schema geodatabases
are an attempt to reduce the cost of isolated ArcSDE instances on Oracle (though they're not really all that
isolated). Using PostgreSQL, you have the option of multiple databases, which are much more useful (and
truly independent) -- There's no reason to use the kludge when you have the real thing available.

- V

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

Anonymous User — Mon, 29 Aug 2011 16:18:29 GMT

Original User: kasi4188

Hi MacJeremy,
Yes, you are correct, ArcSDE for PostgreSQL does not support the multiple gdb model on the schema level, for the reason that Vince provided, it is not necessary. You can have one instance of PostgreSQL, with multiple databases which can become geodatabases, in each of those databases the sde repository tables will have to reside in the sde schema.

Sincerely,
Kasia

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

DanielMrvos — Thu, 13 Oct 2011 15:29:38 GMT

Thanks Vince and Kasia.

Just saw that ArcSDE 10 can use multiple users and schemas.
http://help.arcgis.com/en/geodatabase/10.0/install_guides/arcsde_for_postgresql_installation_and_upgrade_guide/00q3/00q30000002p000000.htm

Though a confirmation would be nice before I turn to 10.

The reason why I need multiple users and not just one, the sde user, is that I have multiple users editing different geodatabases. Until now I had to start several sde services to not allow that user1 can edit data on user2 geodb, and that only if I don't tell them the ports that the other sde services are running. And installing a new service is a great hassle, with /etc/services, dbinit.sde and such. This is like 1 hour for one service instance. Yes I can have multiple databases through one sde service, but they are all owned by the role "sde". And that is something that I want to avoid. I need multiple databases with different roles for sec. reasons.

I want to have one sde service through which users can connect with arccatalog but only to edit their db, and not the ones that they are not supposed to. And that can only be done if I have one service, through which I can connect to diff databases with different user names (roles).

Thx again,
D

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

Anonymous User — Thu, 13 Oct 2011 15:58:38 GMT

Original User: vangelo

You're intermingling several different definitions of "schema". There is no requirement for all
users to use the 'sde' login (and, in fact, doing so is a violation of best practice -- no SDE user
should *ever* own spatial data). All versions of ArcSDE have always (well, since SDE 2.0 was
released) supported using multiple users with different accesses within a database. There
is no need to move to 10.0 to gain this functionality (though you should move to 10 soon for
access to newer PostgreSQL builds).

- V

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

tKasiaTuszynska — Fri, 14 Oct 2011 17:07:35 GMT

Daniel,
It sounds like you are trying to improvise your own versioning environment. Have you looked at the geodatabase versioning environment?
you can set up versions, through which the data can be "seen" and edited, which will have the effect of isolating the changes made by editors to that specific version and than you can use visual tools to merge the changes from multiple editors (editing through multiple versions).

Esri has several courses on the topic and plenty of doc.

Sincerely,
Kasia

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

Anonymous User — Sun, 16 Oct 2011 09:34:00 GMT

Original User: MacJeremy

...and, in fact, doing so is a violation of best practice -- no SDE user
should *ever* own spatial data).
- V

Then I'm missing something. Because that is what I want but can't get. When I'm using sdeinstall I can only use schema sde and user sde, because the username is the same as schema, and I can't use any other username, because I get the error that I stated in my earlier posts. And when I use sdeinstall, and I have to if I want to use sde as my database connection and not use Direct Connect, then the only user with whom I, or any other arcgisdesktop user, can connect through sde to the database is the sde user. And as I can recall when using sdemon, the username and the schema in the database have to have the same name so creating a different user than the schema doesn't work, and if I try to create a different schema/user pair then sdeinstall has problems. But since I changed my gisserver to 10, a few days ago, I will also change sde to 10 and then will try if I can get the user seperations and will get back to you.

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

VinceAngelo — Sun, 16 Oct 2011 19:00:23 GMT

The ArcSDE software is installed (via 'sdesetup') as the SDE login/schema (only). This creates
metadata tables and installs the SDE.ST_GEOMETRY type.

All further data creation should be done as a user other than SDE. Create a login, create a schema
(with the same name as the login), grant the permissions, then create and populate tables.

- V

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

Anonymous User — Mon, 17 Oct 2011 16:21:15 GMT

Original User: kasi4188

Daniel,
To expand on Vince's post:
sde user/schema in the database are used for the sde repository tables, the 40 tables and 300 some stored procedures that are placed in the db to make it spatial. That is all that the sde user should be used for.
Any loading of spatial data, viewing or editing should be done as users that you the admin create explicit in the RDBMS instance. You create the login, the schema in the db and add privileges to that user depending on what you need them to do.
An sde installation is not going to create other users besides the sde superuser, that is part of database administration and is left up to each individual dba to perform.
The sde for postgres installation comes with a tools folder, in that folder is a sample sql script, that gives you examples on how to create various users and the privileges they may need.
Sincerely,
Kasia

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

DanielMrvos — Fri, 21 Oct 2011 15:15:20 GMT

Just wanted to thank you 🙂

I have used the wrong methodology of creating geo-dbs. Though one thing is still bothering me. Now I have one sde service, the spatial data is not in the sde-schema anymore but by themselfs in their own schemas and as their own users, but still through arccatalog, connecting as a user you can see the feature datasets of the other users. You can't see whats inside or change them, but you can see them. i.e. if I have a schema test1 and user test1, where let's say I have a feature dataset "streets" and let's say I have schema test2 with user test2 with a feature dataset "rivers", if I connect, through arccatalog, as user test1 to the database test where the schemas test1 and test2 are, test1 can see the feature dataset of test2. I tried to revoke priviliges of the users respectively to their schemas but that didn't work. Any ideas about that?

Thanks again....
D.

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

Anonymous User — Fri, 21 Oct 2011 15:27:54 GMT

Original User: kasi4188

Daniel,
The issue with Datasets being visible and what they contain not, if you do not have read privileges on the data is a known issue, that has been around for a while.
As far as I know there is not much you can do about that.
The reason for the behavior is that a dataset is a visual representation of the relationship between the feature classes, it is not a db object in it's self, therefore db level privileges can not be issued against it.
Sincerely,
Kasia

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

StephenHodge — Wed, 21 Dec 2011 14:04:45 GMT

To take a slightly different tract with this, if you do have 2 schemas (the standard POSTGRES public and sde) and you have an application that writes to the tables in the public schema, is there a way for ArcCatalog to be able to view the contents of those tables? I have tried unsuccessfully to register them through the command line and right clicking in ArcCatalog

Re: Using Schemas on SDE 9.3.1 and PostgreSQL 8.3.11

Anonymous User — Wed, 21 Dec 2011 15:08:47 GMT

Original User: kasi4188

Stephen,
In ArcGIS 10, you will not be able to see data through ArcCatalog that is stored in the postgres public schema.
In general I would recommend that any spatial data stored in Postgres, reside in a user/schema explicitly created for that purpose, by an admin on that cluster. Please do not use the following users to store spatial data: postgres, sde, public.
You can create additional users with pgAdminIII or with a sql script provided in the ArcSDE installation.
In ArcGIS 10.1, a gp tool will be provided to create users, so it will be a little easier.
Sincerely,
Kasia