Passwordless login to Postgres from VSCode SQLTools extension using IDENT/PEER method
Tags: vscode, postgres, beginners, tutorial
Published: 2024-01-31 19:16:29

This post explains how to connect to PostgreSQL using the current Linux user account with _IDENT/PEER_ authentication method.

* Postgres version: 16
* SQLTools extension version: 0.28.1

This may apply to other Postgres and SQLTools versions. Replace the version number in the paths and URLs as needed.

## About IDENT/PEER Authentication methods

_IDENT/PEER_ authentication method maps the currently logged-in Linux user name to the Postgres user name without a need for storing the password in Postgres or VSCode configuration files.

With _IDENT/PEER_ auth, instead of receiving the login and password from the user, Postgres gets the OS user name of the caller and maps it to the user name stored in its _pg_authid_ system catalog.
This method is enabled by default in _/etc/postgresql/16/main/pg_hba.conf_ file:

```conf
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
```

For the _PEER_ auth to work we need to connect via _sockets_, not TCP/IP.
A socket is a logical endpoint for communication that bypasses the networking hardware.


Postgres has a default socket at either: _/run/postgresql_ or _/var/run/postgresql_.
The exact location is configured in _/etc/postgresql/16/main/postgresql.conf_ file:

```conf
unix_socket_directories = '/var/run/postgresql'
```

References:

* [Postgres Client auth methods](https://www.postgresql.org/docs/current/auth-pg-hba-conf.html)
* [PEER auth method](https://www.postgresql.org/docs/current/auth-peer.html)
* [What is a socket](https://unix.stackexchange.com/a/193665)
* [How Postgres stores user names](https://www.postgresql.org/docs/current/catalog-pg-authid.html)
* [Mapping OS user names to Postgres roles](https://www.postgresql.org/docs/current/auth-username-maps.html)


## Install Postgress with default settings

Skip this section if you already have Postgres installed.

```bash
# add the PostgreSQL 16 repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# import the repository signing key
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

sudo apt update

# install PostgreSQL 16
sudo apt install postgresql-16

# start and enable PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# check the installed version
psql --version
```

You should be able to connect to Postgres via _psql_ utility with this command:

```bash
sudo -u postgres psql
```

References:

* [Postgres installation on Ubuntu](https://www.postgresql.org/download/linux/ubuntu/)
* [About pslq](https://www.postgresql.org/docs/current/app-psql.html)

## Add your current Linux user to Postgres

Postgres creates a new Linux user called _postgres_ to match the default superuser name in Postgres.
For _IDENT/PEER_ authentication to work you need to create a Postgres user matching your OS username:

```bash
sudo -u postgres createuser --superuser [your_linux_username]
```

You should be able to connect to Postgres without a need for `sudo -u postgres` from then on. For example, running `psql -d postgres` command should print something like this:

```
psql (16.1 (Ubuntu 16.1-1.pgdg23.04+1))
Type "help" for help.

postgres=#
```

Note that `postgres=#` prompt means you are a superuser. Otherwise, it would be `postgres=>` for less privileged Postgres users.

References:

* [_createuser_ utility](https://www.postgresql.org/docs/current/app-createuser.html)
* [multiple ways of creating Postgres users](https://phoenixnap.com/kb/postgres-create-user)

## Connecting VS Code to Postgres

Install SQLTools extension for VSCode if you have not done so.

Do not attempt to use the UI provided by the extension to configure PEER access.
Open _settings.json_ and add the following section:

```json
"sqltools.connections": [
    {
      "name": "PGSQL PEER TEST",
      "server": "/run/postgresql",
      "driver": "PostgreSQL",
      "database": "postgres"
  }
]
```
where

* _name_ - whatever you name it
* _server_ - leave as in the example for a default installation on Linux
* _driver_ - leave as is
* _database_ - use your DB name, if you have one, but "postgres" DB is a good option for the test because it's guaranteed to be there

Save the settings and you should see a new connection option appear in the SQLTools sidebar.

Try connecting to the server:

* click on _Connect_ icon against the connection you've just added
* you should get a popup saying _The extension wants to sign in using SQLTools Driver Credentials_
* click on _Allow_ and enter the password for the current Linux user

The extension saves the password via VSCode _SecretStorage API_ which is protected by the OS keyring. It is much more secure than storing it withing VSCode settings.

You can choose to save it permanently or only for the duration of the VSCode session by clicking on the _Key_ icon in the password dialog. You will have to enter the password again after restarting VSCode if you choose not to save it.

References:

* [SQLTools extension](https://marketplace.visualstudio.com/items?itemName=mtxr.sqltools)
* [SQLTools Postgres config](https://vscode-sqltools.mteixeira.dev/en/drivers/postgre-sql#11-specific-options)
* [SecretStorage API](https://code.visualstudio.com/api/references/vscode-api#SecretStorage)
* [Secret storage discussion on GitHub](https://github.com/microsoft/vscode-discussions/discussions/748)
* [GitHub issue discussing this topic](https://github.com/mtxr/vscode-sqltools/issues/667)