Feature Request: Password Prompt

[Chovin]

Issue Type

• [x] Feature Request

Description

Any chance we can get a prompt for a password if left as null in the connection settings? I feel uncomfortable keeping all the login information in one place in plaintext

Versions

• Version: v0.9.1
• OS: PC
• RDBMS: Oracle

You can get this information from executing ST: About from Sublime Command Palette.

[mtxr]

It's better to add an option require_password or, prompt_password for the connection. For postgresql, if you leave it null it gets the password from file.

What do you think?

[tkopets]

I would explore if there are any other built-in alternative ways to supply a password for Oracle, if there are it would be best to utilize those (e.g. as we already do for MySQL and PostgreSQL).

A couple of things to consider if we would go with the idea of asking a password from user:

• each interaction with CLI would require a password, so we have to store that password internally to avoid asking it every time
• dealing with loading default connection and password prompt.
• we do launch internal Commands as well to get a list of identifiers (tables, columns, functions etc) - probably not a problem

[mtxr]

Nice.

I think the feature is good, but I would consider storing password for session to avoid lots of prompts.

I know that other sides have some features to avoid using cli passwords, but sometimes users don't know how to achieve it, depending on the OS (eg. Postgresql on Windows) it's so painful that they give up and prefer to type it every time.

What do you think? That's a good discussion.

On May 17, 2017 07:56, "Taras Kopets" notifications@github.com wrote:

I would explore if there are any other built-in alternative ways to supply a password for Oracle, if there are it would be best to utilize those (e.g. as we already do for MySQL and PostgreSQL).

A couple of things to consider if we would go with the idea of asking a password from user:

• each interaction with CLI would require a password, so we have to store that password internally to avoid asking it every time
• dealing with loading default connection and password prompt.
• we do launch internal Commands as well to get a list of identifiers (tables, columns, functions etc) - probably not a problem

— You are receiving this because you were assigned. Reply to this email directly, view it on GitHub https://github.com/mtxr/SQLTools/issues/99#issuecomment-302056079, or mute the thread https://github.com/notifications/unsubscribe-auth/AArL6YBlLHUs0kxxpwSelOfihwMPZzmfks5r6tJbgaJpZM4NdfNy .

[tkopets]

Yes, I completely agree with you that usability and user friendliness is affected when using more secure ways to supply a password (e.g. configuring pgpass.conf for PostgreSQL or defaults-extra-file/login-path for MySQL). That's almost always the case when dealing with security related things - you have to do (configure) something extra if you want additional security.

On PostgreSQL authentication: I'm all for making SQLTools more user-friendly, but let's make it clear if some auth methods are not secure, so the user makes an informed decision (convenience vs. security). Right now SQLTools supports pgpass.conf file as authentication and also setting an environment variable PGPASSWORD should work as well. There is no way to supply a password for psql directly as command-line parameter, so we can only go with setting environment variable PGPASSWORD before invoking psql to support SQLTools password option (or to-be-implemented prompt_password option). Note: even though the use of PGPASSWORD environment variable is deprecated (from pg docs) it is still widely used in practice, so I guess we can go with that to make life easier for SQLTools users. Also, we have to think carefully how to make this thing configurable from SQLTools settings vs. writing a custom authentication code for each RDBMS. I think it we will need to introduce a new RDBMS-specific option(s) to set those env. variable(s).

On Oracle authentication: There is an option to use a wallet Oracle feature: http://docs.oracle.com/cd/B19306_01/network.102/b14266/cnctslsh.htm However, it may not be as easy to implement it, so I guess going with password prompt is a good idea at this stage. We can always review it once again at some point in time and eventually implement that auth method as well.

[tkopets]

I guess we can borrow the concept of setting environment variables (for PostgreSQL auth) from Sublime Text exec - providing a dict of key-value pairs. http://docs.sublimetext.info/en/latest/reference/build_systems/exec.html

E.g. something like this:

{
    "cli_options": {
        "pgsql": {
            ...
            "env": {
                "PGPASSWORD" : "{password}"
            }
            ...
    }
}

[xnmp]

This would be nice: as a newbie I can't get this plugin to work for the life of me, I keep getting psql: fe_sendauth: no password supplied even though I've created .pgpass and pgpass.conf all over the place.

[tkopets]

@xnmp What OS are you using? Windows?

[xnmp]

@tkopets Ubuntu

[tkopets]

@xnmp the .pgpass file should exist in your home directory (~/.pgpass or expanded path /home/YOUR_USERNAME/.pgpass). The file should have the right privileges - use chmod 0600 ~/.pgpass to fix this (if needed). The file format is:

hostname:port:database:username:password

If you still having problems doing it manually you can install pgAdmin and add a connection there (use the checkbox option to save password). pgAdmin will store the connection details in .pgpass file. I hope this will get much easier when we implement additional auth method for PostgreSQL. Let me know if you managed to connect to your DB.

[xnmp]

Thanks for the help. I did that, and I made the same one in /var/lib/postgresql as well, but it's the same error.

On the other hand, the data-atom plugin for Atom just works.

[tkopets]

@xnmp Please try the new version (v0.9.2) - there is an ability to set PostgreSQL password directly in Connection file.

[mtxr]

I just developed this feature in the vscode version.

Ref. mtxr/vscode-sqltools@07443436ff69132ccf8644bb1e55d3f429facc2f

I plan to do it here on Saturday.

[mtxr]

/remind me to do this in two days

[reminders[bot]]

@mtxr set a reminder for Saturday at 9:00 AM

[reminders[bot]]

:wave: @mtxr, do this

[bclinthall]

@mtxr, In December, you were hoping to implement this same Password Prompt feature for sublime that you did in vscode. Is that still something you're planning to do?

[mtxr]

@bclinthall I'm a bit far from Sublime Text for while. @tkopets is maintaining it for most cases.

Anyway, I'll take a look at Sublime recent updates and check a way to explore it and request the password.

[tkopets]

This new feature is available in just published and most recent release v0.9.12. To use it just set any connection parameter value to JSON null value and you will be prompted for this parameter when a connection is selected.

Example

Note the line "password": null,

{
    "Connection PostgreSQL": {
      "type"    : "pgsql",
      "host"    : "127.0.0.1",
      "port"    :  5432,
      "database": "dbname",
      "username": "anotheruser",
      "password": null,
      "encoding": "utf-8"
    },
    "default": null
}