Setup OpenCoral Database Users and Schema

offbeatlossData Management

Nov 22, 2012 (4 years and 6 months ago)

269 views

Copyright © 2000 The OpenCoral Software Project. The Board of Trustees of the Leland Stanford
Junior University and the Massachusetts Institute of Technology. All rights reserved. All rights reserved.
Setup OpenCoral Database Users and
Schema
Table of contents
1 Initial Setup of Database Users.........................................................................................2
2 Setting Up Server-specific Database Users and Database Tables.....................................3
3 Implicit casting in Postgresql 8.3 and Newer....................................................................4
Setup OpenCoral Database Users and Schema
Page 2
Copyright © 2000 The OpenCoral Software Project. The Board of Trustees of the Leland Stanford
Junior University and the Massachusetts Institute of Technology. All rights reserved. All rights reserved.
This section describes how to setup the OpenCoral database users and schema.
1 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 7.4.13, 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
Setup OpenCoral Database Users and Schema
Page 3
Copyright © 2000 The OpenCoral Software Project. The Board of Trustees of the Leland Stanford
Junior University and the Massachusetts Institute of Technology. All rights reserved. All rights reserved.
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
2 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.
Setup OpenCoral Database Users and Schema
Page 4
Copyright © 2000 The OpenCoral Software Project. The Board of Trustees of the Leland Stanford
Junior University and the Massachusetts Institute of Technology. All rights reserved. All rights reserved.
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.
3 Implicit casting in Postgresql 8.3 and Newer
Note:
You may ignore this section if you are running on a RedHat Linux installation that uses either
version 8.1 or 8.2 of Postgresql.
Note:
If you are installing Coral servers on a machine that is not running RedHat Linux, you may find
that you are using a version of Postgresql that is version 8.3.something or 8.4.something that is
significantly newer than the version that is currently supported under RedHat. RedHat Linux
currently supports Postgresql version 8.1.something. You can determine the version of Postgresql
that you are running by issuing the command rpm -qa | grep postgresql. If you are
running version 8.3.something or 8.4.something, 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.3, including 8.4, 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 Postgresql 8.4 you need to follow the following
instructions to add an appropriate number of implicit casts to make Postgresql 8.3 and
Setup OpenCoral Database Users and Schema
Page 5
Copyright © 2000 The OpenCoral Software Project. The Board of Trustees of the Leland Stanford
Junior University and the Massachusetts Institute of Technology. All rights reserved. All rights reserved.
Postgresql 8.4 handle the same explicit casts that are supported in Postgresql 8.1, Postgresql
8.2, 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