Skip to content

PostgreSQL Server Setup

Jump to bottom
Doyle Rowland edited this page Feb 13, 2022 · 8 revisions

Installing and configuring a postgresql server is really outside the scope of RAMSTK installation. Your first resource should be the official postgresql documents. Internet searches should provide you more assistance if needed. This page contains instructions and hints for setting up a postgresql server to use with RAMSTK. They may be helpful, but expect your mileage to vary.

RAMSTK Site Database Server

The RAMSTK Site or Common database contains information that is not specific to a particular development program. This includes such things as potential hazards, RPN definitions, component categories and subcategories, etc. The following instructions should help you get a Site database setup with the user 'ramstk' as the owner. The Site database should be installed on a machine that is accessible by all users rather than a local machine. In the commands below, replace <SERVER> with the name of the central postgresql server housing the Site database.

Two users will be created to interact with the Site database. A read only user called ramstk and a read/write user named ramstk_admin. The ramstk user connects to the Site database every time someone launches the RAMSTK application and reads the common information from the Site database. The ramstk_admin user has the ability to add, modify, and delete records from the Site database. It is recommended this user be assigned to no more than two people in your work group, a primary and backup, rather than allowing everyone in the group to be able to modify the information in the Site database.

  1. First create the ramstk and ramstk_admin users. In this example, the password for the ramstk user is also ramstk. The password for the ramstk_admin user is ramstk_admin.

    psql -h <SERVER> -U postgres template1 -c "CREATE USER ramstk WITH PASSWORD 'ramstk';"

    psql -h <SERVER> -U postgres template1 -c "CREATE USER ramstk_admin WITH PASSWORD 'ramstk_admin';"

  2. Create the Site database. The Site database must be named ramstk_common_ramstk.

    psql -h <SERVER> -U postgres template1 -c 'CREATE DATABASE ramstk_common_ramstk WITH OWNER ramstk;'

  3. Load the data into the ramstk_common_ramstk tables. This sql is installed to the system data directory when RAMSTK is installed. It is assumed RAMSTK was installed in /usr/local for this example.

    psql -h <SERVER> -U postgres ramstk_common_ramstk < /usr/local/share/RAMSTK/postgres_common_db.sql

  4. Grant permissions for the ramstk and ramstk_admin user to the ramstk_common_ramstk database.

    psql -h <SERVER> -U postgres ramstk_common_ramstk -c 'GRANT USAGE ON SCHEMA public TO ramstk;'

    psql -h <SERVER> -U postgres ramstk_common_ramstk -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO ramstk_admin;'

    psql -h <SERVER> -U postgres ramstk_common_ramstk -c 'GRANT USAGE ON SCHEMA public TO ramstk_admin;'

    psql -h <SERVER> -U postgres ramstk_common_ramstk -c 'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO ramstk_admin;'

  5. If RAMSTK is a new installation, launch RAMSTK. You will be prompted to enter the Site database connection parameters. Use the following:

Dialect: postgres
Server: <SERVER>
Port: 5432
Database: ramstk_common_ramstk
User: ramstk
Password: ramstk
  1. Click 'OK' to connect to the Site database and save the connection parameters to the Site configuration file.
  2. If this is not a new installation of RAMSTK, edit the Site.toml file found in /usr/local/share/RAMSTK with the updated Site database connection information.
title = "RAMSTK Site Configuration"

[backend]
dialect = "postgres"
host = "<SERVER>"
port = "5432"
database = "ramstk_common_ramstk"
user = "ramstk"
password = "ramstk"

RAMSTK Program Database Server

The RAMSTK Program database server is the server where the databases for each development program are stored. It is possible to have multiple Program database servers to choose from. For example, an aerospace company might choose to keep their military program on one server while using a different server for their civil aircraft programs. When creating a new or opening an existing Program database, the user will supply the necessary connection information. This information can be saved to the user's RAMSTK.toml file so it doesn't need to be manually entered each time.

The users accessing the RAMSTK Program database server need to have read/write access to all RAMSTK Program databases. These instructions accomplish that, but finer control is possible using SQL commands. That is beyond the scope of this guide, but see issue #951. In the commands below, replace <SERVER> with the name of the central postgresql server housing the Program databases.

  1. Add a RAMSTK analyst type user.

    psql -h <SERVER> -U postgres template1 -c "CREATE USER weibullguy WITH PASSWORD 'weibulldude';"

  2. Grant the new user the privilege of creating databases on this server.

    psql -h <SERVER> -U postgres template1 -c "ALTER USER weibullguy CREATEDB;"

  3. Change the default privileges to the new user. This ensures this user will be able to manipulate any new RAMSTK Program databases.

    psql -h <SERVER> -U postgres template1 -c "ALTER DEFAULT PRIVILEGES FOR ROLE weibullguy GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO weibullguy;"

This will give us a RAMSTK analyst named weibullguy who can create new databases on <SERVER> and will have read/write access to them. Keep in mind, this user will also have access to all databases on <SERVER>, even those created by other RAMSTK analysts.

Clone this wiki locally