Reload config on SIGHUP

[kderme]

db-sync should reload the configuration in case of a SIGHUP. This requires proper handlers and can be related to #1010.

[erikd]

What problem does this solve? How often does the config change and need reloading?

[nrdxp]

Just to add a reference point, this would make my work here a little cleaner:
IntersectMBO/cardano-world#70

Basically what I am doing here is using dynamic db credentials generated by Vault's db secrets engine. Those credentials can be extended up to a certain maximum lease time. Once the max lease time has passed, the entrypoint kills db-sync (which it has running as a background job) grabs fresh login information from Vault again, and then restarts db-sync.

A similar process occurs when the Vault token itself expires and Nomad restarts the entire task. If I could send SIGHUP in both cases, it would be just a bit nicer, since there would be no service interuption. But with that said, the interuption is fairly minimal as is (only a second or two).

[rdlrt]

Basically what I am doing here is using dynamic db credentials generated by Vault's db secrets engine.

Isn't it more secure/better practice to use ident/socket connection instead of relying on creds to connect against DB? You can have alternate users authenticate with creds as desired (if really needed), but sounds a bit forced usage of vault - unless there is more context to it

[nrdxp]

@rdlrt you are correct there is a better way of doing it that that I just become aware of. I dunno what you mean by "forced usage" necessarily since there is an officially supported backend expressly for this purpose. However, I did not realize that with the proper creation and revocation SQL statements it is possible to switch to a permanent role, from which these dynamic roles inherit their permissions, immediately after login.

This has some nice properties, for example new tables are owned by the permanent user (whose credentials are never exposed) so there are no access issues once the dynamic user is destroyed, but even more importantly I can completely drop the dynamic user after its Vault lease expires, but the open connection continues to function with read/write abilities as the permanent role until the job stops, negating the need for application restarts at all.

[rdlrt]

I dunno what you mean by "forced usage"necessarily since there is an officially supported backend expressly for this purpose.

I meant use of credentials for dbsync is less than ideal IMO, keeping the scope to dbsync itself - where the actual dbsync database and postgres are on same machine and the user used to connect from dbsync to postgres:

• DBSync itself can connect to postgres using socket file (thus, use of vault becomes irrelevant for dbsync itself - as there are no credentials needed), this is true even for docker.
• For alternate user (for consumption of database) - sure , you can have credential based user who creds are in vault - but that becomes a downstream application operations, thus - splits any concerns regarding operations of dbsync itself.

Thus, while that backend might be useful for consumption use-cases, it isnt for the connectivity between dbsync and postgres database itself

[nrdxp]

The postgres database is not on the same machine in our case.

[erikd]

@nrdxp There is a huge amount of data traffic between the db-sync executable and PostgreSQL. For this reason it is strongly recommended that the executable and the database are run on the same machine. Failure to do this will result in a relative performance degradation of 4x or more.

This is explicitly mentioned in the system requirements section of the README.

[nrdxp]

Yeah, I saw that but that isn't how it was handed off to me to begin with. Initially we had our own patroni managed db instance in cluster that db-sync targeted. It wasn't on the same machine either, to alleviate myself of the maintenance burden I moved it to an RDS cluster. So far I haven't seen any issues, but if it becomes an issue at some point in the future I can just move it again.

[erikd]

There are two potential issues:

• Syncing from genesis will take 4 times (or more) longer than it should.
• As blocks sizes and transactions per block increases, it may reach a point where new blocks arriving cannot be inserted in the database in time enough to keep up with newly arriving blocks.