DEV Community


Posted on

Passwordless login to Postgres from VSCode SQLTools extension using IDENT/PEER method

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:

# 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
Enter fullscreen mode Exit fullscreen mode

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:

unix_socket_directories = '/var/run/postgresql'
Enter fullscreen mode Exit fullscreen mode


Install Postgress with default settings

Skip this section if you already have Postgres installed.

# add the PostgreSQL 16 repository
sudo sh -c 'echo "deb $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# import the repository signing key
curl -fsSL | 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
Enter fullscreen mode Exit fullscreen mode

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

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode


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:

sudo -u postgres createuser --superuser [your_linux_username]
Enter fullscreen mode Exit fullscreen mode

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.

Enter fullscreen mode Exit fullscreen mode

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


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:

"sqltools.connections": [
      "name": "PGSQL PEER TEST",
      "server": "/run/postgresql",
      "driver": "PostgreSQL",
      "database": "postgres"
Enter fullscreen mode Exit fullscreen mode


  • 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.


Top comments (0)