prayer-generator

Given a postgresql database created as ``pgdb``
And a postgresql user created as ``pgdb``
And pljava extension installed

Ensure at least one SWORD module installed

See https://crosswire.org/ and specifically https://crosswire.org/sword/index.jsp.

On Debian system, the KJV can be installed as follows

sudo apt-get install sword-text-kjv

Or install xiphos and download the modules from there

sudo apt-get install xiphos

NOTE: You may need to place the modules in the postgresql directory and with correct permissions.

As privileged user

# sudo su
mkdir -p /var/lib/postgresql/.sword/mods.d
chown postgres:postgres /var/lib/postgresql/.sword
chmod 0755 /var/lib/postgresql/.sword
cp ~/.sword/mods.d/* /var/lib/postgresql/.sword/mods.d
chown -R postgres:postgres /var/lib/postgresql/.sword/mods.d
chmod 0755 /var/lib/postgresql/.sword/mods.d
chmod -R 0644 /var/lib/postgresql/.sword/mods.d

Ensure java policy allows JSword to function

Need to tweak the default policy shipped with PlJava to allow JSword to search for module directory.

As privileged user

mv /etc/postgresql-common/pljava.policy{,.bak}
cat <<'EOF' > /etc/postgresql-common/pljava.policy
//
// Security policy for PL/Java. These grants are intended to add to those
// contained in the java.policy file of the standard Java installation.
//


//
// This grant is unconditional. It adds these properties to the standard Java
// list of system properties that any code may read.
//
grant {
	// "standard" properties that can be read by anyone, by analogy to the
	// ones so treated in Java itself.
	//
	permission java.util.PropertyPermission
		"org.postgresql.version", "read";
	permission java.util.PropertyPermission
		"org.postgresql.pljava.version", "read";
	permission java.util.PropertyPermission
		"org.postgresql.pljava.native.version", "read";

	permission java.util.PropertyPermission
		"org.postgresql.pljava.udt.byteorder.*", "read";

	permission java.util.PropertyPermission
		"org.postgresql.server.encoding", "read";

	// PostgreSQL allows SELECT current_database() or SHOW cluster_name anyway.
	//
	permission java.util.PropertyPermission
		"org.postgresql.database", "read";
	permission java.util.PropertyPermission
		"org.postgresql.cluster", "read";

	// SQL/JRT specifies this property.
	//
	permission java.util.PropertyPermission
		"sqlj.defaultconnection", "read";

	// This property is read in the innards of Java 9 and 10, but they forgot
	// to add a permission for it. Not needed for Java 11 and later.
	//
	permission java.util.PropertyPermission
		"jdk.lang.ref.disableClearBeforeEnqueue", "read";

	// Something similar happened in Java 14 (not yet fixed in 15).
	//
	permission java.util.PropertyPermission
		"java.util.concurrent.ForkJoinPool.common.maximumSpares", "read";
};


//
// This grant is specific to the internal implementation of PL/Java itself,
// which needs these permissions for its own operations.
//
// Historically, PL/Java has been able to read any file on the server filesystem
// when a file: URL is passed to sqlj.install_jar or sqlj.replace_jar. Such a
// broad grant is not necessary, and can be narrowed below if desired.
//
grant codebase "${org.postgresql.pljava.codesource}" {
	permission java.lang.RuntimePermission
		"charsetProvider";
	permission java.lang.RuntimePermission
		"createClassLoader";
	permission java.lang.RuntimePermission
		"getProtectionDomain";
	permission java.net.NetPermission
		"specifyStreamHandler";
	permission java.util.logging.LoggingPermission
		"control";
	permission java.security.SecurityPermission
		"createAccessControlContext";

	// This gives the PL/Java implementation code permission to read
	// any file, which it only exercises on behalf of sqlj.install_jar()
	// or sqlj.replace_jar() when called with a file: URL.
	//
	// There would be nothing wrong with restricting this permission to
	// a specific directory, if all jar files to be loaded will be found there,
	// or replacing it with a URLPermission if they will be hosted on a remote
	// server, etc.
	//
	permission java.io.FilePermission
		"<<ALL FILES>>", "read";
};


//
// This grant defines the mapping onto Java of PostgreSQL's "trusted language"
// category. When PL/Java executes a function whose SQL declaration names
// a language that was declared WITH the TRUSTED keyword, it will have these
// permissions, if any (in addition to whatever others might be granted to all
// code, or to its specific jar, etc.).
//
grant principal org.postgresql.pljava.PLPrincipal$Sandboxed * {
	permission java.lang.RuntimePermission
		"createClassLoader";
        permission java.lang.RuntimePermission
                "getClassLoader";
        permission java.util.PropertyPermission
        	"user.home", "read";
        permission java.util.PropertyPermission
        	"sword.home", "read";
        permission java.util.PropertyPermission
        	"jsword.home", "read";
	permission java.io.FilePermission
		"/usr/share/sword", "read";
	permission java.io.FilePermission
		"/usr/share/sword/-", "read";
	permission java.io.FilePermission
		"/usr/share/sword/mods.d", "read,write";
	permission java.io.FilePermission
		"/usr/share/sword/mods.d/-", "read,write";
	permission java.io.FilePermission
		"/var/lib/postgresql", "read";
	permission java.io.FilePermission
		"/var/lib/postgresql/.jsword", "read";
	permission java.io.FilePermission
		"/var/lib/postgresql/.jsword/-", "read";
	permission java.io.FilePermission
		"/var/lib/postgresql/.sword", "read";
	permission java.io.FilePermission
		"/var/lib/postgresql/.sword/-", "read";
	permission java.io.FilePermission
		"/var/lib/postgresql/.sword/mods.d", "read,write";
	permission java.io.FilePermission
		"/var/lib/postgresql/.sword/mods.d/-", "read,write";
	permission java.io.FilePermission
		"./sword.conf", "read";
	permission java.io.FilePermission
		"./mods.d", "read";
	permission java.io.FilePermission
		"../library/mods.d", "read";
	permission java.io.FilePermission
		"/etc/sword.conf", "read";
	permission java.io.FilePermission
		"/usr/local/etc/sword.conf", "read";
	//permission java.io.FilePermission
	//	"<<ALL FILES>>", "read";
};


//
// This grant defines the mapping onto Java of PostgreSQL's "untrusted language"
// category. When PL/Java executes a function whose SQL declaration names
// a language that was declared WITHOUT the TRUSTED keyword, it will have these
// permissions (in addition to whatever others might be granted to all code, or
// to its specific jar, etc.).
//
grant principal org.postgresql.pljava.PLPrincipal$Unsandboxed * {
	// Java does not circumvent operating system access controls; this grant
	// will still be limited to what the OS allows a PostgreSQL backend process
	// to do.
	permission java.io.FilePermission
		"<<ALL FILES>>", "read,readlink,write,delete";
};


//
// This grant applies to a specific PL/Java sandboxed language named java_tzset
// (if such a language exists) and grants functions created in that language
// permission to adjust the time zone. There is an example method in the
// org.postgresql.pljava.example.annotation.PreJSR310 class, which needs to
// temporarily adjust the time zone for a test. That example also uses
// sqlj.alias_java_language to create the java_tzset "language" when deployed,
// and DROP LANGUAGE to remove it when undeployed.
//
grant principal org.postgresql.pljava.PLPrincipal$Sandboxed "java_tzset" {
	permission java.util.PropertyPermission "user.timezone", "write";
};
EOF

Create the sqlj schema

Creates a schema for pljava extension to use.

Imports the dependencies for jsword and pljava-jsword.

Authorizes pgdb user to access the pljava-jsword functions.

As postgres user

NOTE: java jar file location hard-coded to /home/pi/bin in create-pljava-jsword

# sudo su postgres
psql -d pgdb -1 < ./init-privileged.sql

Test everything is working

Sanity check the pljava-jsword is working.

Script should exit with zero exit code. Non-zero exit code indicates failure.

NOTE: requires the KJV SWORD module to be installed.

As unprivileged user

./test-pljava-jsword.sh

Create the pgdb schema

Creates the tables, functions, triggers, and views that form the core of the app.

As unprivileged user

psql -d pgdb -1 < ./init.sql

Add the data

Inserts all the data from the CSV files.

As unprivileged user

psql -d pgdb -1 < ./import-data.sql

Check the default module is installed

A default module is required as fallback if the specified translation is unavailable.

The default translation can be determined with a pljava-jsword stored procedure call.

As unprivileged user

psql -d pgdb -c 'SELECT * FROM getdefaulttranslation();'

Generate prayer

Generates a table of scriptures.

One scripture for each of the sections of the model prayer outlined in Matthew 6:9-13 and Luke 11:2-4.

Each scripture is randomly selected from the pool of scriptures in each section.

As unprivileged user

psql -d pgdb -c 'SELECT * FROM pg_random_view_default_if_null;'

Example output

 category  | translation |         reference         |                                                                                                                                                                                                                            txt                                                                                                                                                                                                           
-----------+-------------+---------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 name      | KJV         | 1 Corinthians 15:23       | But every man in his own order: Christ the firstfruits; afterward they that are Christ’s at his coming.
 z_kingdom | KJV         | 1 John 4:3                | And every spirit that confesseth not that Jesus Christ is come in the flesh is not of God: and this is that spirit of antichrist, whereof ye have heard that it should come; and even now already is it in the world.
 a_kingdom | KJV         | Revelation of John 20:1-3 | And I saw an angel come down from heaven, having the key of the bottomless pit and a great chain in his hand. And he laid hold on the dragon, that old serpent, which is the Devil, and Satan, and bound him a thousand years, And cast him into the bottomless pit, and shut him up, and set a seal upon him, that he should deceive the nations no more, till the thousand years should be fulfilled: and after that he must be loosed a little season.
 will      | MKJV        | John 11:40                | Jesus answered her, Did I not say to you that if you would believe you would see the glory of God?
 bread     | KJV         | 1 John 5:14               | And this is the confidence that we have in him, that, if we ask any thing according to his will, he heareth us:
 because   | MKJV        | Matthew 6:33              | But seek first the kingdom of God and His righteousness; and all these things shall be added to you.
 debtors   | KJV         | 1 Corinthians 6:9-10      | Know ye not that the unrighteous shall not inherit the kingdom of God? Be not deceived: neither fornicators, nor idolaters, nor adulterers, nor effeminate, nor abusers of themselves with mankind, Nor thieves, nor covetous, nor drunkards, nor revilers, nor extortioners, shall inherit the kingdom of God.
 tempt     | ESV         | Deuteronomy 6:6-8         | And these words that I command you today shall be on your heart. You shall teach them diligently to your children, and shall talk of them when you sit in your house, and when you walk by the way, and when you lie down, and when you rise. You shall bind them as a sign on your hand, and they shall be as frontlets between your eyes.
 deliver   | MKJV        | Luke 21:36                | Watch therefore, praying in every season that you may be counted worthy to escape all these things which shall occur, and to stand before the Son of man.
 glory     | ESV         | Psalms 150:6              | Let everything that has breath praise the Lord! Praise the Lord!
(10 rows)