User credentials in R-script

[FrederikEg]

Hi all,

I am using a R-script to do a upload data into a database (doesn’t work properly if done via the normal nodes). I want every user that executes the workflow to be required to enter their database credentials (or, if not possible, at least embedd mine in a secure way).

I have tried to do it using the “Credentials Input” Node, but I can not provide this Flow Variable to a R-script.
Using regular Flow Variables is not really an option since users then could save their passwords to the server by misstake in an unsecure way.

Does anybody have a good suggestion on how to procede here?

[MartinDDDD]

Hey there,

Just to clarify: you run a workflow on desktop analytics platform?

Or on Knime server / Hub?

If it’s hub: check out the Secrets Manager Feature regarding credential storage.

I don’t think though that will help with passing it to R script…

Best way would be to get the DB nodes to work… maybe you can share what problem you encounter there?

[FrederikEg]

Hi Martin, thanks for the response.

Knime is run in desktop - workflows are stored on Server (version is sadly 4.5).

I agree that the DB nodes would be the best option - but that is an ongoing issue since at least 2019. It’s that the write speed to Redshift from KNIME is horrible (< 1000 Records/Min). And the recommended workaround to upload to S3 and then copy to redshift is not a good option at my company (due to compliance).
With a R-Script embedded in KNIME I get a w-speed of ~25k Records/Min (which is still far from great).

[mlauber71]

@FrederikEg there is this to extract the credentials to flow variables

Credential Variable Extractor

You can force a reset on load with this

Reset on Load — NodePit

And/or the Don't Save Start – KNIME Community Hub and Don't Save End – KNIME Community Hub nodes prevent an extracted credential from being saved.

This example does use such an approach it seems

block nodes

Hi @noe1995, I believe, as @rfeigel expressed a well, that you want to write into the DB only under certain conditions. There is no way to literally block access to a node if the extension is installed as the DB Writer is always installed alongside the DB Loader. Though, a better approach would be to not block access to the node but i.e. require credentials. The awesome Selenium Nodes got one which resets when the workflow is loaded. If the DB credentials are stored in a file that is specific …

@FrederikEg

ongoing issue since at least 2019

Is there a ticket number or something like an example?

[FrederikEg]

Hi @mlauber71,

Thanks. Yes, the credential extractor sadly doesnt help since it is only about the password - the username can be stored insecurly, i don’t really care about that😅.

The “Dont save” looks like a workaround i could use - would still prefer a more secure and stable version - but thanks for the suggestion.

Not aware of a ticket, just some posts on here over the time - e.g.:

• inserting rows of data into a database table takes forever
• Poor performance Database Writer to Redshift