ArcGIS Server .NET Help - Creating a PostgreSQL database and sde user on Linux

The Linux installation of ArcSDE provides a script—setup_pgdb.sde—for creating the PostgreSQL tablespace, database, sde user, and its schema on Red Hat Linux. You can use the script provided on the installation media or manually perform these steps. For more information on PostgreSQL installation, see the PostgreSQL documentation.

The setup_pgdb.sde script also installs the PL/pgSQL language. Manual installation of PL/pgSQL is explained in Installing PL/pgSQL on Linux.

Note:

Although PostgreSQL allows you to store object names that are mixed case or upper case by doing such things as enclosing the name in quotes, you cannot use these in an ArcSDE geodatabase. Therefore, do not use mixed-case database, tablespace, user, or schema names.

Steps:

Log into psql as the postgres super user and create the ArcSDE administrative user. The user created must be named sde, but you can choose whatever password you like.
```
CREATE ROLE sde LOGIN PASSWORD <password> SUPERUSER NOINHERIT
CREATEDB;
```
If you want to store the database used for your ArcSDE geodatabase in a tablespace different from the default (pg_default), create a new tablespace in an empty directory.
In the following example, a tablespace named sde1 is created in the sde directory on a 32-bit server. The tablespace is owned by the sde user.
```
CREATE TABLESPACE sde1 OWNER sde LOCATION
'/var/lib/pgsql/data/sde';
```
Note:

PostgreSQL requires the tablespace to be created in an empty directory. If you specify a directory that already contains files, tablespace creation will fail.
Create a database and grant the sde user full access to it.

Full access is necessary for the sde user to create the geodatabase.

Note:

If you intend to use the PostGIS geometry type for data storage, you must create the database using the PostGIS template or enable an existing database for PostGIS geometry using a script provided by PostGIS.

The name of a database used to store a geodatabase cannot contain special characters.
In this example, the agency database is owned by the sde user, uses UTF8 character encoding, and is stored in the sde1 tablespace.
```
CREATE DATABASE agency OWNER sde ENCODING 'UTF8' TABLESPACE sde1;
GRANT ALL ON DATABASE agency TO sde;
```
Grant usage privileges to the public or to specific roles and groups so other users can access the database.
```
GRANT USAGE 
ON DATABASE agency 
TO public;
```
Log into the database as the postgres super user to create the sde user's schema. This is the schema in which the ArcSDE geodatabase repository will be stored. The schema name must be sde.
```
\c agency
CREATE SCHEMA sde AUTHORIZATION sde;
```
Grant the sde user full rights to the sde schema.
```
GRANT ALL 
ON SCHEMA sde 
TO sde;
```
Grant usage to the public or to specific roles and groups to access the sde schema.

Any user who interacts with the geodatabase must have usage on the sde schema.
```
GRANT USAGE 
ON SCHEMA sde 
TO public;
```
If you are using the PostGIS geometry type, you must also grant SELECT, INSERT, UPDATE, and DELETE privileges on the public.geometry_columns table to sde.
```
GRANT SELECT, INSERT, UPDATE, DELETE 
ON TABLE public.geometry_columns 
TO sde;
```
Also, if you are using the PostGIS geometry type, grant SELECT on the public.spatial_ref_sys table to sde.
```
GRANT SELECT
ON TABLE public.spatial_ref_sys
TO sde;
```

Create other users to access and manipulate data in the database.

Tip:

A file is included with ArcSDE that contains SQL statements for the creation of other users and their schemas. You can find this file, roles_schemas_privileges.txt, in the $SDEHOME > tools > postgres after you have installed ArcSDE.

Creating a PostgreSQL database and sde user on Linux

Related Topics