Desktop Help 10.0 - Adding users to a PostgreSQL database

PostgreSQL uses roles to log in to the database cluster and databases. You add roles to the PostgreSQL database cluster.

Individual database users are called login roles. For all login roles that will own objects in the geodatabase, you must also create a schema in that database. The schema must have the same name as and be owned by the login role.

You can also create group roles to which login roles can be added. Then you can specify permissions on the group that will apply to all associated login roles.

Note:

You must still create a matching schema for each login role in the group that will own objects in the geodatabase. You cannot create a schema for the group role.

You can use a PostgreSQL client application, such as pgAdmin III, or PL/pgSQL to create a role in the PostgreSQL database cluster. These instructions show you how to create a role, schema, and group using PL/pgSQL and grant permissions to the role or group.

Tip:

A SQL script, roles_schemas_privileges.sql, is installed in SDEHOME > tools > postgres. This script can be altered and used to create users and their schemas and grant permissions in the database.

Steps:

Log in to PL/pgSQL as a user with permissions to create other roles in the DBMS. This is usually the postgres super user.
Execute the CREATE ROLE command.
In this example, a login role, role4u, is created with an encrypted password. The role is able to create objects in the database but is not a super user, does not inherit permissions from groups, and cannot create roles in the database cluster.
```
CREATE ROLE role4u LOGIN 
ENCRYPTED PASSWORD 'super.secret' 
NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;
```
Tip:
If you know in advance that you want to assign this login to a group, create the login with INHERIT permissions.
Execute the CREATE SCHEMA command to create a schema for the login role in the database where the geodatabase is stored.
In this example, the matching role4u schema is created, and authorization on the schema is granted to the role4u login:
```
CREATE SCHEMA role4u AUTHORIZATION role4u;
```
Grant USAGE and CREATE on the schema to the login role.
This is required to allow the user to create log file tables. See ArcSDE log file table configuration options for PostgreSQL for more information on log file tables.
```
GRANT USAGE ON SCHEMA role4u TO role4u;
GRANT CREATE ON SCHEMA role4u TO role4u;
```
Grant USAGE permissions on the schema to any other login or group role that needs to access the data in the user's schema. In this example, USAGE is granted to the public group so that all users connecting to the database can access data in the role4u schema:
```
GRANT USAGE ON SCHEMA role4u TO public;
```
If you want to place the login in a group to control permissions, create a group role.
For example, you might create a group for all logins creating data in the geodatabase. Here, the group role name is dataowner:
```
CREATE ROLE dataowner 
NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;
```
Grant the group role privileges to all applicable login roles.
In this example, the group is granted to the role4u login. You would execute this statement for any other logins you wanted added to the group.
```
GRANT dataowner TO role4u;
```
If the login role was created without the ability to inherit from a group (as is the case in the examples in this topic), enable the login role to inherit group privileges from the groups to which it is assigned.
```
ALTER ROLE role4u INHERIT;
```
If you are using groups, you can grant permissions on other datasets to the group. If not, grant permissions to individual login roles.
In this example, the dataowner group is granted permission on the geometry_columns and spatial_ref_sys tables in the public schema. These permissions are required for any users who create data that uses PostGIS geometry storage.
```
GRANT SELECT, INSERT, UPDATE, DELETE 
ON public.geometry_columns 
TO dataowner;

GRANT SELECT 
ON public.spatial_ref_sys 
TO dataowner;
```

8/19/2013