Project Activity #11286
Project WP #641: WP5 - Supporting Blue Assessment: VREs Development [Months: 1-30]
|Status:||Closed||Start date:||Feb 22, 2018|
|Assignee:||Kostas Kakaletris||% Done:|
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
#2 Updated by Kostas Kakaletris over 1 year ago
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)
#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.
#8 Updated by Andrea Dell'Amico over 1 year ago
- Status changed from New to In Progress
Emmanuel Blondel wrote:
@firstname.lastname@example.org 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.
#10 Updated by Kostas Kakaletris over 1 year ago
Hello @email@example.com . 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?
#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 @firstname.lastname@example.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 @email@example.com, 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.
#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. @firstname.lastname@example.org On the contrary I will not be able to move forward. Thanks for your support
#25 Updated by Kostas Kakaletris over 1 year ago
- % Done changed from 30 to 100
- Status changed from In Progress to Closed