Previous
Index Next
ISCS version 0.1.1
The DataBase Distributor performs two vital functions as its name implies.
It is responsible for holding the production database as well as all edit databases used while making changes with the SPM. ISCS is a multi-concurrent user system. Any number of security administrators may be running an SPM locally on their computer simultaneously but there is only one DBD. All SPM users connect to the DBD.
The DBD is responsible for distributing changes to the PEPs. It has root access to the PEPs based upon its SSH rsa key and is the vital security link for the entire system. One should take great precaution to make the DBD a highly secure computer from both a physical and network perspective.
The DBD may be installed on the same computer as the SPM (for example, in small deployments with a single security administrator where such a set up is considered sufficiently secure - remember the DBD is a security vulnerability point). The DBD may also be installed on a separate computer and support multiple, simultaneous SPM connections.
The DBD may theoretically run any RDBMS supported by the Qt library. This includes most major RDBMS such as Oracle, DB2, MSSQL, Sybase, MySQL and PostgreSQL. However, due to the restrictions of the Qt GPL license, the GPL version of ISCS can only use MySQL or PostgreSQL. Lest anyone think the ISCS development team thinks ill of Trolltech (the Qt creators - www.trolltech.com) for this, please let it be known that we are deeply grateful for the outstanding toolkit Trolltech provides to open source developers. The DBD may thus run on any platform as long as it can run the requisite RDBMS and has a version of SSH which will allow user authentication via RSA keys. We recommend that password ssh authentication be disabled.
There is no support in this version for MySQL or PostgreSQL on any other platform than Linux. Tested versions are MySQL 4.1.22, MySQL 5.0.51, PostgreSQL 8,3.
Create a new group on the DBD named "SPM".
The SPM users will communicate with a remote DBD via SSH. This does not apply when the SPM and DBD are on the same device. Each user will need an account on the DBD and should be a member of the SPM group. Each user should copy the contents of their ssh id_rsa.pub file (typically in ~/.ssh) from their SPM computer to the .ssh/authorized_keys file in their home directory on the DBD. This will enable passwordless connections.
In version 0.1.1 you must connect from the SPM user's workstation to the DBD from the command line first to make the needed entry in the known_hosts file. This is also good practice to ensure SSH is functioning properly before running the SPM.
SSH keys may be created with ssh-keygen, e.g., to create a keypair for root, login as root and execute:
ssh-keygen -t rsa
Press enter to accept the default
file name
Press enter to accept an empty password
Press enter
again to confirm the empty password
The SPM users also need to communicate with the PEPs through the DBD. For now, the DBD communicates with the PEPs using the root account. To enable this passthrough access using the root account, copy root's ssh RSA private key (/root/.ssh/id_rsa) to the .ssh directory of the user's home directory on the DBD and rename it to id_rsa_SPM - RENAME it while or before copying as you do not want to overwrite the user's id_rsa file. Make sure you change the ownership of id_rsa_SPM to the user and that the permission mask is 600, for example:
chown User:User ~/.ssh/id_rsa_SPM
chmod 600
~/.ssh/id_rsa_SPM
The technique outlined above with the id_rsa_SPM key should be used even when the SPM and DBD reside on the same computer. Doing so will enable the user to run the SPM without being logged in as root.
In version 0.1.1, the name of the file is not configurable; it must be id_rsa_SPM even if running the SPM as root.
The SPM users will copy files to the DBD via scp. Create the following directory structure and ensure that the SPM group has read, write and execute rights. Since these directories will contain sensitive information, we suggest an access mask of 770 (chmod -R 770 /usr/local/SPM). Remember to change these rights after copying in all files as instructed below.
/usr/local/SPM/
Database/
spm_spm/
data/
dbversions/
PEPfiles/
Create a file in /usr/local/SPM/spm_spm/dbversions named updateInProcess and set its contents to the single character '0' (zero) (e.g., echo 0 > updateInProcess). This file must also be readable and writable by the SPM group.
Copy all of the sql files used to create the initial database for your database type (e.g., MySQL, PostgreSQL), i.e., spmskeleton, CountryCodes, models, services, SPMData from the DBD tarball directory into the /usr/local/SPM/Database/ directory.
Copy the SPMData file used to create the initial database for your database type (e.g., SPMData.mysql, SPMData.pgsql) from the DBD tarball directory into the /usr/local/SPM/spm_spm/data/ directory. This file must also be readable and writable by the SPM group.
Change all rights as suggested above.
There are scripts in the DBD directory of the tarball for database setup and maintenance. These end in "dbcopy," "dbnew," and "dbdelete" and are prefaced with either "My" for MySQL or "Pg" for PostgreSQL. There is also a script named cleanDBdump. Copy the appropriate scripts for your RDBMS and the cleanDBdump script into the executable path (e.g., for PostgreSQL, cp {Pg*,cleanDBdump} /usr/bin) and ensure that they are executable by all users.
Finally, we suggest changing the ConnectTimeout setting in ssh_config (typically /etc/ssh/ssh_config - note that this is different from sshd_config) to 60 from the default 0. This will reduce the time it takes to timeout from a down PEP or to report a failure to communicate between a remote SPM and the DBD from 180 seconds to 60 seconds.
The RDBMS on the DBD must support transactions. If you are using MySQL, you must enable support for InnoDB tables. If you are using PostgreSQL, you must enable support for the PL/PGSQL language (as the postgres user with PostgreSQL running, issue the command "createlang plpgsql template1", e.g., as root run: su -c "createlang plpgsql template1" postgres).
The SPM users will be prompted for a user name and password to access the database. Ensure there is a valid user they can use. The user will need read and write rights to the spm database and the rights to create and drop new databases as well as having all rights to the databases they create. Each user will create a temporary edit database for their edit session by dumping the spm database, creating the edit database and importing the dumped data into the new edit database.
Especially if one's RDBMS is used for other applications besides ISCS (not generally recommended as the DBD must be protected with the utmost security - it is the key to all ISCS security), one may wish to restrict database users to only being able to create and drop ISCS databases. All ISCS databases will start with "spm_".
The following setup instructions are intended as guidelines. The author is not an experienced DBA. If you know of a better way to achieve the same results, please feel free to use it. In fact, please let us know so we can include it in future versions of this documentation.
Edit the pg_hba file so that the authentication method for "local all all" is md5 (or some other password method) instead of "ident sameuser".
Reread pg_hba (pg_ctl reload or /etc/init.d/postgresql reload) if PostgreSQL is running otherwise start PostgreSQL.
Become the postgres user (su - postgres).
Add support for PL/PGSQL (createlang plpgsql template1)
Start psql (psql).
Create the spm role (CREATE ROLE spm;).
Create users with the CREATEDB priviledge (CREATE ROLE <username> LOGIN CREATEDB PASSWORD '<password>';).
Add the users to the spm role (GRANT spm TO <user1>,<user2> . . . <usern>;).
As a user with rights to read the files in the /usr/local/SPM/Database directory, run the following command:
Pgdbnew <database user name> <database user password> spm_spm /usr/local/SPM/ postgres
The first two parameters should be obvious. The third is the name of the default database to be created; the fourth is where to find the Database directory created in the previous section and the fifth is an existing database required by some of the psql commands in the script.
You may wish to clear your command history (history -c) lest someone be able to obtain your DBD password from your history file.
Start mysql as the superuser or whichever user used to created new MySQL users (mysql -u root -p)
Create any new users with each of the two below commands (one is for local usage and the other for network usage):
grant ALL on `spm\_%`.* to '<username>'@'%' identified by '<password>'; (N.B. The back quotes rather than single quotes around the wildcard definition of the database name).
grant ALL on `spm\_%`.* to '<username>'@'localhost' identified by '<password>'; (N.B. The SPM will use database logins from both the localhost (DBD) and the computer the hosting the SPM thus the need to set up both accounts in MySQL).
A MySQL user must also have RELOAD privileges. This can only be granted on the global level so we need two more commands:
grant RELOAD on *.* to '<username>'@'%' identified by '<password>';
grant RELOAD on *.* to '<username>'@'localhost' identified by '<password>';
Exit the mysql client.
As a user with rights to read the files in the /usr/local/SPM/Database directory, run the following command:
Mydbnew <database user name> <database user password> spm_spm /usr/local/SPM/
The first two parameters should be obvious. The third is the name of the default database to be created and the fourth is where to find the Database directory created in the previous section.
You may wish to clear your command history (history -c) lest someone be able to obtain your DBD password from your history file.
The DBD should now be ready to service an ISCS network.