# - bof - #





:1
# -  - #
# - Installation - #

# -  - #
# - Update - #
sudo apt update; sudo apt upgrade -y; sudo apt autoremove -y;

:1.1
# -  - #
# - On Server - #
sudo apt install postgresql-16 postgresql-client-16 -y;

:1.2
# -  - #
# - On Client (Ubuntu) - #
sudo apt install postgresql-client-16 -y;

:1.3
# -  - #
# - Check PostgreSQL Status - #
systemctl status postgresql.service;
systemctl status postgresql@16-main.service;





:2
# -  - #
# - Configuration - #

:2.1
# -  - #
# - Backup configuration files - #
sudo cp -p /etc/postgresql/16/main/pg_hba.conf \
           /etc/postgresql/16/main/pg_hba.conf.old;

sudo cp -p /etc/postgresql/16/main/postgresql.conf \
           /etc/postgresql/16/main/postgresql.conf.old;

:2.2
# -  - #
# - Edit pg_hba.conf - #
sudo vim /etc/postgresql/16/main/pg_hba.conf;

host    all             all             127.0.0.1/32            md5
host    all             all             192.168.0.0/24          md5
host    all             all             192.168.1.0/24          md5

# -  - #
# - Add my public IP - #
host    all             all             69.126.62.0/24          md5


:2.3
# -  - #
# - Edit postgresql.conf - #
sudo vim /etc/postgresql/16/main/postgresql.conf;

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'

:2.4
# -  - #
# - Check files properties - #
ls -lah /etc/postgresql/16/main;

-rw-r----- 1 postgres postgres 4.9K Apr 16 14:51 pg_hba.conf
-rw-r----- 1 postgres postgres 4.9K Apr 16 14:45 pg_hba.conf.old
-rw-r--r-- 1 postgres postgres  27K Apr 16 14:53 postgresql.conf
-rw-r--r-- 1 postgres postgres  27K Apr 16 14:45 postgresql.conf.old

:2.5
# -  - #
# - Restart PostgreSQL - #
sudo service postgresql restart;
sudo systemctl status postgresql;
systemctl status postgresql@16-main.service;





:3
# -  - #
# - PostgreSQL users configuration - #

:3.1
# -  - #
# - Create postgres user password in uServer - #
sudo bash;
passwd postgres;
Administrator2
exit

:3.2
# -  - #
# - Create postgres user password in PostgreSQL - #
sudo -u postgres psql postgres;
\password postgres
Administrator2
\q





:4
# -  - #
# - Test PostgreSQL connections - #

# -  - #
# - Test PostgreSQL access - #

:4.1
# -  - #
# - Test 1 - #
su postgres;
psql
\q
exit

:4.2
# -  - #
# - Test 2 - #
sudo -i -u postgres;
psql
\q
exit

:4.3
# -  - #
# - Test 3 - #
sudo -u postgres psql;
\q

:4.4
# -  - #
# - Test 4 - #
sudo -u postgres psql postgres;
\q

:4.5
# -  - #
# - Access from a client terminal - #
sudo -u postgres psql -d postgres;
sudo -u postgres psql -d postgres -h 192.168.0.100;
sudo -u postgres psql -d postgres -h 192.168.1.100;
sudo -u postgres psql -d postgres -h 192.168.1.200;
sudo -u postgres psql -d postgres -h 192.168.122.100;
psql -U postgres -d postgres -h www.acecom.one;
psql -U postgres -d postgres -h www.isdevelopment.us;
psql -U is_derayo -d postgres -h www.isdevelopment.us;


\q





:5
# -  - #
# - Create & test admin user - #

:5.1
# -  - #
# - Create admin user - #
sudo -u postgres psql postgres;

CREATE ROLE is_derayo WITH LOGIN PASSWORD 'Administrator2' SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS;
#ALTER USER is_derayo WITH SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS;
GRANT ALL PRIVILEGES ON DATABASE postgres TO is_derayo;
GRANT ALL ON ALL TABLES IN SCHEMA public TO is_derayo;

CREATE ROLE cvandemberg WITH LOGIN PASSWORD 'Admin22' SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS;
#ALTER USER cvandemberg WITH SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS;
GRANT ALL PRIVILEGES ON DATABASE postgres TO cvandemberg;
GRANT ALL ON ALL TABLES IN SCHEMA public TO cvandemberg;
\q

:5.2
# -  - #
# - Test admin user - #

sudo -u is_derayo psql -d postgres -h 192.168.1.100;
sudo -u is_derayo psql -d postgres -h 192.168.1.120;
sudo -u is_derayo psql -d postgres -h 192.168.1.200;
sudo -u is_derayo psql -d postgres -h www.isdevelopment.us;


sudo -u cvandemberg psql -d postgres -h 192.168.0.100;
sudo -u cvandemberg psql -d postgres -h www.acecom.one;

\q





# - eof - #