Observational Health Data Sciences and Informatics

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
~~NOTOC~~

===== IMPORTANT NOTE =====

All WebAPI documentation has moved to [[https://github.com/OHDSI/WebAPI/wiki|GitHub]]. Please disregard the content below as it is legacy and kept for posterity.

===== PostgreSQL Setup Guide (LEGACY) =====
----

==== Database Engine Installation ====
----
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).

==== PostgreSQL database administration ====
----
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]

=== Creating the database Users and Roles ===
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.

=== Creating Database Group Roles ===
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';

=== Creating Database Login Roles===
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';

=== Creating the OHDSI WebAPI database ===
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;

=== Prepare Schema for WebAPI ===
//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]
Observational Health Data Sciences and Informatics

User Tools

Site Tools

Page Tools
