2

Because of security reasons, we can't keep SQL authentication in plain text, is there a way to hide or encrypt passwords?

I am getting bad documentation and bad support from the plugin site. Unfortunately I can't keep this data in environment variables.

GitHub link: https://github.com/fluent/fluent-plugin-sql

<source>
  @type sql
  @id output_sql
  host "sqlserverhost.aws_region.rds.amazonaws.com"
  database db_name
  adapter sqlserver
  username user
  password pwd   ==============================>>>> This is in plain text
  tag_prefix myrdb # optional, but recommended
  select_interval 60s # optional
  select_limit 500 # optional
  state_file /var/run/fluentd/sql_state
  <table>
    table tbl_name
    update_column insert_timestamp
  </table>
</source>

<match **>
  @type stdout
</match>
halfer
  • 19,824
  • 17
  • 99
  • 186
Mysterious288
  • 365
  • 5
  • 24
  • 2
    What kind of solution are you looking for if not env vars? – Azeem Jan 19 '22 at 05:54
  • @Azeem I'm looking to encrypt the password and decrypt it when I want to use it for any database operation, keeping in a file or environment variables as plain text is a security violation, could you please help to achieve the requirement. – Mysterious288 Jan 20 '22 at 02:00
  • Right. What kind of deployment is it that you're working on? Kubernetes? – Azeem Jan 20 '22 at 05:17
  • @Azeem It is a local system windows server, we use an on-premises kind of deployment using msi/wix. – Mysterious288 Jan 20 '22 at 12:16
  • Right. I believe you've already asked the maintainers to support the encryption for password (https://github.com/fluent/fluent-plugin-sql/issues/106) with no response yet. – Azeem Jan 20 '22 at 12:52
  • @Azeem Yes and it's been long time no response, its like a dead forum, I desperately need a solution. – Mysterious288 Jan 20 '22 at 17:40
  • Right. I think you need to look for other deployment methods where you could dynamically fetch and place the password instead of using env vars (or maybe using env vars carefully i.e. set and load fluentd, and then reset the env var, or something like that). The password won't show up in the logs as it's been marked as a secret (https://github.com/fluent/fluent-plugin-sql/blob/master/lib/fluent/plugin/in_sql.rb#L38-L39). – Azeem Jan 21 '22 at 03:58
  • @Azeem Can we use some encryption plugin where the password is encrypted and then using the same plugin decrypt the password ? – Mysterious288 Jan 21 '22 at 05:45
  • I'm not aware of such a plugin. But if there were a plugin, how would you fit it in your deployment scenario? As I understand this, even if there's some external entity doing encryption/decryption, it needs to be stored somewhere and passed on to the fluentd instance. Otherwise, in your case, the SQL plugin should support some mechanism to communicate with such an en/decryption plugin. – Azeem Jan 21 '22 at 06:48
  • @Azeem The encrypted password will be stored in the environment variable using PowerShell and fluentd instance will retrieve it by decrypting. – Mysterious288 Jan 21 '22 at 10:53
  • You could do something similar by writing a wrapper script that would invoke fluentd. Before that, you would be able to handle the en/decryption stuff, store it in an env var and run fluentd. That would be automating what you're already doing manually. – Azeem Jan 21 '22 at 12:19
  • @Azeem Didn't quite follow you, encrypting in an environment variable and how would I decrypt that in fluentd? Practically why the basic thing in fluentd is so hard. – Mysterious288 Jan 22 '22 at 14:25
  • Actually, given your deployment scenario it's hard to figure out something generic. I'd suggest forking the plugin and add en/decryption functionality in it. You can use that custom plugin afterwards. – Azeem Jan 22 '22 at 14:50
  • @Azeem Can you show some practical example? I don't have any idea how to create a plugin, and I 'm not looking anything generic fairly simple. – Mysterious288 Jan 22 '22 at 16:38
  • I don't have any examples to share. It's just an idea that you can fork the original repo (https://github.com/fluent/fluent-plugin-sql) and modify it accordingly. This might be helpful: https://docs.fluentd.org/plugin-development. – Azeem Jan 22 '22 at 17:29
  • @Azeem It's difficult for me since I don't have knowledge on writing plugins or writing a code in rails, however will try, thanks. – Mysterious288 Jan 23 '22 at 02:37

1 Answers1

0

I recommend adding the SQL plugin password into your config/credentials.yml.enc which should be able to be accessed as an environmental variable.

unencrypted config/credentials.yml.enc

fluentd:
    password: yourpassword

Then when you need to access the password

Rails.application.credentials.fluentd[:password]

See more about encryption of secrets and its workflow in this guide: https://blog.corsego.com/ruby-on-rails-6-credentials-full

Edit

Responding to this part of your question:

Unfortunately I can't keep this data in environment variables.

I would advocate for using environment variables but perhaps you could provide additional insight on why that solution would not fit your use case.

There is a compelling conversation on an another Stack Overflow question:

Is it secure to store passwords as environment variables (rather than as plain text) in config files?

halfer
  • 19,824
  • 17
  • 99
  • 186
Joe Thor
  • 1,164
  • 1
  • 11
  • 19
  • Like I mentioned in my previous comment it's against our security policy, we cannot afford to keep it in plain text no matter wherever it is and you see one comment in the link posted by you "As mentioned before, both methods do not provide any layer of additional "security" once your system is compromised." – Mysterious288 Jan 20 '22 at 02:26
  • I'm using SQL plugin in fluentd so it's more of a solution I need in fluentd since I don't have my custom plugin nor any knowledge on ruby on rails – Mysterious288 Jan 20 '22 at 02:27
  • @Mysterious288 The `config/credentials.yml.enc` file contents is encrypted, you'll need to have the associated `config/master.key` (or `RAILS_MASTER_KEY` environment variable) to decrypt the file. To edit/read it you'll have to use `rails credentials:edit`. Meaning that your database password is not stored in plain text. Note that there is also the option to create environment specific credentials, use `rails credentials:edit --environment development` – 3limin4t0r Jan 26 '22 at 00:47
  • @3limin4t0r Sorry I'm not getting exactly, is this related to the ruby on rails process, is it possible in fluentd?? – Mysterious288 Jan 26 '22 at 11:22