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
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'
References:
- Postgres Client auth methods
- PEER auth method
- What is a socket
- How Postgres stores user names
- Mapping OS user names to Postgres roles
Install Postgress with default settings
Skip this section if you already have Postgres installed.
# 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:
sudo -u postgres psql
References:
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]
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:
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"
}
]
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:
Top comments (0)