Setup datastore and datapusher #27

etj · 2021-04-15T12:08:34Z

add dockerized datapusher app
setup datastore plugin

randomorder · 2021-04-21T09:55:58Z

@lpasquali please make sure you have all the info you need to move on with the task and assign an estimate to the issue so we can schedule it. Make changes to the checklist as needed

etj · 2021-04-21T09:57:31Z

Official doc here:
https://docs.ckan.org/en/2.9/maintaining/datastore.html

This is the config of the datapusher image in the official docker-compose:
https://github.com/ckan/ckan/blob/ckan-2.9.2/contrib/docker/docker-compose.yml#L45-L49
It's ok to run it into the CKAN VM.

In the ckan.ini file we need to add datastore to the ckan.plugins property list.

The datastore DB should already be set, anyway make sure that it can be properly accessed by both the datapusher app and from inside CKAN (tabular data should be displayed in a grid -- current deploy is not properly parsing the ";" as field delimiter.)

randomorder · 2021-04-28T09:39:09Z

@lpasquali please make sure you have all the info you need to move on with the task and assign an estimate to the issue so we can schedule it. Make changes to the checklist as needed

@lpasquali ?

lpasquali · 2021-05-03T16:44:21Z

@randomorder I think I can work on it, I put estimate

lpasquali · 2021-05-10T14:23:02Z

@etj I think I implemented ckan datapusher/datastore plugins correctly. if I try to move data into the datastore the csv resources actually do become correctly formatted as stated above #27 (comment)
only thing is that for resources > 10 mb the import feature within the gui is not working, I do not know if pushing original json files to the datasource api instead of the default one, will work, I have not found yet how to import data that way.
The 10 mb limit is hardcoded here

also the "official" datapusher image is 4 years old:
image: clementmouchet/datapusher
as can be seen from the repository:
https://github.com/clementmouchet/datapusher

current upstream repository of datapusher is missing a Dockerfile but the code would support to setup MAX_CONTENT_LENGTH as env variable:

https://github.com/ckan/datapusher

I would suggest to add another submodule, for https://github.com/ckan/datapusher use Dockerfile from https://github.com/clementmouchet/datapusher and make our datapusher image, with more maintained datapusher code

lpasquali · 2021-05-10T14:36:13Z

PR with work up to now: #35

etj · 2021-05-10T15:47:29Z

It seems that the official ckan docker file references an old fork for the datapusher, which has not been updated in 6 years.

Issue opened about this in the official repo: ckan/datapusher#228.

Currently working on the dockerization of the master branch of the official repo: https://github.com/geosolutions-it/datapusher/tree/228_docker

lpasquali · 2021-05-10T16:48:39Z

Currently working on the dockerization of the master branch of the official repo: https://github.com/geosolutions-it/datapusher/tree/228_docker
dockerization done, updated PR, we can move on testing things on azure, I will do it tomorrow I think @etj

lpasquali · 2021-05-11T16:33:01Z

@etj unfortunately the code using datastore writing and read only users is not working for similar reasons we found in past:

ckan          | [SQL: SELECT has_table_privilege(%s, '_foo', %s)]
ckan          | [parameters: ('datastore_ro@testpostgres01aaaa', 'INSERT')]
ckan          | (Background on this error at: http://sqlalche.me/e/f405)
ckan          | Setting var and venv...
ckan          | Initting DB...
ckan          | 2021-05-11 16:18:04,718 INFO  [ckan.cli] Using configuration file /etc/ckan/production.ini
ckan          | 2021-05-11 16:18:04,719 INFO  [ckan.config.environment] Loading static files from public
ckan          | 2021-05-11 16:18:04,721 DEBUG [ckan.lib.webassets_tools] Base path /usr/lib/ckan/venv/src/ckan/ckan/public/base
ckan          | 2021-05-11 16:18:04,751 INFO  [ckan.config.environment] Loading templates from /usr/lib/ckan/venv/src/ckan/ckan/templates
ckan          | 2021-05-11 16:18:04,959 DEBUG [ckan.logic] check access OK - get_site_user user=None
ckan          | 2021-05-11 16:18:05,004 INFO sqlalchemy.pool.impl.QueuePool Pool disposed. Pool size: 10  Connections in pool: 0 Current Overflow: -10 Current Checked out connections: 0
ckan          | 2021-05-11 16:18:05,004 INFO  [sqlalchemy.pool.impl.QueuePool] Pool disposed. Pool size: 10  Connections in pool: 0 Current Overflow: -10 Current Checked out connections: 0
ckan          | 2021-05-11 16:18:05,006 INFO sqlalchemy.pool.impl.QueuePool Pool recreating
ckan          | 2021-05-11 16:18:05,006 INFO  [sqlalchemy.pool.impl.QueuePool] Pool recreating
ckan          | 2021-05-11 16:18:05,017 INFO  [rdflib] RDFLib Version: 4.2.1
ckan          | 2021-05-11 16:18:05,113 DEBUG [ckan.lib.webassets_tools] Base path /usr/lib/ckan/venv/src/ckan/ckan/public/base
ckan          | 2021-05-11 16:18:05,279 DEBUG [ckanext.azure_auth.auth_config] Loading ADFS ID Provider configuration.
ckan          | 2021-05-11 16:18:05,280 INFO  [ckanext.azure_auth.auth_config] Trying to get OpenID Connect config from https://login.microsoftonline.com/00000000-0000-0000-0000-000000000000/.well-known/openid-configuration?appid=00000000-0000-0000-0000-000000000000
ckan          | 2021-05-11 16:18:05,363 INFO  [ckanext.azure_auth.auth_config] Trying to get ADFS Metadata file https://login.microsoftonline.com/00000000-0000-0000-0000-000000000000/FederationMetadata/2007-06/FederationMetadata.xml
ckan          | 2021-05-11 16:18:05,439 CRITI [ckanext.azure_auth.auth_config] Could not load any data from ADFS server. Authentication against ADFS is not possible. 
ckan          | 2021-05-11 16:18:05,440 CRITI [ckanext.azure_auth.plugin] Could not load any data from ADFS server. Authentication against ADFS is not possible. 
ckan          | 2021-05-11 16:18:05,442 INFO  [ckan.config.environment] Loading templates from /usr/lib/ckan/venv/src/ckan/ckan/templates
ckan          | Traceback (most recent call last):
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
ckan          |     cursor, statement, parameters, context
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 550, in do_execute
ckan          |     cursor.execute(statement, parameters)
ckan          | psycopg2.errors.UndefinedObject: role "datastore_ro@testpostgres01aaaa" does not exist
ckan          | 
ckan          | 
ckan          | The above exception was the direct cause of the following exception:
ckan          | 
ckan          | Traceback (most recent call last):
ckan          |   File "/usr/lib/ckan/venv/bin/ckan", line 33, in <module>
ckan          |     sys.exit(load_entry_point('ckan', 'console_scripts', 'ckan')())
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/click/core.py", line 829, in __call__
ckan          |     return self.main(*args, **kwargs)
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/click/core.py", line 781, in main
ckan          |     with self.make_context(prog_name, args, **extra) as ctx:
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/click/core.py", line 700, in make_context
ckan          |     self.parse_args(ctx, args)
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/click/core.py", line 1212, in parse_args
ckan          |     rest = Command.parse_args(self, ctx, args)
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/click/core.py", line 1048, in parse_args
ckan          |     value, args = param.handle_parse_result(ctx, opts, args)
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/click/core.py", line 1630, in handle_parse_result
ckan          |     value = invoke_param_callback(self.callback, ctx, self, value)
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/click/core.py", line 123, in invoke_param_callback
ckan          |     return callback(ctx, param, value)
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckan/cli/cli.py", line 100, in _init_ckan_config
ckan          |     ctx.obj = CkanCommand(value)
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckan/cli/cli.py", line 50, in __init__
ckan          |     self.app = make_app(self.config)
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckan/config/middleware/__init__.py", line 24, in make_app
ckan          |     load_environment(conf)
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckan/config/environment.py", line 122, in load_environment
ckan          |     p.load_all()
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckan/plugins/core.py", line 165, in load_all
ckan          |     load(*plugins)
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckan/plugins/core.py", line 193, in load
ckan          |     plugins_update()
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckan/plugins/core.py", line 153, in plugins_update
ckan          |     environment.update_config()
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckan/config/environment.py", line 296, in update_config
ckan          |     plugin.configure(config)
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckanext/datastore/plugin.py", line 81, in configure
ckan          |     self.backend.configure(config)
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckanext/datastore/backend/postgres.py", line 1777, in configure
ckan          |     self._check_urls_and_permissions()
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckanext/datastore/backend/postgres.py", line 1661, in _check_urls_and_permissions
ckan          |     if not self._read_connection_has_correct_privileges():
ckan          |   File "/usr/lib/ckan/venv/src/ckan/ckanext/datastore/backend/postgres.py", line 1708, in _read_connection_has_correct_privileges
ckan          |     (read_connection_user, privilege)
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 982, in execute
ckan          |     return self._execute_text(object_, multiparams, params)
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1155, in _execute_text
ckan          |     parameters,
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
ckan          |     e, statement, parameters, cursor, context
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
ckan          |     util.raise_from_cause(sqlalchemy_exception, exc_info)
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 399, in raise_from_cause
ckan          |     reraise(type(exception), exception, tb=exc_tb, cause=cause)
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
ckan          |     raise value.with_traceback(tb)
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
ckan          |     cursor, statement, parameters, context
ckan          |   File "/usr/lib/ckan/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 550, in do_execute
ckan          |     cursor.execute(statement, parameters)
ckan          | sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) role "datastore_ro@testpostgres01aaaa" does not exist
ckan          | 
ckan          | [SQL: SELECT has_table_privilege(%s, '_foo', %s)]
ckan          | [parameters: ('datastore_ro@testpostgres01aaaa', 'INSERT')]
ckan          | (Background on this error at: http://sqlalche.me/e/f405)

ckan configuration of datastore database is correct, but the app itself is not able to determine the user even trying to escape, as in past the @ with %40, unfortunately the datastore code does not use ckan models for db

etj · 2021-05-12T11:58:38Z

Found some other issues in the datastore:

Datastore: sqlalchemy setting "pool_pre_ping" (non blocking): Datastore: sqlalchemy setting "pool_pre_ping" ckan/ckan#6086 -> PR #6086 fix sqlalchemy configuration for datastore ckan/ckan#6087

etj · 2021-05-13T15:00:56Z

I'm going to check the issue in the datastore code

randomorder · 2021-05-18T12:46:45Z

updates @lpasquali ?

lpasquali · 2021-05-18T14:25:51Z

Hello @etj did you get further on the datastore database (azure related) issues?

randomorder · 2021-05-24T12:47:41Z

please let us know @etj

etj · 2021-05-26T14:33:56Z

The datastore does not need any fix.
The pg role should be created the proper way:

psql -U $arg1@$arg3 -h $arg4 postgres -c "CREATE ROLE "datastore_ro@$arg3" NOCREATEDB NOCREATEROLE LOGIN PASSWORD '${arg5}';"

instead of

psql -U $arg1@$arg3 -h $arg4 postgres -c "CREATE ROLE "datastore_ro" NOCREATEDB NOCREATEROLE LOGIN PASSWORD '${arg5}';"

It means you have to add the @PGHOST part in the role name yourself.

etj · 2021-05-26T15:00:36Z

The set-permission.sql fails because sql command referencing the role ckan@etj-pg3 are failing, in that such role does not exist, even if the psql command is run using that very username.
It seems that the default user created at startup is handled by the azure pg in a different way than the roles created by hand.
Adding a sed -e "s/ckan@etj-pg3/ckan/g" in the pipe running set-permission.sql.

etj · 2021-05-26T15:06:55Z

I added a few commits to the datapusher-datastore-ckan branch that fixes the configuration (not any problem in the datastore per se, there were some configuration issues our side).

The deploy procedure now completes successfully and ckan is properly launched.

@lpasquali I guess this is unblocked now.

randomorder · 2021-05-27T10:38:18Z

please go ahead @lpasquali

We need this before COB Friday

lpasquali · 2021-05-28T12:01:12Z

I finally was able to get datastore, datapusher and their interactions working
I cleaned the branch conflicts
I am doing one last clean deployment before making a last needed modification before the PR can be merged

#35

lpasquali · 2021-05-28T15:03:24Z

making PR #35 ready

* implementation of datapusher/datastore plugins * implementation of datapusher container in azure compose, fixed readme * added new datapusher docker image, configured datastore, datapusher ckan plugins, initted datastore db * aligned datapusher submodule * aligned datapusher submodule * aligned datapusher in azure compose * removed unused module datapusher * fixed building compose in ckan-docker/docker-compose.yml * fixed building compose in ckan-docker/docker-compose.yml * reverted parameters * datastore db provision * changed psql command * changed psql command * changed psql command * changed psql command * changed psql command * changed psql command * changed psql command * changed psql command * changed psql command * changed psql command * changed psql command * pg for datastore * #39 make ckan config persistent * #39 make ckan config persistent * #39 make ckan config persistent * #39 make ckan config persistent * #27 fix datastore role creation * #27 fix datastore set-permission * #27 fix datastore role creation * parametrized sed * updated wrong image for datapusher on azure * datastore setup * updated wrong image for datapusher on azure * updated wrong image for datapusher on azure * updated wrong image for datapusher on azure * fixing datastore_ro privileges * fixing datastore_ro privileges * fixing datastore_ro privileges * fixing datastore_ro privileges * Provide APIKEY in command line * fixing datastore_ro privileges * fixing datastore_ro privileges * fixed after merge * last fixes * fixing datastore_ro privileges * wrong branch * revert to master branch * Use datapusher-datastore-ckan for testing * Reinstate grace-period plugin * Fix custom plugin order * Switch back to master branch Co-authored-by: etj <[email protected]> Co-authored-by: Emanuele Tajariol <[email protected]>

etj added the C195-CREA-AA-2020-AGRIDIGIT label Apr 21, 2021

etj assigned etj and randomorder Apr 21, 2021

randomorder added the estimate needed we need to do an estimate for this ticket label Apr 21, 2021

etj added the DevOps This label marks this as a DevOps activity label Apr 28, 2021

lpasquali removed the estimate needed we need to do an estimate for this ticket label May 3, 2021

randomorder assigned lpasquali and unassigned randomorder May 7, 2021

lpasquali mentioned this issue May 10, 2021

Datapusher datastore ckan #35

Merged

etj linked a pull request May 13, 2021 that will close this issue

Datapusher datastore ckan #35

Merged

etj added a commit that referenced this issue May 26, 2021

#27 fix datastore role creation

00789eb

etj added a commit that referenced this issue May 26, 2021

#27 fix datastore set-permission

e5285e2

etj added a commit that referenced this issue May 26, 2021

#27 fix datastore role creation

1983ce0

etj closed this as completed in #35 Jun 4, 2021

This was referenced Jul 8, 2022

Sqlalchemy throws error that role does not exist on Azure database ongov/ckanext-ontario_theme#225

Closed

Can't connect to postgres running on azure ckan/ckan#6308

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Setup datastore and datapusher #27

Setup datastore and datapusher #27

etj commented Apr 15, 2021 •

edited by lpasquali

Loading

randomorder commented Apr 21, 2021

etj commented Apr 21, 2021 •

edited

Loading

randomorder commented Apr 28, 2021

lpasquali commented May 3, 2021

lpasquali commented May 10, 2021

lpasquali commented May 10, 2021

etj commented May 10, 2021

lpasquali commented May 10, 2021

lpasquali commented May 11, 2021 •

edited by etj

Loading

etj commented May 12, 2021

etj commented May 13, 2021

randomorder commented May 18, 2021

lpasquali commented May 18, 2021

randomorder commented May 24, 2021

etj commented May 26, 2021

etj commented May 26, 2021

etj commented May 26, 2021 •

edited

Loading

randomorder commented May 27, 2021

lpasquali commented May 28, 2021

lpasquali commented May 28, 2021

Setup datastore and datapusher #27

Setup datastore and datapusher #27

Comments

etj commented Apr 15, 2021 • edited by lpasquali Loading

randomorder commented Apr 21, 2021

etj commented Apr 21, 2021 • edited Loading

randomorder commented Apr 28, 2021

lpasquali commented May 3, 2021

lpasquali commented May 10, 2021

lpasquali commented May 10, 2021

etj commented May 10, 2021

lpasquali commented May 10, 2021

lpasquali commented May 11, 2021 • edited by etj Loading

etj commented May 12, 2021

etj commented May 13, 2021

randomorder commented May 18, 2021

lpasquali commented May 18, 2021

randomorder commented May 24, 2021

etj commented May 26, 2021

etj commented May 26, 2021

etj commented May 26, 2021 • edited Loading

randomorder commented May 27, 2021

lpasquali commented May 28, 2021

lpasquali commented May 28, 2021

etj commented Apr 15, 2021 •

edited by lpasquali

Loading

etj commented Apr 21, 2021 •

edited

Loading

lpasquali commented May 11, 2021 •

edited by etj

Loading

etj commented May 26, 2021 •

edited

Loading