Setting up Postgres locally with peer-authentication
Table of Contents
It's a common task to want a local database available for development purposes. This article tells you how to do this with PostgreSQL on Debian, with peer-authentication so you can login locally without a password.
Install it from apt:
sudo apt install postgresql
Note which version of Postgres you have installed. For me it is 13.
Now try to connect with:
If that doesn't work it's because by default the only user account is "postgres". It's a good idea to make a separate user for our purposes. By matching the Postgres username to your system username we can do "peer authentication" which means no password is required. Let's do that now.
sudo -u postgres psql
Now you are in the postgres shell:
create user yoursystemname with createdb createrole; create database yoursystemname; \du
Now quit out of that and test you can login as your username by simply typing:
2.1. If your app supports peer-authentication
Now you may create databases for your apps at your leisure just by
psql and running
psql create database my_app;
2.2. If your app requires a user+password
If your app requires a user+password, you may create database and user for your app like so (we are using database and username the same):
psql create database my_app; create user my_app with password 'password'; grant all on database my_app to my_app;
2.3. Schema issues with PostgreSQL 15
Postgres 15 requires a schema to be specified as they don't want you using the default.
So you must create one and configure this user to use that:
psql \c my_app create schema my_app; grant usage, create on schema my_app to my_app;
This works because the schema name matches the username. If the schema name does not match the username you will need to also:
alter user my_app set search_path to 'schemaname';