Observational Health Data Sciences and Informatics

All WebAPI documentation has moved to GitHub. Please disregard the content below as it is legacy and kept for posterity.

You can install the PostgreSQL core distribution from the following URL: http://www.postgresql.org/download/ Follow your platform specific installation to install the binary and start the service. Remember the postgreSQL user and password that is set during the installation process since this will be needed later to administer the server (creating new databases and adding new schemas).

For the database administration, we will be using the pgAdmin III tool available from the PostgreSQL Downloads URL above. After launching, the main window will look like this (ignoring the two additional connections for Public OHDSI and Public Ohdsi WebAPI): [pgAdmin_welcome.png]

If your environment already has application level administration and non-administrative access groups created, you can skip this section on creating the roles. Before creating a database, roles and users should be created for administering the OHDSI database and for non-administrative access (ie: application access) to the database. The 2 groups created in this guide are ohdsi_admin and ohdsi_app. The ohdsi_admin group will have ownership of the OHDSI database and have rights to create/alter/drop database objects, and the ohdsi_app group will have only the rights to access the database objects needed for the WebAPI service calls.

In pgAdmin III, right click Group Roles and select ‘New Group Role’. This presents the following dialog:

[pgAdmin_new_group_1]

Each tab will have values entered to configure the group. Two groups are created: the admin group (to create the database and administer tables) and the app group to grant limited rights to the applications for accessing the database.

Group: ohdsi_admin Tab: Properties Role Name: ohdsi_admin Comment: Administration group for OHDSI applications

Tab: Definition (Leave all values blank since we will not let this group log into the database)

Tab: Role Privileges Check: Inherits rights from parent roles, can create databases, can initiate streaming replication and backups

Tab: Role membership

The ohdsi_admin role is not a member of any other parent group, so leave member empty. After these values are set, the generated SQL to create this role is produced:

CREATE ROLE ohdsi_admin
CREATEDB REPLICATION
 VALID UNTIL 'infinity';
COMMENT ON ROLE ohdsi_admin
IS 'Administration group for OHDSI applications';

Group: ohdsi_app Tab: Properties Role Name: ohdsi_app Comment: Application group for OHDSI applications

Tab: Definition (Leave all values blank since we will not let this group log into the database)

Tab: Role Privileges Check: Inherits rights from parent roles

Tab: Role membership The ohdsi_app role is not a member of any other parent group, so leave member empty.

After these values are set, the generated SQL to create this role is produced:

CREATE ROLE ohdsi_app
 VALID UNTIL 'infinity';
COMMENT ON ROLE ohdsi_app
IS 'Application groupfor OHDSI applications';

In pgAdmin III, right-click Login Roles and select ‘New Login Role’. This will present the dialog: [pgAdmin_new_login_1]

This dialog is similar to the group dialog, but in this case we will be setting the password for these logins and placing them into their corresponding roles (either _admin or _app). This allows us to define permissions to the database at the group level, and not the individual user level.

Login: ohdsi_admin_user Tab: Properties Role Name: ohdsi_admin_user Comment: Admin user account for OHDSI applications

Tab: Definition Password: admin1 {note: do not use this for your production environment!}

Tab: Role Privileges Check: Inherits rights from parent roles

Tab: Role membership Add ohdsi_admin group to the list of members for this login

After these values are set, the generated SQL to create this role is produced:

CREATE ROLE ohdsi_admin_user LOGIN ENCRYPTED PASSWORD 'md58d34c863380040dd6e1795bd088ff4a9'
 VALID UNTIL 'infinity';
GRANT ohdsi_admin TO ohdsi_admin_user;
COMMENT ON ROLE ohdsi_admin_user
IS 'Admin user account for OHDSI applications';

Login: ohdsi_app_user Tab: Properties Role Name: ohdsi_app_user Comment: Application user account for OHDSI applications

Tab: Definition Password: app1 {note: do not use this for your production environment!}

Tab: Role Privileges Check: Inherits rights from parent roles

Tab: Role membership Add ohdsi_app group to the list of members for this login

After these values are set, the generated SQL to create this role is produced:

CREATE ROLE ohdsi_app_user LOGIN ENCRYPTED PASSWORD 'md55cc9d81d14edce93a4630b7c885c6410'
 VALID UNTIL 'infinity';
GRANT ohdsi_app TO ohdsi_app_user;
COMMENT ON ROLE ohdsi_app_user
IS 'Application user account for OHDSI applications';

Note: if you have an existing database you can skip this step and proceed to the schema preparation section below. In pgAdmin, right click Databases and select ‘New Database’. The following dialog is displayed: [pgAdmin_new_database_1]

Tab: Properties Name: OHDSI Owner: ohdsi_admin Comment: OHDSI database

Tab: Definition Leave all attributes the default Variables: Nothing to enter here Privileges: Add role ohdsi_admin with ALL, add role ohdsi_app with Temp and Connect

The following SQL is generated:

CREATE DATABASE "OHDSI"
WITH ENCODING='UTF8'
     OWNER=ohdsi_admin
     CONNECTION LIMIT=-1;
COMMENT ON DATABASE "OHDSI"
IS 'OHDSI database';
GRANT ALL ON DATABASE "OHDSI" TO GROUP ohdsi_admin;
GRANT CONNECT, TEMPORARY ON DATABASE "OHDSI" TO GROUP ohdsi_app;

Note: this step is extremely important. If you do not assign default permissions when connected as the ohdsi_admin_user, the ohdsi_app_user account will not be granted the correct access privileges when the WebAPI creates the tables in the database!

In pgAdmin, create a new connection to the PostgreSQL server, but using the ohdsi_admin_user account created above. This account will be used to create the database to host the WebAPI schema and tables. The new connection should be configured as follows:

[pgAdmin_new_connection_1]

Note: username and password was the admin user and password that was created above. Once connected, the pgAdmin III as ohdsi_admin_user, object browser should look as follows: [pgAdmin_new_connection_2]

Expand the OHDSI database, and right click on Schemas to create a new Schema: [pgAdmin_new_schema_1]

Tab: Properties Name: webapi Owner: ohdsi_admin Comment: Schema containing tables to support WebAPI functionality

Tab: Privileges: Add public as USAGE, ohdsi_admin as ALL, ohdsi_app as USAGE The following SQL is generated:

CREATE SCHEMA webapi
     AUTHORIZATION ohdsi_admin;
COMMENT ON SCHEMA webapi
IS 'Schema containing tables to support WebAPI functionality';
GRANT USAGE ON SCHEMA webapi TO public;
GRANT ALL ON SCHEMA webapi TO GROUP ohdsi_admin;
GRANT USAGE ON SCHEMA webapi TO GROUP ohdsi_app;

Once the schema is created, right click on the new schema ‘webapi’ and select ‘Properties’

[pgAdmin_new_schema_2]

Tab Default Privileges: Sub-Tab: Tables Add group ohdsi_app with INSERT, SELECT, UPDATE, DELETE, REFERENCES and TRIGGER.

Sub-Tab: Sequences Add group ohdsi_app with SELECT and USAGE

Sub-Tab: Functions Add group ohdsi_app with EXECUTE

Sub-Tab: Types Add group ohdsi_app with USAGE

The following SQL is generated:

ALTER DEFAULT PRIVILEGES IN SCHEMA webapi
  GRANT INSERT, SELECT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES
  TO ohdsi_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA webapi
  GRANT SELECT, USAGE ON SEQUENCES
  TO ohdsi_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA webapi
  GRANT EXECUTE ON FUNCTIONS
  TO ohdsi_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA webapi
  GRANT USAGE ON TYPES
  TO ohdsi_app;

Note: If you do not assign default privileges when connected as the ohdsi_admin_user, the ohdsi_app_user account will not be granted the correct access privileges when the WebAPI creates the tables in the database!

After creating the schemas and assigning the default privileges, the object browser should look as follows:

[pgAdmin_new_schema_3]