UP | HOME

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.

1. Setup

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:

psql

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:

psql

2. Usage

2.1. If your app supports peer-authentication

Now you may create databases for your apps at your leisure just by connecting with psql and running CREATE DATABASE:

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 made some changes to the permissions which causes a lot of errors about permission denied for schema public.

The simplest fix, assuming the database is just accessed by one user, is to just make your user the owner:

alter database my_app owner to my_app;

Copyright 2021 Joseph Graham (joseph@xylon.me.uk)