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.
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