Prerequisites

• PostgreSQL installed and running.

• Access to PostgreSQL with administrative privileges.

• Git installed.

• Command Line Interface (CLI) available (Windows).

Step-by-Step Instructions

1. Connect to PostgreSQL (Windows)

First, connect to your PostgreSQL instance using a command-line tool or a database client. Ensure you are connected with a user that has administrative privileges, usually the username is postgres.

psql -U postgres

Replace postgres with your administrative username if it is different.

2. Create the Database (Windows)

Create a new database named test_imis.

CREATE DATABASE "test_imis";

3. Create the User and Grant Connection Privileges (Windows)

Create a new user named IMISuser with the specified password and grant the user the privilege to connect to the test_imis database.

CREATE USER "IMISuser" WITH PASSWORD 'IMISuser@1234';
GRANT CONNECT ON DATABASE test_imis TO "IMISuser";

4. Switch to the Database (Windows)

Switch to the test_imis database to grant further permissions for IMISuser using the user that has administrative privileges (in this case, postgres). Ensure you are logged in with the correct user by running the following script:

\c test_imis

Check the current user:

SELECT session_user, current_user;

5. Install PL/pgSQL Extension and Grant Permissions (Windows)

Ensure that the PL/pgSQL procedural language extension is installed. Then grant the necessary schema and table permissions to IMISuser.

CREATE EXTENSION IF NOT EXISTS "plpgsql" WITH SCHEMA "pg_catalog";
COMMENT ON EXTENSION "plpgsql" IS 'PL/pgSQL procedural language';

GRANT CREATE ON SCHEMA public TO "IMISuser";
GRANT USAGE ON SCHEMA public TO "IMISuser";
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO "IMISuser";
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO "IMISuser";

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "IMISuser";

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO "IMISuser";

COMMENT ON SCHEMA "public" IS 'standard public schema';

6. Clone the openIMIS Database Repository (Windows)

Clone the openIMIS database repository from GitHub.

git clone <https://github.com/openimis/database_postgresql.git>

7. Prepare the SQL Scripts (Ubuntu)

Open the Ubuntu Terminal (WSL) and navigate to the directory containing the concatenate_files.sh script.

cd /mnt/c/<Path to your cloned repo>/database_postgresql

Run the script to generate the SQL files. After running the script, navigate to the Output folder to find the fullDemoDatabase.sql or EmptyDatabase.sql files.

8. Modify the SQL Scripts (Windows)

Open the generated fullDemoDatabase.sql or EmptyDatabase.sql files in Windows and comment out the following lines:

-- COMMENT ON SCHEMA "public" IS 'standard public schema';
-- CREATE EXTENSION IF NOT EXISTS "plpgsql" WITH SCHEMA "pg_catalog";
-- COMMENT ON EXTENSION "plpgsql" IS 'PL/pgSQL procedural language';

9. Login with IMISuser (Windows)

Log in to PostgreSQL using the IMISuser account in the Windows CLI.

psql -U IMISuser -d test_imis

10. Verify the File Path (Windows)

Navigate to the directory containing the SQL files in the Windows CLI and list the files to ensure the correct path.

cd C:\<Path to your cloned repo>\database_postgresql\output
dir

Ensure fullDemoDatabase.sql is listed.

11. Execute the SQL Scripts (Windows)

Execute the modified fullDemoDatabase.sql or EmptyDatabase.sql scripts to set up the database in the Windows CLI.

psql -U IMISuser -d test_imis -f C:\<Path to your cloned repo>\database_postgresql\output\fullDemoDatabase.sql

or

psql -U IMISuser -d test_imis -f C:\<Path to your cloned repo>\database_postgresql\output\EmptyDatabase.sql

Replace <Path to your cloned repo> with the actual path to the folder where you have cloned the database_postgresql repository.

Summary

You have now successfully set up the openIMIS Database in PostgreSQL with the necessary user and permissions. The user IMISuser has the appropriate rights to interact with the database and its objects. Additionally, you have cloned the openIMIS database repository and executed the required SQL scripts.