postgresql

https://www.cybertec-postgresql.com/en/error-permission-denied-schema-public/

change in psql 15

besides creating the database, role and granting privileges, one now has to also grant all on schema public of same database:

create database "example";
create user "example" login protected with password 'password';
grant all on database 'example' to example;
grant all on schema 'public.example' to example;

when you want to change user inside an existing session, do:

set role <EXAMPLE>;

to create an extension:

\c <DATABASE>
create extension <EXTENSION>;