Task #13111

gCube - Task #13068: PerformFISH Analytical Toolkit Service

Perform-Fish DB configuration

Added by Fabio Sinibaldi about 1 month ago. Updated 30 days ago.

Status:In ProgressStart date:Jan 21, 2019
Priority:NormalDue date:
Assignee:_InfraScience Systems Engineer% Done:

100%

Category:Application
Sprint:PerformFish
Infrastructure:Development, Pre-Production, Production
Milestones:
Duration:

Description

We need to configure access to such DB in the following way :

1) Manager user : used by portal instances and perform-service, MUST be unavailable to other IPs. GRANTS full rights to all database tables.
2) Public user : used by external applications/users. GRANTS read rights on 3 predefined views.

Such Database is published in IS as a ServiceEndpoint with the following coordinates :
Category = Database
Name = PF_DB

Currently, development environment database is 'pf_kpi_apps' hosted on postgresql-srv-dev.d4science.org


Subtasks

VM Creation #13195: Custom DBMS MachineRejected_InfraScience Systems Engineer

History

#1 Updated by Andrea Dell'Amico about 1 month ago

  • % Done changed from 0 to 50
  • Status changed from New to In Progress

Added access to the prod db from the prod service. The dev one is open to all the datacenter subnet.

#2 Updated by Fabio Sinibaldi about 1 month ago

Perform fish application constraints require to increase the limit on tables' field identifiers.
This should be feasible following : https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Currently the longest field name we need to handle is 115 characters long (DBMS limit default is 63)

#3 Updated by Andrea Dell'Amico about 1 month ago

Fabio Sinibaldi wrote:

Perform fish application constraints require to increase the limit on tables' field identifiers.
This should be feasible following : https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Currently the longest field name we need to handle is 115 characters long (DBMS limit default is 63)

easily == recompile the postgresql code after altering a constant.

it can be raised by changing the NAMEDATALEN constant in src/include/pg_config_manual.h

This means that we'll have to distribute our own postgresql set of packages, and recompile them at every bug fix or security upgrade (or doing it once and forget about the vulnerabilities).

I'd really like to avoid that.

#4 Updated by Fabio Sinibaldi about 1 month ago

Didn't say easily but neither i thought it would be such a mess.
I'll try to think of a different solution.

#5 Updated by Andrea Dell'Amico about 1 month ago

Fabio Sinibaldi wrote:

Didn't say easily but neither i thought it would be such a mess.
I'll try to think of a different solution.

We can do that if there's no other way, but I'd prefer not: we don't have the infrastructure in place to automate such a task yet.
If we must, maybe it's better to move the two databases into a dedicate VM.

#6 Updated by Fabio Sinibaldi about 1 month ago

Yes, a dedicated VM might be a good choice.
I could internally manage this issue by implementing a mapping between labels and actual field identifiers, but we have very pressing deadlines.

One option might be to apply the change to the new VM for the time being and then get rid of it (the configuration and/or the VM itself) as soon as I deliver this feature.

If you think it means a lot of work from your side as well, we could wait some day to see if I can manage to implement this feature in time.

#7 Updated by Andrea Dell'Amico about 1 month ago

OK, let's see what I can do.

#8 Updated by Andrea Dell'Amico about 1 month ago

I started a package build. That change requires an initdb to take effect, so a new VM for those databases is mandatory. @fabio.sinibaldi@isti.cnr.it can you please open a subticket with the VM requirements?

#9 Updated by Andrea Dell'Amico about 1 month ago

  • % Done changed from 50 to 70

New deb packages are available in a reserved repository on ppa.research-infrastructures.eu. The repository name is trusty-infrascience-postgresql. Coordinates to add the repository:

When we add this repository, the ansible variable pg_use_postgresql_org_repo must be set to False.

#10 Updated by Fabio Sinibaldi 30 days ago

I've actually managed to implement a mapping mechanism so the fieldname length limitation shouldn't be a problem anymore. Thanks for the support.

Also available in: Atom PDF