Security considerations - documentation?

[analytik]

Hi,

How are database sessions persisted? Is the database password cached somewhere? What is the potential for abuse? What are the best practices?

I think it would be good to have a dedicated security page in the documentation answering these questions.

[neilotoole]

@analytik You raise a good point about the documentation.

Is the database password cached somewhere?

Yes, connection strings are persisted to a config file, typically in ~/.config/sq.yml. That file stores all of sq's state. That config file is not created until it's needed, i.e. when you add a source. The actual work is done here. Note ioz.RWPerms:

// RWPerms is the default file mode used for creating files.
const RWPerms = os.FileMode(0o600)

There's a bunch of docs about the config mechanism at sq.io/docs/config, but I agree that it should be called out that conn strings are persisted. If you're so inclined, a PR to the sq-web repo to that effect would be welcome.

Going forward:

• The conn strings could be stored in the system keychain, e.g. using a library like zalando/go-keyring. (Note: I'm not specifically endorsing that library, I haven't done research on libs in this space).
• Or, it could be an option that the password for the conn string is requested on each sq command. I'm hesitant about that... I tried out that approach in the really early days, and it was a horrible experience.

I suspect system keychain is the better approach. But I'd love to hear feedback, including on whether the current conn-string-in-config-file approach is fundamentally unacceptable for your use case.