Project Activity #10650

Tuna Atlas - new db "tunaatlas"

Added by Paul Taconet over 1 year ago. Updated over 1 year ago.

Status:ClosedStart date:Dec 11, 2017
Priority:HighDue date:Dec 31, 2017
Assignee:_InfraScience Systems Engineer% Done:

100%

Sprint:WP08
Participants:
Milestones:
Duration: 15

Description

We have developed a new version of the Tuna Atlas database model (v2). However, a lot of codes have been written and are running with the current database model (v1), therefore we do not want for now to make changes on the current database model.

We would hence like a new Postgresql+Postgis database for the Tuna Atlas, so as to test the new model, and then adapt the codes. This will also allow us to test the reproducibility of the project (i.e. deployment of an empty db model and upload of datasets).

The specifications for the new database to deploy are below:

  • The database should be named "tunaatlas".
  • The PostgreSQL + PostGIS versions should be the ones used in the current db-tuna database.
  • The capacity should be 100 GB.
  • The database should be empty (i.e. we will deploy the model once the empty database is available)
  • The maximum number of characters for the table and view names should be 100 (see https://support.d4science.org/issues/8354)

Once this new database tested, validated, filled and the codes adapted, the current database (db-tuna) could be dismissed.

Thanks,

Paul

History

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

  • Status changed from New to In Progress

Paul Taconet wrote:

We cannot satisfy this one, sorry. The old motivations still apply.

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

  • % Done changed from 0 to 100
  • Status changed from In Progress to Feedback

#4 Updated by Paul Taconet over 1 year ago

Thanks a lot.
I got the password.
Paul

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

  • Status changed from Feedback to Closed

#6 Updated by Paul Taconet over 1 year ago

I cannot create a new role (user) with the tunaatlas_u user.
e.g. the script "CREATE USER invsardara WITH PASSWORD '***';" fails (ERROR: permission denied to create role)
Could you enable the user tunaatlas_u to create new users?
Thanks
Paul

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

How many users do you need? We usually explicitly add each of them to the pg_hba.conf file, so being able to create them is not enough. If you need a couple of them we can create and configure for you, otherwise I'll add the role to the user and I'll open the DB to any username.

#8 Updated by Paul Taconet over 1 year ago

Thanks for the quick answer.
For the time being, I need only one additional user with read permissions only.
You might call it 'tunaatlas_inv' and provide a short and simple password (e.g. fle087)
Thanks,
Paul

#9 Updated by Paul Taconet over 1 year ago

Can you please enable PostGIS and postgis_topology on the DB?
Thanks,
Paul

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

Done, both the user with select privileges and the postgis extensions.

#11 Updated by Paul Taconet over 1 year ago

Thanks a lot for the quick work.
Paul

#12 Updated by Paul Taconet over 1 year ago

I also need to execute the following query to grant select on the schema information_table to tunaatlas_inv

GRANT USAGE ON SCHEMA information_schema TO tunaatlas_inv;
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO tunaatlas_inv;

but I believe I do not have enough rights to do it with tunaatlas_u.
Could you execute the query for me please?
Thanks,
Paul

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

Done.

#14 Updated by Paul Taconet over 1 year ago

Hi,

I guess that you created the user tunaatlas_inv with the postgres role, since I cannot grant priviliges for that user using the tunaatlas_u user. However, I need the "tunaatlas_u" user to be owner of the "tunaatlas_inv" user.

Could you please re-create the user "tunaatlas_inv" so as to enable the user "tunaatlas_u" to grant him privileges?

Thanks,
Paul

#15 Updated by Paul Taconet over 1 year ago

Hi,
Can you please answer the last request? I need it asap for my webinar next week.
Thanks,
Paul

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

  • Status changed from Closed to Feedback

I just added the CREATEROLE CREATEUSER roles to the tunaatlas_u user. Let me know if it's sufficient.

Hi, and thanks. However, I still cannot do SELECT queries with the user "tunaatlas_inv" on tables that I have created with "tunaatlas_u" and where I have set "GRANT SELECT ON TABLE xxxxx TO tunaatlas_inv;" (e.g. GRANT SELECT ON TABLE metadata.metadata TO tunaatlas_inv;)

Do you know why?
Thanks,
Paul

#17 Updated by Paul Taconet over 1 year ago

Hi, and thanks. However, I still cannot do SELECT queries with the user "tunaatlas_inv" on tables that I have created with "tunaatlas_u" and where I have set "GRANT SELECT ON TABLE xxxxx TO tunaatlas_inv;" (e.g. GRANT SELECT ON TABLE metadata.metadata TO tunaatlas_inv;)

Do you know why?
Thanks,
Paul

#18 Updated by Paul Taconet over 1 year ago

I got it. I just forgot to execute the query "GRANT USAGE ON SCHEMA xxxx TO tunaatlas_inv". Everything seems to be ok now. Thanks a lot

#19 Updated by Pasquale Pagano over 1 year ago

  • Status changed from Feedback to Closed
  • Tracker changed from Support to Project Activity

Also available in: Atom PDF