MANAGING USERS AND PERMISSIONS IN POSTGRESQL AND POSTGIS

MANAGING USERS AND PERMISSIONS IN POSTGRESQL AND POSTGIS

Table of Contents

Create Users. 2

Create Schema (Optional). 2

Grant USAGE and CREATE on the schema to the user. 2

Grant USAGE permissions on the schema (s). 3

Group Roles. 3

Assign Users to the Roles. 3

Enable Inherit Group Priviledges. 3

Work with Spatial Types. 3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Create Users

 

#Grant USAGE and CREATE on the schema to the user.

#Log into PL/SQL 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 PL/SQL.

#      For example:

 

CREATE ROLE user1 LOGIN ENCRYPTED PASSWORD 'user1' NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;

CREATE ROLE user2LOGIN ENCRYPTED PASSWORD 'dmuthami' NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;

Create Schema (Optional)

 

#Execute the CREATE SCHEMA command to create a schema for the user in the database where the #geodatabase is stored.

#      For example:

#database of interest now is cso

 

CREATE SCHEMA user1 AUTHORIZATION user1;

CREATE SCHEMA user2AUTHORIZATION user1;

 

Grant USAGE and CREATE on the schema to the user.

#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 user1 TO user1;

GRANT CREATE ON SCHEMA user1 TO user1;

GRANT USAGE ON SCHEMA user2TO dmuthami;

GRANT CREATE ON SCHEMA user2TO dmuthami;

Grant USAGE permissions on the schema (s)

#Grant USAGE permissions on the schema to any other role or group that needs to access the data in #the user's schema.

#In this example, USAGE is granted to the Public group.

 

GRANT USAGE ON SCHEMA user1 TO public;

GRANT USAGE ON SCHEMA user2TO public;

Group Roles

#If you want to place the user in a group to control permissions, create another role to be used #as a group.

#For example, you might create a group for all users creating data in the geodatabase.

 

CREATE ROLE dataownersgroup NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;

Assign Users to the Roles

#Grant the group (user1) privileges to the role (role4u).

GRANT dataownersgroup TO user1;

GRANT dataownersgroup TO dmuthami;

Enable Inherit Group Priviledges

#Enable the role (user1) to inherit group privileges from the groups to which it is assigned.

ALTER ROLE user1 INHERIT;

ALTER ROLE user2INHERIT;

Work with Spatial Types

#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 dataownersgroup;

GRANT SELECT ON public.spatial_ref_sys TO dataownersgroup;

GRANT SELECT ON public.geography_columns TO dataownersgroup;

GRANT SELECT ON public.raster_columns TO dataownersgroup;

GRANT SELECT ON public.raster_overviews TO dataownersgroup;