Guard uses of role pg_signal_backend: changed in pg9.6 (N)

[smurthys]

Making ClassDB compatible with Postgres versions prior to 9.6 (back up to 9.3 to be specific) requires performing the following operations only if the server role pg_signal_backend exists:

• In initalizeServerCore.sql, grant pg_signal_backend to role ClassDB
• In addConnectionMgmtReco.sql, make ClassDB the owner of function kilUserConnections

But for the comments in code about the conditional execution, no other documentation change should be necessary.

[KevinKelly25]

Through some more testing with 9.3 I have found another issue.

ALTER SYSTEM which is used 4 times in enableConnectionLogging.psql is not possible since ALTER SYSTEM was not introduced until postgres 9.4.

I believe the only way to have that same functionality in 9.3 is to manually change postgresql.conf

[smurthys]

Good find @KevinKelly25. I have created a new issue #228 to track this change. I also created an epic #227 to track all changes related to compatibility with Postgres versions prior to 9.6.

PS: Please mark all issues related to compatibility with pre 9.6 versions of Postgres as enhancement and make it part of the aforementioned epic.

[afig]

Although pg_signal_backend was not available prior to 9.6, the same functionality is available as a superuser. Since we do not wish to make the classdb role a superuser, a good alternative is to simply make the superuser running the script (usually postgres) the owner of functions that implement the functionality required by pg_signal_backend (currently only one function).

There are two items to guard/perform conditionally:

Only grant pg_signal_backend to the classdb role for versions >= 9.6 (initializeServerCore.sql L69). EDIT: Note that due to the fact that this is a server script, ClassDB's functions related to comparing server versions cannot be used, and instead a documented and easily found use of manual version comparison needs to be used.

Only alter the function owner of ClassDB.killUserConnections to be classdb for versions >= 9.6 (addConnectionMgmtReco.sql L109-111).

[KevinKelly25]

After doing some version checking I noticed upon using removeAllFromDB.sql that the following error occurred.

psql:removeAllFromDB.sql:108: ERROR:  cannot drop desired object(s) because other objects depend on them
DETAIL:  function classdb.killconnection(integer) depends on schema classdb

Function classdb.killconnection(integer) needs to be dropped in removeAllFromDB.sql for ClassDB database removal to properly work.