This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
Step 1: Install PostgreSQL 14 on Ubuntu 22.04
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee /etc/apt/sources.list.d/pgdg.list \
response:
deb http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo apt install postgresql-14-postgis-3 postgresql-14-postgis-3-scripts
After installing PostgreSQL, the commands below can be used to stop, start, enable and check its status:
sudo systemctl stop postgresql.service
sudo systemctl start postgresql.service
sudo systemctl enable postgresql.service
sudo systemctl status postgresql.service
Viewing the server version
/usr/lib/postgresql/12/bin/postgres -V
response:
postgres (PostgreSQL) 14.4 (Ubuntu 14.4-1.pgdg22.04+1)
Step2: Configure CONNECTIONS AND AUTHENTICATION
postgresql.conf
Most global configuration settings are stored in postgresql.conf, which is created automatically when you install PostgreSQL. Open this file in your preferred text editor:
sudo nano /etc/postgresql/14/main/postgresql.conf
By default, Postgres only listens on localhost. However, by editing the listen_addresses-section and replacing localhost with an IP, you can force Postgres to listen on another IP. Use '*' to listen on all IP addresses, restrict access via firewall.
listen_addresses= '*'
pg_hba.conf
It’s now time to open the (in)famous pg_hba.conf configuration file, located at /etc/postgresql/10/main/pg_hba.conf:
sudo nano /etc/postgresql/14/main/pg_hba.conf
HBA stands for host-based authentication. Basically, this file is used to control how PostgreSQL users are authenticated.
host all all 127.0.0.1/32 md5
This line allows "all" users to login using TCP/IP ("host") from the localhost "127.0.0.1/32" to "all" databases, if they succeed in password authentication using the "md5" method. There are more password authentication methods (md5, scram-sha-256, gss, ldap, …) than we can cover, so let’s just get back to simpler examples.
In most cases the access is restricted to localhost and the management/client vlan e.g.:
# existing entry, allows connections from localhost
host all all 127.0.0.1/32 md5
# new entry to allow connections from 192.168.1.1/24 subnet,
host all all 192.168.1.1/24 md5
# ip of your webserver
host all all 172.16.4.48/32 md5
Step 3: Create Database
While in terminal, log in to the psql console as postgres user:
sudo su -l postgres
Create users 'gn_admin' and 'gn_app' with passwords:
Create a admin user called gn_admin with new password:
postgres=# create user gn_admin with encrypted password 'xxx';
CREATE ROLE
Create an app user called gn_app with new password:
postgres=# create user gn_app with encrypted password 'xxx';
CREATE ROLE
Create db 'gn_db' and schema 'gba':
create a database called tethys via psql:
postgres=# create database gn_db;
CREATE DATABASE
user postgres ist default owner of db gn_db
exit psql:
\q
and re-login into gn_db:
postgres@geomon:~$psql -d gn_db -U postgres -p 5432
change owner of schema 'public' to user 'gn_admin' for full authorization:
ALTER SCHEMA public OWNER TO gn_admin;
CREATE SCHEMA
Change user privileges:
-> grant select, insert, update and delete privileges for the user 'gn_app':
gn_db=# grant usage on schema public to gn_app;
GRANT
gn_db=# grant select, insert, update, delete on all tables in schema public to gn_app;
GRANT
Default "select, insert, update, delete privileges" on tables to sos_app
gn_db=# alter default privileges for role gn_admin in schema public grant select, insert, update, delete on tables to gn_app;
ALTER DEFAULT PRIVILEGES
Default usage privileges on sequences to sos_app
grant usage on all sequences in schema public to sos_app:
gn_db=# alter default privileges for role gn_admin in schema public grant usage on sequences to gn_app;
ALTER DEFAULT PRIVILEGES
Default execute privileges on function to sos_app
grant execute on all functions in schema public to sos_app:
alter default privileges for role gn_admin in schema public grant execute on functions to gn_app;
ALTER DEFAULT PRIVILEGES
Create extensions:
gn_db=# CREATE EXTENSION postgis;
CREATE EXTENSION
gn_db=# CREATE EXTENSION adminpack;
CREATE EXTENSION
gn_db=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
gn_db=# SELECT postgis_full_version();
exit to administrator user:
\q