Unable to LOAD_EXTENSION ('mod_virtualpg.so') - Linux Ubuntu

[Arooj Ahmed]

Hi,

I am new to VirtualPG and Spatialite.

I need guidance related to my project. Will be grateful, if you can share your expert opinion:

- I have to setup data sharing mechanism between server (PostgreSQL+postgis with Geodjango as a web server) and client (Android app with local Spatialite database).

- Idea is that whenever, there is new version of database on the server side, it should be updated on the client side.

- Client is an offline application. So whenever there is a network connection, updated version of the server database (PostgreSQL + postgis) should be uploaded on the client side database (Spatialite).

I found virtualpg relevant as it claims to streamline the data exchange between above mentioned Spatial Database.

I have postgis and PostgreSQL installed on Ubuntu.

I have spatialite 3.19.3

with virtualpg 1.0

unable to execute this command in SQLite "SELECT load_extension('mod_virtualpg');"

Error Message is : cannot open shared object file: No such file or directory.

I tried this command as well (recommended in virtualpg tutorial)

SELECT load_extension ('usr/local/lib/mod_virtualpg.so', 'sqlite3_virtualpg_init');

Error Message: undefined symbol "sqlite3_virtualpg_init"

[a.fu...@lqt.it]

On Wed, 28 Jun 2017 08:02:11 -0700 (PDT), Arooj Ahmed wrote:
> Hi,
> I am new to VirtualPG and Spatialite.
> I need guidance related to my project. Will be grateful, if you can
> share your expert opinion:
>
> - I have to setup data sharing mechanism between server
> (PostgreSQL+postgis with Geodjango as a web server) and client
> (Android app with local Spatialite database).
> - Idea is that whenever, there is new version of database on the
> server side, it should be updated on the client side.
> - Client is an offline application. So whenever there is a network
> connection, updated version of the server database (PostgreSQL +
> postgis) should be uploaded on the client side database (Spatialite).
>

Hi Arooj,

what you are imagining is intrinsically unsafe, and opens
the doors to many potentially dangerous security breaches.

a solution like this implies establishing a direct SQL
connection between the PostgreSQL server (listening on
IP port 5432) and the Android client.
the standard Postgres configuration usually forbids
accepting connections coming from other machines (only
connections coming from the same machine hosting the
DBMS are accepted).
you could eventually manually change the Postgres
config files so to accept external connections (not
an easy task), and at the same time you must relax the
firewall rules so to enable IP port 5432 to external
traffic.
but in this case you should be well conscious that
this way you are posing at serious risk the server's
overall security.
this is because an eventual hacker could easily
exploit the SQL port so to trigger a malicious system
wide attack based on SQL injection ... very few
system administrators or DBMS administrators will be
happy to authorize a so dangerous and intrinsically
unsafe configuration.

> I found virtualpg relevant as it claims to streamline the data
> exchange between above mentioned Spatial Database.
>

certainly yes: but the expected configurations that
can be safely adopted without any harm are only the
following two:
1. the spatialite process runs in the same server
hosting Postgres.
2. the spatialite process runs in a different
machine than Postgres, but both them are
connected to the same LAN (local area
network). and in this case the firewall _MUST_
be carefully configured so to reject all
connections coming from external networks
(=Internet).

the correct and safe solution to support the synchronization
of some mobile device connected via the unsafe Internet
usually implies developing some ad hoc WebService.
it would probably imply a strong development effort,
but if correctly implemented it will be an absolutely
robust, safe and harmless solution.

> I have postgis and PostgreSQL installed on Ubuntu.
> I have spatialite 3.19.3
> with virtualpg 1.0
>
> unable to execute this command in SQLite "SELECT

> LOAD_EXTENSION('MOD_VIRTUALPG');"
> ERROR MESSAGE IS : cannot open shared object file: No such file or
> directory.
>

if, as I understand, your spatialite process runs on
Android you should install VirtualPG on Android as
well; but you probably have first to build and install
a binary copy of VirtualPG on your Android.

bye Sandro