Passwordless Login for PostgreSQL using `peer` authentication method

[bikeshedder]

Is your feature request related to a problem? Please describe. I'm trying to connect to my database running at localhost using an unix domain socket without a password. I can run the psql command from the commandline without a password:

I don't have anything configured in my shell. There is no PGPASSWORD environment variable set nor is there some other kind of configuration file providing a password.

Describe the solution you'd like Add another option: "Without a password" or "Passwordless" since PostgreSQL differenciates between providing an empty password (auth method: md5) and no password at all (auth method: peer) when connecting. This is also possible for remote hosts (auth method: ident) which I've never used to be honest.

Describe alternatives you've considered I tried providing a connection string leaving out the password but the UI keeps asking me about one.

Additional context This is the default pg_hab.conf from Debian and Ubuntu systems and this makes it possible to log in using the system user without a password if the database username and system username matches:

I guess this is remotely related to #613 and #621

This is the SQLTools log btw.:

['sql:ext:error-handler'] Error opening connection password authentication failed for user "bikeshedder"
['sql:ext:error-handler:verbose'] [ResponseError: password authentication failed for user "bikeshedder"
    at /home/bikeshedder/.vscode/extensions/mtxr.sqltools-0.22.11/extension.js:16:111706
    at /home/bikeshedder/.vscode/extensions/mtxr.sqltools-0.22.11/extension.js:16:112113
    at Immediate.<anonymous> (/home/bikeshedder/.vscode/extensions/mtxr.sqltools-0.22.11/extension.js:16:112655)
    at processImmediate (internal/timers.js:439:21)
    at process.topLevelDomainCallback (domain.js:131:23)] {
  code: -32001,
  data: { driver: 'PostgreSQL', driverOptions: { pgOptions: [Object] } }
}

The problem is that it's trying a "password authentication" while it should not be providing a password at all.

[mtxr]

@bikeshedder can you share your connection config? masking sensitive data of course.

I'll take a look at this, thanks for reporting the issue.

[mtxr]

@bikeshedder try this:

{
    "name": "PGSQL PEER TEST",
    "driver": "PostgreSQL",
    "port": 5433
},

Worked here just fine. If it works for your env setup, I'll add the changes to the connection assistant to make easier to create connections like this.

[bikeshedder]

I've tried several variants. This is the one I have in my current config which was generated by the SQLTools frontend and then modified by me:

        {
            "database": "bikeshedder",
            "driver": "PostgreSQL",
            "name": "bikeshedder@localhost",
            "pgOptions": {
                "ssl": false
            },
            "port": 5432,
            "previewLimit": 50,
            "server": "localhost",
            "socketPath": "/run/postgresql",
            "username": "bikeshedder",
            "password": null
        }

I just tried your variant with the same result. An error popup appears:

And the log contains the error message I already showed in the first message.

[bikeshedder]

Something curious btw. is the following: A freshly created configuration via the UI contains the following:

        {
            "database": "tmtool",
            "driver": "PostgreSQL",
            "name": "tmtool@localhost",
            "pgOptions": {
                "ssl": {}
            },
            "port": 5432,
            "previewLimit": 50,
            "server": "localhost",
            "socketPath": "/run/postgresql",
            "username": "bikeshedder",
            "password": ""
        }

That gives me the error: "Error opening connection self signed certificate"

I don't think unix domain sockets support SSL so it seams it isn't using the socket file at all. Only after removing the pgOptions or changing it to {"ssl": false} I get the password error.

[bikeshedder]

I just got it working with the following configuration:

        {
            "name": "localhost",
            "driver": "PostgreSQL",
            "server": "/run/postgresql",
            "database": "bikeshedder"
        }

For some reason the socketPath seams to have no effect at all.

The display in the sidebar is a bit off, but at least it can now connect to the database:

The connection URL /run/postgresql/bikeshedder is somewhat odd. Yet it does indeed connect to the unix domain socket now and uses the correct DB. :tada:

One more thing I noticed - but I guess it isn't even related. When leaving out the database option I still only see the one database which is identical to my username. I guess SQLTools doesn't query the database names but just connects to the default db. I know PostgreSQL requires a connection per database and doesn't support switching the current DB within the same connection so that's probably to be expected.

[LiangLouise]

I just got it working with the following configuration:

        {
            "name": "localhost",
            "driver": "PostgreSQL",
            "server": "/run/postgresql",
            "database": "bikeshedder"
        }

For some reason the socketPath seams to have no effect at all.

The display in the sidebar is a bit off, but at least it can now connect to the database:

The connection URL /run/postgresql/bikeshedder is somewhat odd. Yet it does indeed connect to the unix domain socket now and uses the correct DB. 🎉

One more thing I noticed - but I guess it isn't even related. When leaving out the database option I still only see the one database which is identical to my username. I guess SQLTools doesn't query the database names but just connects to the default db. I know PostgreSQL requires a connection per database and doesn't support switching the current DB within the same connection so that's probably to be expected.

I also met the similar issue, as I tried to use option socket file and keep the server and port blank. But when testing connection, the driver would just ignore the socketPath and start to use the default 127.0.0.1:5432.

And I walked around the psql driver's code, this part of driver.ts looks quite like the source of the issue. the config didn't use the socketPath but always use server and port.

[kriswuollett]

It is a valid use case to connect to a database without a password and/or unix sockets when using a proxy such as Google Cloud SQL Proxy. The lack of the passwordless login in this plugin prevents the use of it when running on a cloud-based remote development environment that uses Google Cloud IAM to control access to databases.

[rimutaka]

An update as of v0.28.1

Connection settings

 {
      "name": "PGSQL PEER TEST",
      "server": "/run/postgresql",
      "driver": "PostgreSQL",
      "database": "postgres"
  }

See https://dev.to/rimutaka/passwordless-login-to-postgres-from-vscode-sqltools-extension-using-identpeer-method-434l post for detailed explanations.