sokwedb - User contributions [en]

Installing pg isok

2025-12-15T19:48:46Z

StevanEarl:

pg_isok is "The Warning System".

== When to Install ==

Pg_isok is installed in its own schema, isolated from all of SokweDB's schemas. It only need be installed once, upon database creation. Removing or re-installing SokweDB's schemas using the build tools do not interfere with the content of Pg_isok's schema.

Of course, if an entire database is dropped, re-created, and restored from backup, pg_isok will also need to be restored. Depending on the situation, this may involve reinstalling pg_isok and then restoring the content of its tables.

== Installing The Warning System (pg_isok) ==

The [https://kop.codeberg.page/pg_isok_docs pg_isok] PostgreSQL extension is used as SokweDB's "Warning System". Because SokweDB is in the cloud, pg_isok must be installed from a SQL script.

To generate the SQL needed to install pg_isok, follow the cloud instructions found in the [https://kop.codeberg.page/pg_isok_docs pg_isok documentation].
SokweDB expects installation in a schema named <code>isok</code>.

Install pg_isok in each database.

After installation, permissions must be granted.

The following example shows the steps involved to install pg_isok version 0.1.4, using <code>psql</code>, and to grant the expected permissions. (The name of the SQL file, included with <code>\i</code>, will vary with the pg_isok version and the relative location of the sql file.)

<code>
SET ROLE TO admin;
CREATE SCHEMA isok;
GRANT USAGE ON SCHEMA isok TO reader;
GRANT USAGE ON SCHEMA isok TO writer;

\i ../pg_isok_cloud--0.1.4.sql

-- IQ_TYPES
GRANT SELECT ON isok.iq_types TO reader;
GRANT SELECT ON isok.iq_types TO writer;
GRANT INSERT ON isok.iq_types TO writer;
GRANT UPDATE ON isok.iq_types TO writer;
GRANT DELETE ON isok.iq_types TO writer;

-- IR_TYPES
GRANT SELECT ON isok.ir_types TO reader;
GRANT SELECT ON isok.ir_types TO writer;
GRANT INSERT ON isok.ir_types TO writer;
GRANT UPDATE ON isok.ir_types TO writer;
GRANT DELETE ON isok.ir_types TO writer;

-- ISOK_QUERIES
GRANT SELECT ON isok.isok_queries TO reader;
GRANT SELECT ON isok.isok_queries TO writer;
GRANT INSERT ON isok.isok_queries TO writer;
GRANT UPDATE ON isok.isok_queries TO writer;
GRANT DELETE ON isok.isok_queries TO writer;

-- ISOK_RESULTS
GRANT SELECT ON isok.isok_results TO reader;
GRANT SELECT ON isok.isok_results TO writer;
GRANT INSERT ON isok.isok_results TO writer;
GRANT UPDATE ON isok.isok_results TO writer;
GRANT DELETE ON isok.isok_results TO writer;

GRANT SELECT ON isok.isok_results_irid_seq TO reader;
GRANT SELECT ON isok.isok_results_irid_seq TO writer;
GRANT UPDATE ON isok.isok_results_irid_seq TO writer;

-- run_isok_queries()
GRANT EXECUTE ON FUNCTION isok.run_isok_queries() TO writer;
GRANT EXECUTE ON FUNCTION isok.run_isok_queries(TEXT) TO writer;</code>

== updating pg_isok ==

Installing or updating pg_isok requires a clean isok schema. As such, a backup of warn queries and results that can be reloaded after updating pg_isok is needed if that information should be maintained. The procedure for updating pg_isok is similar to those for installation with the additional steps of first creating a data-only dump of isok schema contents prior to dropping then creating the schema, and restoring those contents once the update is complete. As with installation, these steps need to be addressed separately for each database.

<code>pg_dump -h HOST -d DATABASE -n isok -Fc --data-only > isok_schema_contents</code>

within psql:

<code>DROP SCHEMA isok CASCADE;</code>

Follow the installation steps above to (re)install pg_isok then restore the dumped contents to repopulate the schema:

<code>pg_restore -h HOST -d DATABASE -n isok --data-only isok_schema_contents</code>

PostgreSQL Administration

2025-12-15T19:38:36Z

StevanEarl: now refer isok administration to the installing isok page

Installing pg isok

2025-12-15T19:33:07Z

StevanEarl:

pg_isok is "The Warning System".

== When to Install ==

Pg_isok is installed in its own schema, isolated from all of SokweDB's schemas. It only need be installed once, upon database creation. Removing or re-installing SokweDB's schemas using the build tools do not interfere with the content of Pg_isok's schema.

Of course, if an entire database is dropped, re-created, and restored from backup, pg_isok will also need to be restored. Depending on the situation, this may involve reinstalling pg_isok and then restoring the content of its tables.

== Installing The Warning System (pg_isok) ==

The [https://kop.codeberg.page/pg_isok_docs pg_isok] PostgreSQL extension is used as SokweDB's "Warning System". Because SokweDB is in the cloud, pg_isok must be installed from a SQL script.

To generate the SQL needed to install pg_isok, follow the cloud instructions found in the [https://kop.codeberg.page/pg_isok_docs pg_isok documentation].
SokweDB expects installation in a schema named <code>isok</code>.

Install pg_isok in each database.

After installation, permissions must be granted.

The following example shows the steps involved to install pg_isok version 0.1.4, using <code>psql</code>, and to grant the expected permissions. (The name of the SQL file, included with <code>\i</code>, will vary with the pg_isok version and the relative location of the sql file.)

<code>
SET ROLE TO admin;
CREATE SCHEMA isok;
GRANT USAGE ON SCHEMA isok TO reader;
GRANT USAGE ON SCHEMA isok TO writer;

\i ../pg_isok_cloud--0.1.4.sql

-- IQ_TYPES
GRANT SELECT ON isok.iq_types TO reader;
GRANT SELECT ON isok.iq_types TO writer;
GRANT INSERT ON isok.iq_types TO writer;
GRANT UPDATE ON isok.iq_types TO writer;
GRANT DELETE ON isok.iq_types TO writer;

-- IR_TYPES
GRANT SELECT ON isok.ir_types TO reader;
GRANT SELECT ON isok.ir_types TO writer;
GRANT INSERT ON isok.ir_types TO writer;
GRANT UPDATE ON isok.ir_types TO writer;
GRANT DELETE ON isok.ir_types TO writer;

-- ISOK_QUERIES
GRANT SELECT ON isok.isok_queries TO reader;
GRANT SELECT ON isok.isok_queries TO writer;
GRANT INSERT ON isok.isok_queries TO writer;
GRANT UPDATE ON isok.isok_queries TO writer;
GRANT DELETE ON isok.isok_queries TO writer;

-- ISOK_RESULTS
GRANT SELECT ON isok.isok_results TO reader;
GRANT SELECT ON isok.isok_results TO writer;
GRANT INSERT ON isok.isok_results TO writer;
GRANT UPDATE ON isok.isok_results TO writer;
GRANT DELETE ON isok.isok_results TO writer;

GRANT SELECT ON isok.isok_results_irid_seq TO reader;
GRANT SELECT ON isok.isok_results_irid_seq TO writer;
GRANT UPDATE ON isok.isok_results_irid_seq TO writer;

-- run_isok_queries()
GRANT EXECUTE ON FUNCTION isok.run_isok_queries() TO writer;
GRANT EXECUTE ON FUNCTION isok.run_isok_queries(TEXT) TO writer;</code>

== updating pg_isok ==

Installing or updating pg_isok requires a clean isok schema. As such, a backup of warn queries and results that can be reloaded after updating pg_isok is needed if that information should be maintained. The procedure for updating pg_isok is to installation with the additional steps of first creating a data-only dump of isok schema contents prior to dropping then creating the schema, and restoring those contents once the upgrade is complete. As with installation, these steps need to be addressed separately for each database.

<code>pg_dump -h HOST -d DATABASE -n isok -Fc --data-only > isok_schema_contents</code>

within psql:

<code>DROP SCHEMA isok CASCADE;</code>

Follow the installation steps above to (re)install pg_isok then restore the dumped contents to repopulate the schema:

<code>pg_restore -h HOST -d DATABASE -n isok --data-only isok_schema_contents</code>

Installing pg isok

2025-12-15T19:03:42Z

StevanEarl: added additional instructions for updating pg_isok

pg_isok is "The Warning System".

== When to Install ==

Pg_isok is installed in its own schema, isolated from all of SokweDB's schemas. It only need be installed once, upon database creation. Removing or re-installing SokweDB's schemas using the build tools do not interfere with the content of Pg_isok's schema.

Of course, if an entire database is dropped, re-created, and restored from backup, pg_isok will also need to be restored. Depending on the situation, this may involve reinstalling pg_isok and then restoring the content of its tables.

== Installing The Warning System (pg_isok) ==

The [https://kop.codeberg.page/pg_isok_docs pg_isok] PostgreSQL extension is used as SokweDB's "Warning System". Because SokweDB is in the cloud, pg_isok must be installed from a SQL script.

To generate the SQL needed to install pg_isok, follow the cloud instructions found in the [https://kop.codeberg.page/pg_isok_docs pg_isok documentation].
SokweDB expects installation in a schema named <code>isok</code>.

Install pg_isok in each database.

After installation, permissions must be granted.

The following example shows the steps involved to install pg_isok version 0.1.4, using <code>psql</code>, and to grant the expected permissions. (The name of the SQL file, included with <code>\i</code>, will vary with the pg_isok version and the relative location of the sql file.)

<code>
SET ROLE TO admin;
CREATE SCHEMA isok;
GRANT USAGE ON SCHEMA isok TO reader;
GRANT USAGE ON SCHEMA isok TO writer;

\i ../pg_isok_cloud--0.1.4.sql

-- IQ_TYPES
GRANT SELECT ON isok.iq_types TO reader;
GRANT SELECT ON isok.iq_types TO writer;
GRANT INSERT ON isok.iq_types TO writer;
GRANT UPDATE ON isok.iq_types TO writer;
GRANT DELETE ON isok.iq_types TO writer;

-- IR_TYPES
GRANT SELECT ON isok.ir_types TO reader;
GRANT SELECT ON isok.ir_types TO writer;
GRANT INSERT ON isok.ir_types TO writer;
GRANT UPDATE ON isok.ir_types TO writer;
GRANT DELETE ON isok.ir_types TO writer;

-- ISOK_QUERIES
GRANT SELECT ON isok.isok_queries TO reader;
GRANT SELECT ON isok.isok_queries TO writer;
GRANT INSERT ON isok.isok_queries TO writer;
GRANT UPDATE ON isok.isok_queries TO writer;
GRANT DELETE ON isok.isok_queries TO writer;

-- ISOK_RESULTS
GRANT SELECT ON isok.isok_results TO reader;
GRANT SELECT ON isok.isok_results TO writer;
GRANT INSERT ON isok.isok_results TO writer;
GRANT UPDATE ON isok.isok_results TO writer;
GRANT DELETE ON isok.isok_results TO writer;

GRANT SELECT ON isok.isok_results_irid_seq TO reader;
GRANT SELECT ON isok.isok_results_irid_seq TO writer;
GRANT UPDATE ON isok.isok_results_irid_seq TO writer;

-- run_isok_queries()
GRANT EXECUTE ON FUNCTION isok.run_isok_queries() TO writer;
GRANT EXECUTE ON FUNCTION isok.run_isok_queries(TEXT) TO writer;</code>

== updating pg_isok ==

Installing or updating pg_isok requires a clean isok schema. As such, a backup of warn queries and results that can be reloaded after updating pg_isok is needed if that information should be maintained. The procedure for updating pg_isok is generally the same as for installation with the additional steps of first creating a data-only dump of isok schema contents prior to dropping then creating the schema, and restoring those contents once the upgrade is complete. As with installation, these steps need to be addressed separately for each database.

<code>pg_dump -h HOST -d DATABASE -n isok -Fc --data-only > isok_schema_contents</code>

<code>DROP SCHEMA isok CASCADE;</code>

Follow the installation steps above to (re)install pg_isok then restore the dumped contents to repopulate the schema:

<code>pg_restore -h HOST -d DATABASE -n isok --data-only isok_schema_contents</code>

PostgreSQL Administration

2025-11-10T21:34:40Z

StevanEarl:

== Killing long-running queries ==

This section is about stopping, that is ''killing'', queries that should not be running.

Because users run SQL queries, they may inadvertently execute erroneous queries.
These queries usually run for excessive amounts of time and produce extremely large result sets.

Because SokweDB runs in the cloud and cloud billing is usage-based, in addition to slowing down the system, these queries can cost money.

=== Problem overview ===

It is relatively easy to write such a query, if you
<code>SELECT ...
FROM tablea, tableb, tablec ...</code>
and do not supply any <code>WHERE</code> or <code>JOIN</code> conditions,
the result will be the cross product of all rows of all tables. In other words, each row of each table will be paired up with every row of every other table, producing A times B times C number of output rows, where A, B, and C are the number of rows in <code>tablea</code>, <code>tableb</code>, and <code>tablec</code>.

Some of the generic database interfaces may have ways to monitor the database backend to discover and kill such bad queries.
Alternately, use the [[#Finding a query to kill|manual process]] below.

=== Permissions required ===

No matter the user interface used, the permission requirements are the same:
Any user can kill their own queries.
Only [https://sokwe.janegoodall.org/doc/tech_spec/architecture/permissions/#the-administrator-permission-levels an administrator] can kill other user's queries.

=== Finding a query to kill ===
To kill such a query, first find it's process number, it's <code>pid</code>.
This is found in the <code>pid</code> column of the following query:

<code>SELECT * FROM pg_stat_activity;</code>

=== Killing the query ===

The following statement kills the query, where <code>MYPID</code> is the pid of the query to be killed:

<code>SELECT pg_terminate_backend(MYPID);</code>

The query may not always immediately stop.
It is best to check that no mistake was made and the process was actually killed.

== Installing Isok, "The Warning System" ==

SokweDB uses [https://kop.codeberg.page/pg_isok_docs Isok] to generate its warnings.
Because SokweDB uses the Microsoft Azure cloud database service to run PostgreSQL, Isok cannot be installed as a PostgreSQL extension. It must be installed as "pure SQL".

Follow the [https://kop.codeberg.page/pg_isok_docs/pg_isok_html/html_paginated/Installation.html#SQL-Install SQL-based installation instructions] in the Isok documentation to build a SQL file for the current version of Isok. Then execute the following code, using the <code>psql</code> command line program, to install. (Using <code>psql</code>'s <code>\i</code> feature, to execute the code from a file, is recommended.)

The exact code to execute, based on the code below, depends on the generated SQL's file name and path. Don't forget to adjust this before execution.

If installing a newer version of Isok, note that this code deletes all the Isok table content, including queries and query results. The content will need to be restored from a data-only dump of the Isok schema.

-- Update isok to the latest version (ish)
-- Destroys all data in the schema!
-- Must be run by an admin user.
-- A psql input file

set role admin;

drop schema isok cascade;

create schema isok;
grant usage on schema isok to reader, writer;

-- This is your Isok cloud installation file, generated by:
-- make TARGET_SCHEMA=isok sql/pg_isok_cloud--1.2.0.sql
-- https://kop.codeberg.page/pg_isok_docs/pg_isok_html/html_paginated/Installation.html
\i isok/pg_isok_cloud--1.2.0.sql

grant select on all tables in schema isok to reader;
grant select on all sequences in schema isok to reader;

grant select, insert, update, delete on all tables in schema isok to writer;
grant select, update, usage on all sequences in schema isok to writer;

grant execute on all functions in schema isok to reader, writer;

grant truncate on all tables in schema isok to writer ;

PostgreSQL Administration

2025-11-10T21:33:31Z

StevanEarl: added grant truncate to isok schema

VPN Setup

2024-02-09T16:46:38Z

StevanEarl: docs: add not about copying ssh with File Explorer

This page describes how to setup a computer so that it runs the SokweDB VPN, allowing programs on the computer to connect directly to the SokweDB database.

The estimated setup time is 15 minutes.
Communication is involved so more than one sitting is required, increasing the total start-to-finish time beyond 15 minutes.
The setup time may vary widely, depending on whether you read the entire section on digital identity, how much time you spend talking with the SokweDB administrator, how testing goes, and whether you setup on multiple computers.
Time must also be spent on downloading, installing, configuring, and testing whatever database-aware programs you wish to have on your computer.

Note that once you have created a digital identity you should [[#Using the VPN From Multiple Computers|re-use it]], on different computers, instead of creating multiple identities.

== Navigating the Installation Process ==

The [[#Initial VPN Setup|Initial VPN Setup]] section can be completed in one sitting, once the SokweDB administrator is in contact so you can get started and agree on a [[#Agree on a VPN Login Name|VPN login name]].
But testing and [[VPN#Using the VPN|VPN use]] will have to wait for the administrator to [[VPN User Management|setup]] the VPN's server-side.

The SokweDB VPN is constructed from the existing components of your computer's Operating System.
This keep-it-simple approach means that there is no installation program, you will need to follow some instructions to get set-up.
These involve some cutting and pasting of entire blocks of command lines, the details of which you need not be concerned.
MS Windows users will also have to use the mouse to create a shortcut icon on the desktop.
Worst-case, someone else can take remote-control of your computer and set things up for you.

When cutting and pasting, cut and paste entire blocks of text as presented in the document, not individual lines.
After pasting into a terminal window, the "Enter" key on the keyboard must be pressed.

To simplify the creation of this page, mouse actions are described in writing.
For example, writing "click on New->Shortcut" to describe the action of clicking the left mouse button on the "New" drop down menu and then clicking the "Shortcut" choice.
You are free to edit this page and provide screenshots if you think it necessary.

Terminal windows must be opened and closed. The next sub-sections describe how to do that.

=== Opening a Terminal Window ===

;On MS Windows
: Click "Start" -> type "wt" -> click "Open"

Older Microsoft Windows installations did not ship Windows Terminal.
Users on older systems can download Windows Terminal from the Microsoft Store:
Click Search -> type "micosoft store" -> click on Microsoft Store -> Search -> type "windows terminal" -> Click the Install/Get button

;On Mac OS/X
: Finder -> Go -> Utilities -> Terminal

=== Closing a Terminal Window ===

;MS Windows
: Close the terminal window.
;Mac OS/X
: Just closing the terminal window does not stop the terminal program. You must "File -> Quit" the terminal program.

== Initial VPN Setup ==

There are 5 steps to initial VPN setup:

# Agree on a VPN login name with your SokweDB administrator
# Create a digital identity
# Email Your Identity to the SokweDB Administrator
# Create a desktop icon
# Approve the VPN connection

=== Agree on a VPN Login Name ===

The VPN requires a login name to authenticate you.
Both you and your SokweDGB administrator must know what it is, although it is written into files so neither of you need remember it after your VPN is set-up.

Contact your SokweDB administrator and agree on a VPN login name.

The name must meet Unix login name conventions.
Best are names less than 32 characters long, beginning with a letter, consisting of only lower case letters, digits, the underscore character, and the dash character.
(No spaces are allowed.)

The VPN login name is needed for the [[#Create a Desktop Icon|desktop icon creation step]].

Having agreed on a VPN login name you can complete the VPN setup on your computer(s).
(But note that until the SokweDB administrator has done the server-side setup you will not be able to use the VPN, instead receiving a "Permission denied (publickey)" message.)

=== Create a Digital Identity ===

If you already have an OpenSSH public/private key pair you don't need another.
Skip this step and go directly to the [[#Create a Desktop Icon|next step]].

==== About Your Digital Identity ====

Digital identities are like regular identities, you only want one. Or at least you only want one of any particular type, just as you might have both a driver's licence and a passport.

Feel free to skip the following sub-section unless you have interest.

===== How it Works =====
The process described here creates an identity known as a public/private [https://en.wikipedia.org/wiki/Public-key_cryptography key pair].
This identity is stored in files in an [https://en.wikipedia.org/wiki/Openssh OpenSSH] compatible data format.
These files contain plain text.

As you will see, this makes it easy to inform others of your identity by cutting and pasting the plain text into emails and the like.
The recipient then knows who you are and can grant your identity access to protected resources.

Briefly, a key pair identity has two parts, a secret part and a public part, each stored in a separate file.
The secret part should be kept safe and never be revealed.
The public part can be shared with anyone, it is what identifies you to the other party.

In our application the public part acts like a lock.
It protects against unauthorized access, preventing people who are not you from accessing database content.
The private part acts like a key, opening the lock and giving you database access.

For added security it is possible to put a password on the secret key part, preventing its use without the password.
We don't require this.
Others do, so if you already have a key pair your secret key may be password protected.

==== Steps to Create a Public/Private Key Pair ====

This section presents text to be cut from here and pasted into a terminal window.
When cutting and pasting, be sure to replace the marked text with appropriate identifying information, like your name.

* [[#Open a Terminal Window|Open a terminal window]].
* Create a Public/Private Key pair on:
** [[Create a Public/Private Key pair on MS Windows|MS Windows]]
** [[Create a Public/Private Key pair on Mac OS/X|Mac OS/X]] (or other systems which support the bash shell)

==== Backup Your Digital Identity (Key Pair) ====

At this point it is a good idea to backup your identity.
The section on [[#Using the VPN From Multiple Computers|installing the VPN on a 2nd computer]] says what files need copying.
The section also, in effect, documents how to restore your identity from a backup.

Again, your private key (file <code>id_ed25519</code>) should be kept secret and secured.
Anyone who has it can impersonate you.
Keep your backup in a safe and secure place.

Note that USB sticks lose their memory if not plugged in every couple of years.

=== Email Your Identity to the SokweDB Administrator ===

Copy and paste your public key into an email to the SokweDB administrator.
It wouldn't hurt to remind them of the [[#Agree on a VPN Login Name|VPN login name]] you have both agreed on.
They will [[VPN User Management|create a VPN user]] and get back to you when the server-side of your VPN is ready to use.

Follow [[#Showing Your Identity (Your Public Key)|these instructions]] if you don't know your public key.

You may now [[#Closing a Terminal Window|close the terminal window]] or otherwise stop the terminal program.

=== Create a Desktop Icon ===

You will use this icon to start the VPN. For convenience we are creating it on your desktop. Feel free to move it elsewhere.

Likewise, the icon image is the default. You may change it as desired.

Create a desktop icon on:
* [[Creating an Icon on MS Windows|MS Windows]]
* [[Creating an Icon on Mac OS/X|Mac OS/X]]

=== Approve the VPN Connection ===

The first time you use the VPN your computer asks to validate that the server connected to is the server which is expected (and not some malicious computer masquerading as same).

* Start the VPN with the icon
* The VPN's terminal window will ask:
<nowiki>The authenticity of host 'sokwe.janegoodall.org (20.119.86.26)' can't be established.
ED25519 key fingerprint is
SHA256:10oDrQQDHNicqZn2BYBWQpUazSBCO6Hvabuz9GRMmqE.
This key is not known by any other names.
Are you sure you want to continue connecting (yes/no/[fingerprint])?</nowiki>

If your display shows the above fingerprint (the part that begins with SHA256:..) there are no malicious actors and you may type <code>yes</code>. (Press the "Enter" key after.)

If the fingerprint is not exactly the same as that above say <code>no</code> and contact your SokweDB administrator.

NOTE: At this point the SokweDB administrator may or may not have set up the server-side of your VPN.
If not, you will get a "Permission denied" message after answering <code>yes</code> or <code>no</code>.

Regardless of whether VPN startup is successful, once you approve the VPN connection by answering <code>yes</code> you will not have to do so again. Or at least not until you install the VPN on another computer.

When done, shut down the VPN and [[#Closing a Terminal Window|close the terminal]].

You have completed the VPN setup. After your SokweDB administrator tells you the server-side is ready, [[VPN#Using the VPN|test]] with your favorite PostgreSQL-aware desktop application.

== Showing Your Identity (Your Public Key) ==

You can always retrieve your public key with the following steps:

* [[#Open a Terminal Window|Open a terminal window]].
* Paste the following into the window and press the "Enter" key on the keyboard:

* On MS Windows:
<nowiki>type $HOME/.ssh/id_ed25519.pub</nowiki>

* On Mac OS/X (or any system supporting <code>bash</code>):
<nowiki>cat ~/.ssh/id_ed25519.pub</nowiki>

The line printed, a long line beginning with "ssh-ed25519", is your public key.

== Using the VPN From Multiple Computers ==

The VPN must be installed on each computer which uses it.
This is usually best done by copying from ''your'' existing installation (not someone else's files).
Don't share your identity or copy someone else's identity files!

Once you have created a digital identity you can re-use that identity on multiple computers. Creating multiple identities leads to confusion and is not recommended.

Take the following steps to use the VPN on another computer.

=== Copy your Identity ===

Copy (from a backup?) the identity you have on your old computer to your new computer.

''Do not reveal your private key!''
Do not use a cloud service or other third party to copy your private key onto another computers.

How to copy files and folders between computers is beyond the scope of this document.
Minimally, you must copy your public and private key files, the <code>id_ed25519</code> '''''and''''' <code>id_ed25519.pub</code> files in your <code>~/.ssh/</code> folder.
But if your second computer does not have a <code>~/.ssh/</code> folder, instead copy the entire folder and its contents.
The folder to copy, where "YOU" is your login name on your computer, is:
;On MS Windows (usually)
: <code>C:\Users\YOU\.ssh\</code>
If you use Windows file explorer, the address bar will look something like <code>This PC > OSDisk (C:) > Users > YOU</code>.
You can also copy the [[#Create a Desktop Icon|desktop icon]] that you created previously.
;On Mac OS/X
: <code>/Users/YOU/.ssh/</code>

Not only must the <code>~/.ssh/</code> folder structure be copied, but the permissions must be copied as well.
If permissions are not copied your private key will not be secure and the VPN will then refuse to use it!
This is a common cause of <code>Permission denied (publickey)</code> errors.
If your copy method does not copy permissions, on Mac OS and Unix-like systems the permissions may be reset manually by running the following command [[#Opening a Terminal Window|in a terminal]]:
<nowiki>chmod g=,o= ~/.ssh ~/.ssh/id_ed25519</nowiki>

It never hurts to run the above command.

=== Get a VPN Icon onto the New Computer ===

Copying the SokweDB VPN's icon between computers should usually work. If it does not, [[#Create a Desktop Icon|create a desktop icon]] from scratch on the new computer.

=== Approve the VPN Connection ===
[[#Approve the VPN Connection|Approve the VPN connection]] on the new computer.

VPN Setup

2024-02-09T16:42:36Z

StevanEarl: /* Copy your Identity */

This page describes how to setup a computer so that it runs the SokweDB VPN, allowing programs on the computer to connect directly to the SokweDB database.

The estimated setup time is 15 minutes.
Communication is involved so more than one sitting is required, increasing the total start-to-finish time beyond 15 minutes.
The setup time may vary widely, depending on whether you read the entire section on digital identity, how much time you spend talking with the SokweDB administrator, how testing goes, and whether you setup on multiple computers.
Time must also be spent on downloading, installing, configuring, and testing whatever database-aware programs you wish to have on your computer.

Note that once you have created a digital identity you should [[#Using the VPN From Multiple Computers|re-use it]], on different computers, instead of creating multiple identities.

== Navigating the Installation Process ==

The [[#Initial VPN Setup|Initial VPN Setup]] section can be completed in one sitting, once the SokweDB administrator is in contact so you can get started and agree on a [[#Agree on a VPN Login Name|VPN login name]].
But testing and [[VPN#Using the VPN|VPN use]] will have to wait for the administrator to [[VPN User Management|setup]] the VPN's server-side.

The SokweDB VPN is constructed from the existing components of your computer's Operating System.
This keep-it-simple approach means that there is no installation program, you will need to follow some instructions to get set-up.
These involve some cutting and pasting of entire blocks of command lines, the details of which you need not be concerned.
MS Windows users will also have to use the mouse to create a shortcut icon on the desktop.
Worst-case, someone else can take remote-control of your computer and set things up for you.

When cutting and pasting, cut and paste entire blocks of text as presented in the document, not individual lines.
After pasting into a terminal window, the "Enter" key on the keyboard must be pressed.

To simplify the creation of this page, mouse actions are described in writing.
For example, writing "click on New->Shortcut" to describe the action of clicking the left mouse button on the "New" drop down menu and then clicking the "Shortcut" choice.
You are free to edit this page and provide screenshots if you think it necessary.

Terminal windows must be opened and closed. The next sub-sections describe how to do that.

=== Opening a Terminal Window ===

;On MS Windows
: Click "Start" -> type "wt" -> click "Open"

Older Microsoft Windows installations did not ship Windows Terminal.
Users on older systems can download Windows Terminal from the Microsoft Store:
Click Search -> type "micosoft store" -> click on Microsoft Store -> Search -> type "windows terminal" -> Click the Install/Get button

;On Mac OS/X
: Finder -> Go -> Utilities -> Terminal

=== Closing a Terminal Window ===

;MS Windows
: Close the terminal window.
;Mac OS/X
: Just closing the terminal window does not stop the terminal program. You must "File -> Quit" the terminal program.

== Initial VPN Setup ==

There are 5 steps to initial VPN setup:

# Agree on a VPN login name with your SokweDB administrator
# Create a digital identity
# Email Your Identity to the SokweDB Administrator
# Create a desktop icon
# Approve the VPN connection

=== Agree on a VPN Login Name ===

The VPN requires a login name to authenticate you.
Both you and your SokweDGB administrator must know what it is, although it is written into files so neither of you need remember it after your VPN is set-up.

Contact your SokweDB administrator and agree on a VPN login name.

The name must meet Unix login name conventions.
Best are names less than 32 characters long, beginning with a letter, consisting of only lower case letters, digits, the underscore character, and the dash character.
(No spaces are allowed.)

The VPN login name is needed for the [[#Create a Desktop Icon|desktop icon creation step]].

Having agreed on a VPN login name you can complete the VPN setup on your computer(s).
(But note that until the SokweDB administrator has done the server-side setup you will not be able to use the VPN, instead receiving a "Permission denied (publickey)" message.)

=== Create a Digital Identity ===

If you already have an OpenSSH public/private key pair you don't need another.
Skip this step and go directly to the [[#Create a Desktop Icon|next step]].

==== About Your Digital Identity ====

Digital identities are like regular identities, you only want one. Or at least you only want one of any particular type, just as you might have both a driver's licence and a passport.

Feel free to skip the following sub-section unless you have interest.

===== How it Works =====
The process described here creates an identity known as a public/private [https://en.wikipedia.org/wiki/Public-key_cryptography key pair].
This identity is stored in files in an [https://en.wikipedia.org/wiki/Openssh OpenSSH] compatible data format.
These files contain plain text.

As you will see, this makes it easy to inform others of your identity by cutting and pasting the plain text into emails and the like.
The recipient then knows who you are and can grant your identity access to protected resources.

Briefly, a key pair identity has two parts, a secret part and a public part, each stored in a separate file.
The secret part should be kept safe and never be revealed.
The public part can be shared with anyone, it is what identifies you to the other party.

In our application the public part acts like a lock.
It protects against unauthorized access, preventing people who are not you from accessing database content.
The private part acts like a key, opening the lock and giving you database access.

For added security it is possible to put a password on the secret key part, preventing its use without the password.
We don't require this.
Others do, so if you already have a key pair your secret key may be password protected.

==== Steps to Create a Public/Private Key Pair ====

This section presents text to be cut from here and pasted into a terminal window.
When cutting and pasting, be sure to replace the marked text with appropriate identifying information, like your name.

* [[#Open a Terminal Window|Open a terminal window]].
* Create a Public/Private Key pair on:
** [[Create a Public/Private Key pair on MS Windows|MS Windows]]
** [[Create a Public/Private Key pair on Mac OS/X|Mac OS/X]] (or other systems which support the bash shell)

==== Backup Your Digital Identity (Key Pair) ====

At this point it is a good idea to backup your identity.
The section on [[#Using the VPN From Multiple Computers|installing the VPN on a 2nd computer]] says what files need copying.
The section also, in effect, documents how to restore your identity from a backup.

Again, your private key (file <code>id_ed25519</code>) should be kept secret and secured.
Anyone who has it can impersonate you.
Keep your backup in a safe and secure place.

Note that USB sticks lose their memory if not plugged in every couple of years.

=== Email Your Identity to the SokweDB Administrator ===

Copy and paste your public key into an email to the SokweDB administrator.
It wouldn't hurt to remind them of the [[#Agree on a VPN Login Name|VPN login name]] you have both agreed on.
They will [[VPN User Management|create a VPN user]] and get back to you when the server-side of your VPN is ready to use.

Follow [[#Showing Your Identity (Your Public Key)|these instructions]] if you don't know your public key.

You may now [[#Closing a Terminal Window|close the terminal window]] or otherwise stop the terminal program.

=== Create a Desktop Icon ===

You will use this icon to start the VPN. For convenience we are creating it on your desktop. Feel free to move it elsewhere.

Likewise, the icon image is the default. You may change it as desired.

Create a desktop icon on:
* [[Creating an Icon on MS Windows|MS Windows]]
* [[Creating an Icon on Mac OS/X|Mac OS/X]]

=== Approve the VPN Connection ===

The first time you use the VPN your computer asks to validate that the server connected to is the server which is expected (and not some malicious computer masquerading as same).

* Start the VPN with the icon
* The VPN's terminal window will ask:
<nowiki>The authenticity of host 'sokwe.janegoodall.org (20.119.86.26)' can't be established.
ED25519 key fingerprint is
SHA256:10oDrQQDHNicqZn2BYBWQpUazSBCO6Hvabuz9GRMmqE.
This key is not known by any other names.
Are you sure you want to continue connecting (yes/no/[fingerprint])?</nowiki>

If your display shows the above fingerprint (the part that begins with SHA256:..) there are no malicious actors and you may type <code>yes</code>. (Press the "Enter" key after.)

If the fingerprint is not exactly the same as that above say <code>no</code> and contact your SokweDB administrator.

NOTE: At this point the SokweDB administrator may or may not have set up the server-side of your VPN.
If not, you will get a "Permission denied" message after answering <code>yes</code> or <code>no</code>.

Regardless of whether VPN startup is successful, once you approve the VPN connection by answering <code>yes</code> you will not have to do so again. Or at least not until you install the VPN on another computer.

When done, shut down the VPN and [[#Closing a Terminal Window|close the terminal]].

You have completed the VPN setup. After your SokweDB administrator tells you the server-side is ready, [[VPN#Using the VPN|test]] with your favorite PostgreSQL-aware desktop application.

== Showing Your Identity (Your Public Key) ==

You can always retrieve your public key with the following steps:

* [[#Open a Terminal Window|Open a terminal window]].
* Paste the following into the window and press the "Enter" key on the keyboard:

* On MS Windows:
<nowiki>type $HOME/.ssh/id_ed25519.pub</nowiki>

* On Mac OS/X (or any system supporting <code>bash</code>):
<nowiki>cat ~/.ssh/id_ed25519.pub</nowiki>

The line printed, a long line beginning with "ssh-ed25519", is your public key.

== Using the VPN From Multiple Computers ==

The VPN must be installed on each computer which uses it.
This is usually best done by copying from ''your'' existing installation (not someone else's files).
Don't share your identity or copy someone else's identity files!

Once you have created a digital identity you can re-use that identity on multiple computers. Creating multiple identities leads to confusion and is not recommended.

Take the following steps to use the VPN on another computer.

=== Copy your Identity ===

Copy (from a backup?) the identity you have on your old computer to your new computer.

''Do not reveal your private key!''
Do not use a cloud service or other third party to copy your private key onto another computers.

How to copy files and folders between computers is beyond the scope of this document.
Minimally, you must copy your public and private key files, the <code>id_ed25519</code> '''''and''''' <code>id_ed25519.pub</code> files in your <code>~/.ssh/</code> folder.
But if your second computer does not have a <code>~/.ssh/</code> folder, instead copy the entire folder and its contents.
The folder to copy, where "YOU" is your login name on your computer, is:
;On MS Windows (usually)
: <code>C:\Users\YOU\.ssh\</code>
Alternatively, if you use Windows file explorer, the address bar will look something like <code>This PC > OSDisk (C:) > Users > YOU</code>.
You can also copy the [[#Creating_an_Icon_on_MS_Windows|Desktop icon]] that you created previously.
;On Mac OS/X
: <code>/Users/YOU/.ssh/</code>

Not only must the <code>~/.ssh/</code> folder structure be copied, but the permissions must be copied as well.
If permissions are not copied your private key will not be secure and the VPN will then refuse to use it!
This is a common cause of <code>Permission denied (publickey)</code> errors.
If your copy method does not copy permissions, on Mac OS and Unix-like systems the permissions may be reset manually by running the following command [[#Opening a Terminal Window|in a terminal]]:
<nowiki>chmod g=,o= ~/.ssh ~/.ssh/id_ed25519</nowiki>

It never hurts to run the above command.

=== Get a VPN Icon onto the New Computer ===

Copying the SokweDB VPN's icon between computers should usually work. If it does not, [[#Create a Desktop Icon|create a desktop icon]] from scratch on the new computer.

=== Approve the VPN Connection ===
[[#Approve the VPN Connection|Approve the VPN connection]] on the new computer.

Maintaining Python Virtual Environments

2023-12-16T16:52:48Z

StevanEarl: add reminder to update requirements file after upgrading venvs

== Revision Control Support ==
Tools to create and manage Python [https://docs.python.org/3/library/venv.html virtual environments] are in the [https://sokwe.janegoodall.org/git/?p=sokwe_server;a=tree sokwe_server code repository].

After checking out the repository, make the repo your current working directory and get help with:
<nowiki>make help</nowiki>

== Package Update ==
To update a particular package in a venv to the latest version (run from a regular developer login, not root) follow the instructions found in the [https://sokwe.janegoodall.org/git/?p=sokwe_server;a=summary sokwe_server] repository's [https://sokwe.janegoodall.org/git/?p=sokwe_server;a=blob_plain;f=Makefile;hb=HEAD Makefile].
The instructions are presented by running:

<pre>make | less</pre>

After upgrading a package, update the corresponding requirements file then be sure to commit the change to the <code>sokwe_server</code> code repository.
The instructions make presents explain how.
This allows others, perhaps a future-you, to re-create an identical environment.

It is also safe to delete the entire virtual environment and re-create it with the latest available packages, although this will lead to interruption of service.
Again, the <code>sokwe_server</code> repository should be updated to record the new package versions.

== Service Restart ==

After a change to the virtual environment all daemons which use the virtual environment must be restarted. This varies by virtual environment.

=== PgAdmin4 ===
<nowiki>systemctl restart pgadmin4_gnunicorn</nowiki>

Create a Public/Private Key pair on MS Windows

2023-11-22T22:15:51Z

StevanEarl:

The following steps create a public/private key pair (a digital identity) on Microsoft Windows:

* Supply your name and address. Type the following into the terminal window and press the "Enter" key, substituting your name and email in the requisite spot. If you make a mistake, type it again.
<nowiki>$NAME_EMAIL="First Last <myself@example.com>"</nowiki>

* Show your digital identity (the public key half), creating an identity if it does not exist. Cut and paste the following blocks of commands into the terminal window. Press the "Enter" key after pasting.
<nowiki>if (!(Test-Path -Path $HOME\.ssh)) {
New-Item -Path $HOME\.ssh -ItemType Directory
}
if (!(Test-Path -Path $HOME\.ssh\id_ed25519)) {
ssh-keygen.exe -t ed25519 -f $HOME\.ssh\id_ed25519 -a 100 -C ${NAME_EMAIL} -N '""'
}</nowiki>

Your terminal window will afterward contain text similar to this:

<nowiki>Your identification has been saved in C:\Users\myself\.ssh\id_ed25519
Your public key has been saved in C:\Users\myself\.ssh\id_ed25519.pub
The key fingerprint is:
SHA256:RS/EcUuHR8w75KpvXoWj/2xNzMBnkAMhFYImOQMLUes First Last <myself@example.com>
The key's randomart image is:
+--[ED25519 256]--+
| ooo. . o*oBOo. |
| . o= oo.*.oO |
| o = o o= + |
| . . . *.o|
| E S .oB.|
| .. o+|
| .. ...|
| . .o .o|
| +o .oo|
+----[SHA256]-----+
</nowiki>

Lastly, cut and paste the following text and press the "Enter" key:

<nowiki>type $HOME\.ssh\id_ed25519.pub</nowiki>

The output from should look similar to this:

<nowiki>ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIEay3Ro8xHXJrxfDbhm5jUSqZ3tHjXLBzhcLbIaVwNIT First Last <myself@example.com></nowiki>

That long line beginning with "ssh-ed25519" and ending with your name and email is your public key. You're going to copy and paste the whole line into an email
in the step after next.
----
[[VPN Setup#Steps to Create a Public/Private Key Pair|Back to the VPN Setup page]]

Creating an Icon on MS Windows

2023-11-18T00:22:41Z

StevanEarl:

Create a icon to start the SokweDB VPN on your Microsoft Windows desktop with the following procedure:
----
* Press the right-side mouse button on an empty portion of your desktop.
* Click on New -> Shortcut in the resulting menu
* Enter <code>wt ssh -L 5432:sokwe-dbs.postgres.database.azure.com:5432 -l YOU sokwe.janegoodall.org</code> in the "Type the location of the item:" field
* Change the "YOU" in the line above to the VPN login name that you and the SowkeDB administrator [[#Agree on a VPN Login Name|agreed upon]].
* Click the "Next" button
* Enter "SokweDB_VPN" as the name of the shortcut
* Click the "Finish" button

----
Now go back to the [[VPN Setup#Create a Desktop Icon|VPN Setup]] page.

Create a Public/Private Key pair on MS Windows

2023-11-15T23:25:35Z

StevanEarl: adjust Windows instructions for Powershell

The following steps create a public/private key pair (a digital identity) on Microsoft Windows:

* Supply your name and address. Type the following into the terminal window and press the "Enter" key, substituting your name and email in the requisite spot. If you make a mistake, type it again.
<nowiki>$NAME_EMAIL="First Last <myself@example.com>"</nowiki>

* Show your digital identity (the public key half), creating an identity if it does not exist. Cut and paste the following block of commands into the terminal window. Press the "Enter" key after pasting.
<nowiki>if (!(Test-Path -Path $HOME\.ssh)) { `
New-Item -Path $HOME\.ssh -ItemType Directory `
} `
if (!(Test-Path -Path $HOME\.ssh\id_ed25519)) { `
ssh-keygen.exe -t ed25519 -f $HOME\.ssh\id_ed25519 -a 100 -C ${NAME_EMAIL} `
} `
type $HOME\.ssh\id_ed25519.pub</nowiki>

Your terminal window will afterward contain text similar to this:

<nowiki>Your identification has been saved in C:\Users\myself\.ssh\id_ed25519
Your public key has been saved in C:\Users\myself\.ssh\id_ed25519.pub
The key fingerprint is:
SHA256:RS/EcUuHR8w75KpvXoWj/2xNzMBnkAMhFYImOQMLUes First Last <myself@example.com>
The key's randomart image is:
+--[ED25519 256]--+
| ooo. . o*oBOo. |
| . o= oo.*.oO |
| o = o o= + |
| . . . *.o|
| E S .oB.|
| .. o+|
| .. ...|
| . .o .o|
| +o .oo|
+----[SHA256]-----+
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIEay3Ro8xHXJrxfDbhm5jUSqZ3tHjXLBzhcLbIaVwNIT First Last <myself@example.com>
</nowiki>

The last line printed, one long line beginning with "ssh-ed25519" and ending with your name and email, is your public key. You're going to copy and paste the whole line into an email
in the step after next.

Build System

2023-10-12T17:50:38Z

StevanEarl: new page for build system

This page contains notes, examples, and instructions on working with the system that installs software and database structures.

Development

2023-10-12T17:47:38Z

StevanEarl: adding a new page for build system notes

All changes to database structure, database documentation, bespoke programs, etc. should under revision control -- committed to the SokweDB master software repository.

== Working with the SokweDB Master Software Repository ==

The [https://{{SERVERNAME}}/git/?p=sokwedb SokweDB master software repository] may be browsed on the web.

To push or pull from the master repo while on the <code>sokwe</code> server, use the respository's path to identify it. E.g.:

<code>git clone /srv/repos/sokwedb</code>
<code>git pull /srv/repos/sokwedb</code>
<code>git push /srv/repos/sokwedb</code>

Anyone may pull from the master repo over the Internet, with:

<code>git pull https://sokwe.janegoodall.org/repos/sokwedb</code>

The same URL may be used when cloning.

You can perform any operation on the master git repository with <code>ssh</code> and your login.
E.g. to push to the master repo over the Internet, use:

<code>git push ssh://YOURLOGIN@sokwe.janegoodall.org/srv/repos/sokwedb</code>

== Collaborating on Code; Making Public Repositories ==

One way to let other people see what you're working on is by creating a publicly available code repository. Public repositories on the SokweDB server act like public repositories on other git hosting services such as GitHub, GitLab, etc., although the web-browser facing interface and associated toolkits differs from service to service. A public repository, no matter the server, is useful when you develop on more than one machine and need to move code between them.

Create a public repository with:

<code>git init --bare /srv/repos/YOURREPONAME</code>

The <code>/srv/repos/</code> directory holds all the shared repositories.
Repositories in this directory are shared on the server, and publicly available to the word for browsing via the SokweDB website.

The advantage of sharing is the ability to view each other's work, work on each other's branches, etc., without having to make changes to the master repository.

Note that the repositories in </code>/srv/repos/</code> must be bare. This means they contain only revision history and can't be used for development. You will need your own repository(ies), in your home directory or elsewhere, for development purposes.

=== Permissions on Shared Repositories ===

All shared repositories, those in <code>/srv/repos</code>, including the SokweDB master software repository, are readable and writable by all [[Unix_Administration#wwwdev|developers]].

Please take care when making changes to the master software repository.

It is improper etiquette to alter someone else's public repository without their permission.

=== Tips on Best Practices for Upstream Repositories ===

==== Avoid Accidental Pushes to the Master Repository ====

It is best to have your public repository be the default place to which you push changes. That way you don't accidentally push unproven code to the master repository.

This is most easily accomplished by:

# Creating your public repository in <code>/srv/repos</code>, as explained above.
# Cloning that repository to create your working repository, in which development is done.
# Pulling from the master repository to populate your working repository.

==== Name the Remote Repositories ====

Typing a long URL when interacting with the SokweDB repositories can be tedious.
Use <code>git remote</code> to assign a short name to, e.g., the master repository:

<code>git remote add blessed ssh://YOURLOGIN@sokwe.janegoodall.org/srv/repos/sokwedb</code>

With this you can <code>git push blessed master</code>, etc., from your local repository to push and pull from <code>blessed</code> when interacting with the SokweDB master repository.

Note that, normally, your remote interactions are only with "bare" repositories, the repositories that are used only for code storage and not for actual development or deployment. Repositories in your home directory, on whatever machine, are normally ''not'' bare, and are where development or installation is done.

It is also useful to name the repository in <code>/srv/repos/</code> on the sokwe server which you use as a personal push/pull endpoint.

Renaming with <code>git remote rename ...</code> is also possible; if you made your repo by cloning the master then you automatically created the name <code>origin</code> to refer to the master repository. Unless you're a senior developer, <code>origin</code> should probably refer to your personal <code>sokwe.janegoodall.org</code> public repository.

== The Build System ==

add text

details on working with the build system can be found [[BuildSystem|here]].