Create a new user in PostgreSQL using psql

Summary

Instructions provided below describe how to add a new user to a PostgreSQL geodatabase using the psql interactive terminal.

The psql PostgreSQL interactive terminal can be used to create additional users in a PostgreSQL ArcGIS Enterprise geodatabase. The commands in this How To article will:

• Create a user and assign a password
• Create a schema for the user if that user will own data within the geodatabase
• Assign appropriate privileges so that other users can access the data in the newly created user's schema

This article assumes that PostgreSQL has already been installed.

Procedure

Follow the steps below:

1. In Windows, go to Start > All Programs > PostgreSQL 8.3 > psql to 'postgres'. This opens up the psql interactive terminal.
2. Type the following statement, supplying the desired username and password:

CREATE ROLE username LOGIN PASSWORD 'password' NOINHERIT CREATEDB;

3. Press the Enter key. This creates a new user login, or ROLE as it is called in postgreSQL, and assigns a password.
4. The next step is to create a schema for the new user. This step is necessary only for those users who will own data. Before creating the schema, connect to the database where the schema will be created. In this example, the database name is sde:

\c sde

CREATE SCHEMA username AUTHORIZATION username;

5. Grant usage on the schema to other users:

GRANT USAGE ON SCHEMA username TO PUBLIC;

6. If using a database that is PostGIS enabled, additional privileges must be granted to the user:

GRANT SELECT, INSERT, UPDATE, DELETE ON public.Geometry_columns TO username;

7. The new user account is now ready to be used in the PostgreSQL geodatabase. To exit (quit) from the the PostgreSQL interactive terminal, type the following and press Enter.

\q