how to we want to take a dump of the db

[jcrossley3]

Is there no existing postgres cli tool that will do it? Do we need the dump feature in the app?

[JimFuller-RedHat]

there is good old fashioned pg_dump/pg_restore approach

backup

> pg_dump -U username -h hostname -p port -d database_name > backup_file.sql

restore

> pg_restore -U username -h hostname -p port -d database_name backup_file.sql

depends if you want schema+data ... also complexities arise if you use row security ... baking in backup/restore to the app is also baking in pg dep.

[helio-frota]

Thank you for sharing, the title is quite vague, I'll add context:

Months ago we had a discussion regarding generating the database schema (in development mode) when creating new unit tests, which, in this case, alter the database.

What I understood is that the intention would be to obtain the database schema at the end of the tests for later generation of an entity-relationship diagram, as a visual verification step or something like that (I'm not sure).

With the focus being on PM-mode, we made some attempts to get the database dump for later diagram generation, until the author of pg_embedded added support for it programmatically https://github.com/theseus-rs/postgresql-embedded/blob/main/postgresql_embedded/tests/dump_command.rs#L10.

Overall, I'm still seeing this diagram generation as a development step, not a product step... so I don't yet know if we really need it or how we need this diagram. Since we can run commands (as you mentioned)... still, with the dump.sql, we would need some other external tool for diagram generation using that dump.sql as input.

thinking in development-mode only the ideal would be:

cargo test

and in the end some diagram.png (not .sql) in target directory or etc, but that seems too far away (both dev-mode-only and not part of the project).

having dump.sql in target directory or something, is better than nothing but team needs to decide to change the code to find a better place to have that dump call.

and ofc we can always double think to check if we are not creating new unnecessary problems : ) and with that in mind we could close this issue... because it is marked as question :+1:

[JimFuller-RedHat]

ah right ... there are tools for automating generation of erd - https://schemaspy.org/ comes to mind (and https://www.schemacrawler.com/downloads.html), otherwise lots of sql editors generate erd as well ... will ask around (and yes looking for non java pref rust options)

[helio-frota]

even if we want to call that dump via code I found other issue when running the app... https://github.com/trustification/trustify/issues/213

( previously focused on tests only ... when running the app we have this ^)

[JimFuller-RedHat]

This technique might be 'clean' enough without wading into the java muck (as opposed to rust muck), with added bonus of emitting mermaid diagrams which default display in github (and text based).

[helio-frota]

interesting... thanks for sharing :+1: if we continue thinking on the developer-mode diagram approach (run tests and take a look on what is happening with the db) I'm +1 to this and forget the diagram.png :+1:

[helio-frota]

note: in previous comment I mentioned development-mode related to the tests and etc...

I found this handy when running PM-mode:

erDiagram
advisory {
    integer id PK
    character_varying identifier
    character_varying location
    timestamp_with_time_zone modified
    timestamp_with_time_zone published
    character_varying sha256
    character_varying title
    timestamp_with_time_zone withdrawn
}
advisory_vulnerability {
    integer advisory_id PK,FK
    integer vulnerability_id PK,FK
}
affected_package_version_range {
    integer id PK
    integer advisory_id FK
    integer package_version_range_id FK
    integer vulnerability_id FK
}
cpe {
    integer id PK
    character_varying edition
    character_varying language
    character_varying other
    character_varying part
    character_varying product
    character_varying sw_edition
    character_varying target_hw
    character_varying target_sw
    character_varying update
    character_varying vendor
    character_varying version
}
cvss3 {
    integer advisory_id PK,FK
    integer minor_version PK
    integer vulnerability_id PK,FK
    cvss3_a a
    cvss3_ac ac
    cvss3_av av
    cvss3_c c
    cvss3_i i
    cvss3_pr pr
    cvss3_s s
    cvss3_ui ui
}
cvss4 {
    integer advisory_id PK,FK
    integer minor_version PK
    integer vulnerability_id PK,FK
    cvss4_ac ac
    cvss4_at at
    cvss4_av av
    cvss4_pr pr
    cvss4_sa sa
    cvss4_sc sc
    cvss4_si si
    cvss4_ui ui
    cvss4_va va
    cvss4_vc vc
    cvss4_vi vi
}
fixed_package_version {
    integer id PK
    integer advisory_id FK
    uuid package_version_id FK
    integer vulnerability_id FK
}
importer {
    character_varying name PK
    jsonb configuration
    timestamp_with_time_zone last_change
    character_varying last_error
    timestamp_with_time_zone last_run
    timestamp_with_time_zone last_success
    uuid revision
    integer state
}
importer_report {
    uuid id PK
    character_varying importer FK
    timestamp_with_time_zone creation
    character_varying error
    jsonb report
}
not_affected_package_version {
    integer id PK
    integer advisory_id FK
    uuid package_version_id FK
    integer vulnerability_id FK
}
package {
    uuid id PK
    character_varying name
    character_varying namespace
    timestamp_with_time_zone timestamp
    character_varying type
}
package_relates_to_package {
    uuid left_package_id PK,FK
    integer relationship PK,FK
    uuid right_package_id PK,FK
    integer sbom_id PK,FK
}
package_version {
    uuid id PK
    uuid package_id FK
    timestamp_with_time_zone timestamp
    character_varying version
}
package_version_range {
    integer id PK
    uuid package_id FK
    character_varying end
    character_varying start
    timestamp_with_time_zone timestamp
}
qualified_package {
    uuid id PK
    uuid package_version_id FK
    jsonb qualifiers
    timestamp_with_time_zone timestamp
}
relationship {
    integer id PK
    character_varying description
}
sbom {
    integer id PK
    character_varying[] authors
    character_varying document_id
    character_varying location
    timestamp_with_time_zone published
    character_varying sha256
    character_varying title
}
sbom_describes_cpe {
    integer cpe_id PK,FK
    integer sbom_id PK,FK
}
sbom_describes_package {
    uuid qualified_package_id PK,FK
    integer sbom_id PK,FK
}
sbom_package {
    uuid qualified_package_id PK,FK
    integer sbom_id PK,FK
}
seaql_migrations {
    character_varying version PK
    bigint applied_at
}
vulnerability {
    integer id PK
    character_varying identifier
    timestamp_with_time_zone timestamp
    character_varying title
}
vulnerability_description {
    integer id PK
    integer vulnerability_id FK
    character_varying description
    character_varying lang
    timestamp_with_time_zone timestamp
}

advisory_vulnerability }o--|| advisory: ""
advisory_vulnerability }o--|| vulnerability: ""
affected_package_version_range }o--|| advisory: ""
affected_package_version_range }o--|| vulnerability: ""
affected_package_version_range }o--|| package_version_range: ""
cvss3 }o--|| advisory: ""
cvss3 }o--|| advisory: ""
cvss4 }o--|| advisory: ""
cvss4 }o--|| advisory: ""
fixed_package_version }o--|| advisory: ""
fixed_package_version }o--|| vulnerability: ""
fixed_package_version }o--|| package_version: ""
importer_report }o--|| importer: ""
not_affected_package_version }o--|| advisory: ""
not_affected_package_version }o--|| vulnerability: ""
not_affected_package_version }o--|| package_version: ""
package_relates_to_package }o--|| qualified_package: ""
package_relates_to_package }o--|| relationship: ""
package_relates_to_package }o--|| qualified_package: ""
package_relates_to_package }o--|| sbom: ""
package_version }o--|| package: ""
package_version_range }o--|| package: ""
qualified_package }o--|| package_version: ""
sbom_describes_cpe }o--|| sbom: ""
sbom_describes_cpe }o--|| cpe: ""
sbom_describes_package }o--|| sbom: ""
sbom_describes_package }o--|| qualified_package: ""
sbom_package }o--|| sbom: ""
sbom_package }o--|| qualified_package: ""
vulnerability_description }o--|| vulnerability: ""

not the best detailed option but better than nothing

[helio-frota]

1) cargo run --bin trustd 1.1) copy the db connection string

2024-05-06T16:47:09.390771Z  WARN trustify_server: Authentication is disabled
2024-05-06T16:47:09.390835Z  INFO trustify_common::db: connect to postgres://postgres:trustify@localhost:36477/trustify 
2024-05-06T16:47:09.523592Z  INFO trustify_infrastructure::infra: Infrastructure endpoint is disabled
2024-05-06T16:47:09.523879Z  INFO trustify_infrastructure::health::checks::local: received shutdown signal
2024-05-06T16:47:09.524015Z  INFO trustify_infrastructure::health::checks::local: check future returned

The connection string ^: postgres://postgres:trustify@localhost:36477/trustify

2) open a new terminal 2.1) install this cargo install sqlant (https://github.com/kurotych/sqlant) 2.2) run

sqlant "postgres://postgres:trustify@localhost:36477/trustify" -o mermaid

Copy the output to a mermaid viewer

[helio-frota]

@jcrossley3 can we close this ? thanks

[jcrossley3]

Sure! :)