Microsystems Technology Laboratories > OpenCoral
 

Setup OpenCoral Database Users and Schema

This section describes how to setup the OpenCoral database users and schema.

Initial Setup of Database Users

Now we need to create the database tables, insert bootstrap data, and some initial fab data. As postgres, set the database and its password [db.password].

$ psql template1 postgres
Welcome to psql 9.xx.xx, the PostgreSQL interactive terminal.
                                                                                
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
                                                                                
template1=# alter user postgres with password '[db.password]';
ALTER USER
template1=# \q
Note
We are assuming that the user "postgres" is the system-wide database adminstrator with a password of "db.password". Of course, you should not use "db.password" but should enter a database password of your choosing. You will also need to remember this password for future use. We will then create the database users named "coraldba" that will be the database adminstrator for the Coral database. Later in this process, coraldba will create a series of database users with names like "rscmgr", "eqmgr" that will own the tables that are needed by the resource manager, the equipment manager, and the other Coral servers. In general, it is reasonable for "coraldba", "rscmgr", "eqmgr", and all of the other database users (except for the system-wide DBA "postgres") to share a common password. In fact, the Coral build and install process assumes that this will be the case. However, these users should have a password that is different from that of the main system-wide DBA. As a result, in the following material, the password of the main "postgres" database users is listed as "db.password" whereas the password of "coraldba", "rscmgr", "eqmgr" and the other Coral-specific database users are assumed to be "coraldba.password". Of course, you should select your own good password for "postgres" and for "coraldba", and the other database users.

Add the language plpgsql to the template1 database. This langauge will be used for any stored procedures that are used by Coral. If it is added at this time, it should be availabe to other databases that are subsequently created.

$ createlang plpgsql template1
CREATE LANGUAGE

Now add user coraldba to postgres

$ createuser -d -a -P coraldba
Enter password for new user: [coraldba.password]
Enter it again: [coraldba.password]
Password: [db.password]
CREATE USER

Now add database coral

$ createdb -e -O coraldba coral 'OpenCoral Database'
Password: [db.password]
CREATE DATABASE coral;
CREATE DATABASE
Password: [db.password]
COMMENT ON DATABASE coral IS 'OpenCoral Database';
COMMENT

Make sure that the plpgsql prodecural languange is available to the coral database:

$ createlang -l coral
Procedural Languages
  Name   | Trusted?
---------+----------
 plpgsql | yes

If plpgsql is not associated with the coral database, it may be added at any time with the command:

$ createlang plpgsql -U coraldba coral
CREATE LANGUAGE

Setting Up Server-specific Database Users and Database Tables

At this point we've created an empty database named coral that is owned by the main database user coraldba. Now we are about to create a series of secondary database users that will correspond to each of the Coral servers and will each own the tables that they need to control. In other words, the Coral equipment server will have tables that are owned by the database user eqmgr and that database user will own and manage the database tables that are used by the equipment server.

Edit /path_to/opencoral/src/sql/Postgres/initialSetup/lab_db.sql replace all 'password' to your coraldba passwords.

Note
The ant build file assumes that all of these database users have the same password and does not support different passwords for each user. The only exception for this is the password for the read-only database user named "reader" that is used by the xReporter reporting engine. This password SHOULD BE different from that of the other database users.

Once you have changed the passwords in the file lab_db.sql to something that you want, you can run all of the sql script files by typing the command:

$ make

Make will prompt you for the coraldba password and then, as it runs some of the subsequent SQL files, it will prompt you for the password of the other database users.

Note
It is important to realize that running these files will wipe out any existing data. As a result, these files should only be run during a fresh Coral installation.

Once these commands have completed, you should edit lab_db.sql to chage the password that you previously entered back to 'password'. This is important to avoid your database password from being easily visible to anyone looking through your filesystem.

The results of each *.sql script will be stored in a log file of the same name. In other words, the result of running lab_db.sql are stored in lab_db.log.

Implicit casting in Postgresql 8.3 and Newer

Note
You may ignore this section if you are using either version 8.1 or 8.2 of Postgresql.
Note
If you are installing Coral servers on a Linux server, you may find that you are using a version of Postgresql that is version 8.3.xx, 8.4.xx or newer. You can determine the version of Postgresql that you are running by issuing the command rpm -qa | grep postgresql. If you are running Postgresql version 8.3 or newer, you need to follow the instructions in the following paragraphs to update your Postgresql installation to support implicit casting.

Versions of Postgresql newer than 8.2 have removed implicit casts based on the contention that implicit casts create problems. While this may be true in certain instances, it is also true that different databases .... Oracle and Postgresql in particular in the case of Coral ... have different syntax for explicit casting from one type to another. While a future version of Coral may include only explicit casts, Coral currently supports and makes use of implicit casts.

If you are running Postgresql 8.3 or newer you need to follow the following instructions to add an appropriate number of implicit casts to make Postgresql 8.3 or newer handle the same explicit casts that are supported in Postgresql 8.2 or older, and all versions of Oracle.

Go to https://opencoral.mit.edu/files/Pg83-implicit-casts.sql.gz and download that file to your local machine.

Uncompress it with the command 'gunzip Pg83-implicit-casts.sql.gz'. Next, create those implicit casts with the command:

psql coral coraldba -f Pg83-implicit-casts.sql