Engineblock's database

[Judith Bush]

Is there any documentation about integration with databases? I note an email thread about using MySQL Community and Percona XtraDB cluster.

I would appreciate information about the database schema(s). Is there a document in GitHub that would allow me to review?

From documentation such as "The User Lifecycle application is where the last login information of OpenConext suite users is stored" i assume there is some minimal amount of PII regarding users who authenticate into the system directly.   I assume no data about the end users who authenticate via the IDPs is stored in the database.

Thanks,

judith bush

[Thijs Kinkhorst]

Hi Judith,

Op 18-07-19 om 21:37 schreef Judith Bush:

Most components have their own database schema.

For the main database we support MySQL and any of its variants (MariaDB,
galera, percona).

Its most important schema is of Engineblock. You can find it represented
in Doctrine Migrations here:
https://github.com/OpenConext/OpenConext-engineblock/tree/master/database/DoctrineMigrations

Every other component will also contain its own database schema. The Java
based applications use a similar way as Doctrine, called Flyway.

But OpenConext deploy will install the necessary schemas for you.

Notable exception is the Manage application, which stores its data in a
MongoDB database, not MySQL. But this is also managed by OpenConext deploy
for you.


Your assumption is not correct. Engineblock will store the values of the
schacHomeOrganization and uid attributes of each authenticated user that
passes through the proxy. This is necessary to store which SPs the user
has given consent for and to store the pseudonymous identifiers we hand
out for that user. The lifecycle app you mention will deprovision this
data after a configurable period of inactivity of the user.


More details should be fairly evident from the database schemas themselves
but we can also help you out further with more specific questions.


Kind regards,
Thijs

[Judith Bush]

Thank you so much Thijs.

The link to the DoctrineMigrations doesn't provide a source for one current mysql schema file. I havessh'ed to the vm and run mysql dump -- but i am having a horrible time figuring out how to get the files off the vm. scp back to the host machine is failing and the /vagrant filesystem, which i infer is an nfs mount, isn't writable by the vagrant account.  I tried to make external connections to the mysql database on the vm using db.vm.openconext.org & 192.168.66.98 with no luck.

Is it possible to export the schema, tar them up, and put them on a shared drive for me? I would greatly appreciate it.

Thanks,

judith

mysqldump --no-data -u root -p aaserver --result-file=schema_aaserver.sql

mysqldump --no-data -u root -p authz-server --result-file=schema_authz-server.sql

mysqldump --no-data -u root -p eb --result-file=schema_eb.sql

mysqldump --no-data -u root -p information_schema --result-file=schema_information_schema.sql

mysqldump --no-data -u root -p mysql --result-file=schema_mysql.sql

mysqldump --no-data -u root -p oidc-server --result-file=schema_oidc-server.sql

mysqldump --no-data -u root -p pdp-server --result-file=schema_pdp-server.sql

mysqldump --no-data -u root -p performance_schema --result-file=schema_performance_schema.sql

mysqldump --no-data -u root -p spdashboard --result-file=schema_spdashboard.sql

mysqldump --no-data -u root -p sr --result-file=schema_sr.sql

mysqldump --no-data -u root -p teams --result-file=schema_teams.sql

[Pieter van der Meulen]

Hi Judith,

Vargrant can be a challenge sometimes because hosts can differ a lot. Problems writing to the vagrant directory through nfs from the guest are usually due to file permission errors on the host.

Anyway if you can ssh into the machine using "vagrant ssh", you can get files out using scp:

* Use vagrant "ssh-config" to get the ssh config for connecting to the guest and add this to ~/.ssh/config on your host.

* You can then use scp with the "Host" name from the ssh config.

Failing that I can mail an EB sql schema dump, but a guest VM that you can work with seems more useful to you.

-- 
OpenConext - Open For Collaboration
--- 
You received this message because you are subscribed to the Google Groups "OpenConext Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openconext+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/openconext/21c15f09-1728-4e56-b40f-720bcf6f9c7b%40googlegroups.com.

Groet,

Pieter.

--
Pieter van der Meulen (Pieter.va...@surfnet.nl)
SURFnet (Trust & Security) - www.surfnet.nl