Project Activity #11286

Project WP #641: WP5 - Supporting Blue Assessment: VREs Development [Months: 1-30]

Project Task #642: T5.1 Stock Assessment VRE [Months: 1-30]

Project Activity #1674: VRE Stock Assessment Workplan

Project Activity #5135: WECAFC-FIRMS

Project Activity #11214: WECAFC-FIRMS data / map dissemination tools

WECAFC-FIRMS - Enable PostGIS spatial extension

Added by Emmanuel Blondel over 1 year ago. Updated over 1 year ago.

Status:ClosedStart date:Feb 22, 2018
Priority:HighDue date:
Assignee:Kostas Kakaletris% Done:

100%

Sprint:WP05
Participants:
Milestones:
Duration:

Description

I'm setting the scope to both pre-production and production, as in any case final map dissemination workflow has to be plugged on the production database.

FYI, I need to use the database connection from 2 interfaces:
- simple SELECT queries from R connecting to the database
- connection through GeoServer datastore. For this I've requested CNR in separate ticket to configure JNDI connector

History

#1 Updated by Pasquale Pagano over 1 year ago

  • Tracker changed from Support to Project Activity

#2 Updated by Kostas Kakaletris over 1 year ago

Dear @emmanuel.blondel@fao.org,
I installed postgis packages on the preproduction server and enabled postgis extension on preproduction database. Bellow are the versions of postgres and postgis and the enabled extensions on the database:
- PostgreSQL 9.5.11
- Postgis full version POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" RASTER

Currently enabled extensions on the database:
name | default_version | installed_version
---------+-----------------+-------------------
postgis | 2.2.1 | 2.2.1
plpgsql | 1.0 | 1.0

Are there any other extensions that you may need to be enabled on the database? (postgis_topology, uuid-ossp, etc?)

b) About the database connections, if I understand it correctly, you mean that those other services/apps (R, geoserver, etc) will connect to this database for read only access using the read only database service endpoint (of course after importing the shapes that you need). Is that correct? If yes then I believe that this is done, ie the read only user service endpoint for preproduction database is created on the IS (WECAFCRegionalDataCollection under preprod/preVRE and preprod/preECO)

#3 Updated by Kostas Kakaletris over 1 year ago

  • % Done changed from 0 to 30

#4 Updated by Emmanuel Blondel over 1 year ago

for a) it's perfect like this, thanks for your prompt action.
for b) I need DB standard connection params: host,dbname/schema, port, user, pwd ; Otherwise i won't be able to connect to DB through R. I'm not sure that I can find this information on the IS. For Geoserver it will be different: CNR will need to these params to configure the JNDI, on my side I will not configure them manually in Geoserver, but just point geoserver to the JNDI reference name.

#5 Updated by Kostas Kakaletris over 1 year ago

The database service endpoint for preproduction was created to the Information System and it has all the needed information for connecting to the preproduction DB. (https://pre.d4science.org/infrastructure-monitor , preprod/preVRE -> service endpoint -> database -> WECAFCRegionalDataCollection)

We created the entry there as mentioned in #11217. I believe that this is what is needed by CNR in order other services to connect on it.

#6 Updated by Emmanuel Blondel over 1 year ago

  • Assignee changed from Kostas Kakaletris to Roberto Cirillo

@roberto.cirillo@isti.cnr.it Could you please allow connections to the above DB in R components? Thank you in advance

#7 Updated by Roberto Cirillo over 1 year ago

  • Assignee changed from Roberto Cirillo to _InfraScience Systems Engineer

#8 Updated by Andrea Dell'Amico over 1 year ago

  • Status changed from New to In Progress

Emmanuel Blondel wrote:

@roberto.cirillo@isti.cnr.it Could you please allow connections to the above DB in R components? Thank you in advance

If you refer to RPostgreSQL it's already installed everywhere R is present.

#9 Updated by Emmanuel Blondel over 1 year ago

Connection to this DB doesn't work using RPostgreSQL, so I suppose subnets are not yet allowed to access the DB

#10 Updated by Kostas Kakaletris over 1 year ago

Hello @andrea.dellamico@isti.cnr.it . Can you please assist me on finding the problem? If the problem is on db server side then there are many possible issues (firewall, postgres configuration, user privileges on database tables, etc). Can you please test the connection from your network?

#11 Updated by Andrea Dell'Amico over 1 year ago

I'm on holiday until Tuesday, but @tommaso.piccioli@isti.cnr.it can help. We do not have firewalls that block outgoing traffick, FYI.

#12 Updated by Kostas Kakaletris over 1 year ago

Hi @tommaso.piccioli@isti.cnr.it . Can you please check connecting to the database with both psql and through R and send me the error that you may get?

#13 Updated by Tommaso Piccioli over 1 year ago

I did a test with psql from both networks (CNR and GARR) and this is not a network issue.
Could be some problem with the database service endpoint on the IS?

#14 Updated by Roberto Cirillo over 1 year ago

Emmanuel Blondel wrote:

Connection to this DB doesn't work using RPostgreSQL, so I suppose subnets are not yet allowed to access the DB

Please @emmanuel.blondel@fao.org could you specify what scope have you used for the test and where did you get the DB credentials?

#16 Updated by Pasquale Pagano over 1 year ago

Hi @emmanuel.blondel@fao.org, I believe that there is a fundamental misunderstanding in the way you are trying to use the IS. The IS is for programmatic access via its client APIs. You are accessing it by the graphical interface I think. This is an issue since the pwd is encrypted and you need to have the symmetric key of the VRE context to decrypt it. This key is available in the container hosting the service and we provide API to decrypt the pwd.
Just to let you understand how it works the workflow, let me add that any time we authorize a service to join a specific VRE, the symmetric key for that VRE context is added automatically to the container. That service using the IS APIs will discover the resources it needs, e.g. a database, and by using the provided APIs (available in all SmartGears container) it can decrypt the password.
In this way either we authorize or deny access to a resource in a VRE.

Your use case is different since you are running in RStudio and you do not have the key to the context. So you cannot decrypt the password.
This is why we have implemented the SDI service for the spatial data services. It acts as a broker that accepts the requests of a client by checking its token and then returns unencrypted credentials required to access the spatial services belonging to the VRE.
We do not have a similar service for the relational databases.

So, if I correctly understood the scenario, there is not a clear solution to this new use case.
a) we could save the pwd of the DB without encrypting it in the IS (this is not secure)
b) you could embed in your code the pwd of the DB (this is not secure and not even nice to do)
c) we could extend the SDI service to cover this case (but it is completely out of its job to return also this information.

To quickly move on, i believe you need to implement solution b).
I invite @fabio.sinibaldi@isti.cnr.it to report on c) and @roberto.cirillo@isti.cnr.it to report on a).

#17 Updated by Emmanuel Blondel over 1 year ago

I agree on b), this is what i requested initially.

#20 Updated by Roberto Cirillo over 1 year ago

  • Assignee changed from _InfraScience Systems Engineer to Kostas Kakaletris

I'm going to assign this ticket to @k.kakaletris@cite.gr that is the DB Manager.

#21 Updated by Kostas Kakaletris over 1 year ago

I did a restart in postgres to read the config changes that I done and you should be able to connect to dl004 but this is the production db and not the preproduction. It has no postgis there, shapes, etc.

You should test on preproduction (dl016.madgik.di.uoa.gr, preVRE) that it has the above. I will proceed with the changes on the production when is complete and tested on preproduction.

#22 Updated by Emmanuel Blondel over 1 year ago

Ok connection to prod works. what is the pwd for pre-prod? (afterwhat we can close this ticket)
Please could you look at production and see if you could migrate easily Yann's DSDs and datasets, because he worked on prod, not pre-production; or please liaise with Yann. @yann.laurent@fao.org On the contrary I will not be able to move forward. Thanks for your support

#24 Updated by Emmanuel Blondel over 1 year ago

Noted, you can delete pwd comments! and close the ticket, thanks!

#25 Updated by Kostas Kakaletris over 1 year ago

  • % Done changed from 30 to 100
  • Status changed from In Progress to Closed

Thank you.
I'm closing the ticket but @yann.laurent@fao.org @dkatris@di.uoa.gr @apostkonst@gmail.com can you please create a new ticket and suggest what should be done to be tested on preproduction or suggested next steps/workarounds?

Also available in: Atom PDF