User/password support for CrateDB connection

IEMaestro commented 3 years ago

Is your feature request related to a problem? Please describe.

Currently, QL version does not support user/password to connect to CrateDB. It uses the "user default" to connect and there is no ENV Variable to provide a password for this user. In case you want to enable the (external or not) access to CrateDB (e.g. to web-monitor the status of the historical DB) and you want to secure it (by using the CrateDB HBA option) this means that QL cannot get conected to the CrateBD. The only way to keep both (users authentication for SQL access/monitoring and QL access to Crate) requires to filter the IPs (internal) that can use the "crate (or defaut) superuser" with "trust" auth. mechanism. E.g. in a kubernetes env. where IPs may change and you cannot control all the assigned IPs, this means that all deployed pods within the cluster could potentially access CrateDB with superuser privileges.

Describe the solution you'd like

Support conection to CrateDB by specifiying a CrateDB user/password. If none are specified, use superuser "crate" (which cannot be assigned a password, according Crate 4.1.x doc) by default.

Describe alternatives you've considered I've check all the (official) doc corresponding to CrateDB 4.1.X. Only HBA is supported to enable authentication. CrateDB secure conf. (address parameter) only supports IP and IP masks (not hostnames) so I cannot restrict access to a given hostmane unless I know its (permanent) IP.

Additional context

Crate HBA doc: https://crate.io/docs/crate/reference/en/4.5/admin/auth/

IEMaestro commented 3 years ago

Thanks Andreas for your tracking and forwarding. But, correct me if I'm wrong: in case "crate" implements this "hostname" support, this would come with next Crate version (this is Crate 4.5.X) and, according to Quantum Leap installation guide, this only supports Crate 4.1.X, so, as far as I understand, this won't solve my issue, at least, not so far. Am I Right? Regards!!

c0c0n3 commented 3 years ago

@IEMaestro thank you so much for bringing this up. Indeed backend authentication and authorisation are totally underdeveloped functionality areas in QuantumLeap. We'll definitely tackle this at some point in time and possibly support other authn/authz methods too. Even though we recognise the importance of having proper authn/authz in place, we can't push on with this issue at the moment since we've got another big chunk of work in the pipeline. But with luck, we might be able to work on this a couple of months from now. As always we'll gladly review PRs...mentioning it just in case anyone out there would like to contribute this feature.

amotl commented 3 years ago

Dear @IEMaestro,

As far as I understand, [having this feature available within CrateDB 4.6+] won't solve my issue, at least, not so far. Am I right?

It will currently not solve your issue, we can't go back in time, right. However, I still want to thank you very much for bringing this up. I am sure it will be useful for you and others in future setups of QuantumLeap+CrateDB, as soon as https://github.com/crate/crate/issues/11275 got implemented.

With kind regards, Andreas.

BaurzhanSakhariev commented 3 years ago

Hi there, crate/crate#11275 has been merged into master and will be available in the next release.

IEMaestro commented 3 years ago

Thanks a lot for considering this feature. I will implement now this new version within our solution. Regards

El mié, 12 may 2021 a las 12:20, Baurzhan @.***>) escribió:

Hi there, crate/crate#11275 https://github.com/crate/crate/issues/11275 has been merged into master and will be available in the next release.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/orchestracities/ngsi-timeseries-api/issues/474#issuecomment-839656906, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEXCPGOOYC3TPBTK7JD3T3DTNJI77ANCNFSM4243SKIQ .

c0c0n3 commented 3 years ago

@BaurzhanSakhariev that's great news, thanks for letting us know!

chicco785 commented 3 years ago

@IEMaestro #481 introduce crate support up to 4.5.1 (tested) if you want to introduce password and user for crate, we are happy :) it's should be really a simple job :)

define proper env variable names
use the env reader utility to load them in the crate translator instance: https://github.com/orchestracities/ngsi-timeseries-api/blob/master/src/translators/crate.py#L264
expand init to use the two new variables https://github.com/orchestracities/ngsi-timeseries-api/blob/master/src/translators/crate.py#L45
compose correctly the url to connect to the db: https://github.com/orchestracities/ngsi-timeseries-api/blob/master/src/translators/crate.py#L54
add a test to verify if that works

it's really a minor debit, the only tricky part would be to test it properly

github-actions[bot] commented 3 years ago

Stale issue message

modularTaco commented 3 years ago

Thanks @IEMaestro for this solution!

We're now also running QuantumLeap with cratedb 4.6.1 and authentication for all clients, except QL.

We run QL and cratedb in Kubernetes and had a bit of a chicken egg problem, but now it works smoothly.

The problem was that we've added the host based authentication configuration to cratedb as follows:

         - -Cauth.host_based.enabled=true
         - -Cauth.host_based.config.0.user=crate
         - -Cauth.host_based.config.0.address=_local_
         - -Cauth.host_based.config.0.method=trust
         - -Cauth.host_based.config.1.user=crate
         - -Cauth.host_based.config.1.address=ql-quantumleap
         - -Cauth.host_based.config.1.method=trust
         - -Cauth.host_based.config.99.method=password

But cratedb was not able to resolve ql-quantumleap, as the corresponding kubernetes service had no endpoints.
There were no endpoints as QL was not ready. And QL was not ready because the readinessProbe, which checks /health, failed.
And it failed because it could not connect to CrateDB. And QL could not connect to Cratedb because, you guessed it, cratedb was not able to resolve the quantumleap service name and therefore was not using the specified authentication method for QL.

So we disabled the readinessProbe for QL, the service got an endpoint, cratedb could resolve the service name and finally QL was able to connect to cratedb without authentication again.

just wanted to post it in case anybody else is running into the same problem

chicco785 commented 3 years ago

Thanks @IEMaestro for this solution!

We're now also running QuantumLeap with cratedb 4.6.1 and authentication for all clients, except QL.

We run QL and cratedb in Kubernetes and had a bit of a chicken egg problem, but now it works smoothly.

The problem was that we've added the host based authentication configuration to cratedb as follows:
         - -Cauth.host_based.enabled=true
         - -Cauth.host_based.config.0.user=crate
         - -Cauth.host_based.config.0.address=_local_
         - -Cauth.host_based.config.0.method=trust
         - -Cauth.host_based.config.1.user=crate
         - -Cauth.host_based.config.1.address=ql-quantumleap
         - -Cauth.host_based.config.1.method=trust
         - -Cauth.host_based.config.99.method=password
But cratedb was not able to resolve ql-quantumleap, as the corresponding kubernetes service had no endpoints. There were no endpoints as QL was not ready. And QL was not ready because the readinessProbe, which checks /health, failed. And it failed because it could not connect to CrateDB. And QL could not connect to Cratedb because, you guessed it, cratedb was not able to resolve the quantumleap service name and therefore was not using the specified authentication method for QL.

So we disabled the readinessProbe for QL, the service got an endpoint, cratedb could resolve the service name and finally QL was able to connect to cratedb without authentication again.

just wanted to post it in case anybody else is running into the same problem

good job! trust among services is a complex issue. my recommendation is that rather than managing it service by service, a framework like istio can better govern this

github-actions[bot] commented 2 years ago

Stale issue message

daminichopra commented 2 years ago

Hi @chicco785 @chicco785 @IEMaestro , Please let me know if someone working on this if not I would like to investigate on this issue. Thank you

chicco785 commented 2 years ago

@daminichopra @modularTaco already made a pr

modularTaco commented 2 years ago

@chicco785 @c0c0n3 as this seems to be part of the 0.8.3 release, can this issue be closed?

orchestracities / ngsi-timeseries-api

User/password support for CrateDB connection #474