PostgreSqlUbuntu

Install PostgreSQL

sudo apt install postgresql postgresql-contrib

response: The following additional packages will be installed:
libllvm10 postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:
postgresql-doc postgresql-doc-12 libjson-perl openssl-blacklist isag
The following NEW packages will be installed:
libllvm10 postgresql postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common
postgresql-contrib ssl-cert sysstat
0 upgraded, 9 newly installed, 0 to remove and 10 not upgraded.

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) 12.2 (Ubuntu 12.2-4)

Create PostgreSQL Linux User Password

After installing PostgreSQL, it’s a good idea to create / change the default PostgreSQL user password

sudo passwd postgres

You should be prompted to create a new Linux password for postgres user.

New password:
Retype new password:
passwd: password updated successfully

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/10/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/10/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 clients 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.101.1/24 subnet,
host    all     all    192.168.101.1/24    md5

# ip of your webserver
host    all     all   xxx.xxx.xxx.xxx/32    md5

App database setup

let’s connect as the superuser “postgres” to make the changes:
sudo su -l postgres

Then use the psql command in an interactive shell when you want to create and manage PostgreSQL databases:
psql

On psql shell, run below command to change database admin password:
alter user postgres password 'new_db_admin_password';

Create a admin user called tethys_admin with new password:
postgres=# create user tethys_admin with encrypted password 'new_password_here';

Create an app user called tethys_app with new password:
postgres=# create user tethys_app with encrypted password 'new_password_here';

then create a database called tethys via psql:
postgres=# create database tethys;
use postgres ist default owner of db tethys

exit psql:
\q

and re-login into tethys db:
psql -d tethys -U postgres -p 5432

create a schemma 'gba' for all tethys tabels with full authorization for the user 'tethys_admin':
tethys=# CREATE SCHEMA IF NOT EXISTS gba AUTHORIZATION tethys_admin;

grant select, insert, update and delete privileges for the user 'tethys_app':\

grant usage on schema gba to tethys_app;

grant select, insert, update, delete on all tables in schema gba to tethys_app;
alter default privileges for role tethys_admin in schema gba grant select, insert, update, delete on tables to tethys_app;

grant usage on all sequences in schema gba to tethys_app;
alter default privileges for role tethys_admin in schema gba grant usage on sequences to tethys_app;

grant execute on all functions in schema gba to tethys_app;
alter default privileges for role tethys_admin in schema gba grant execute on functions to tethys_app;

exit psql:
\q

switch back to your os user:
exit

go mack to main installation