POSTGRESQL SERVER ON UBUNTU SERVER 20.04

PostgreSQL or Postgres is an open-source general-purpose object-relational database management system with many advanced features that allows you to build fault-tolerant environments or complex applications. https://linuxize.com/post/how-to-install-postgresql-on-ubuntu-20-04/

root@snet[~]# sudo apt install postgresql postgresql-contrib postgresql-client -y

root@snet[~]# systemctl status postgresql.service

SETTING PASSWORD ON POSTGRES USER

root@snet[~]# sudo -u postgres psql postgres

root@snet[~]# postgres=# \password postgres

Enter password

root@snet[~]# postgres=# \q

CREATING OF NEW USER AND DATABASE

Be sure to change stephen to your preferred username and password to a strong password of your choosing as well as database name snet:

root@snet[~]# sudo -u postgres psql postgres

root@snet[~]# CREATE USER stephen WITH PASSWORD 'password';

root@snet[~]# CREATE DATABASE snet;

root@snet[~]# GRANT ALL PRIVILEGES ON DATABASE snet TO stephen;

root@snet[~]# postgres=# \q

ALLOWING LOCAL CONNECTION

root@snet[~]# sudo nano /etc/postgresql/12/main/pg_hba.conf

Change from local all postgres peer to local all postgres md5

Change from local all all peer to local all all md5

Change from host all all 127.0.0.1/32 md5 to host all all 0.0.0.0/0 md5

root@snet[~]# sudo systemctl restart postgresql

ENABLING REMOTE ACCESS IN POSTGRESQL

root@snet[~]# sudo nano /etc/postgresql/12/main/postgresql.conf

Uncomment the line and change localhost to ‘*’.

root@snet[~]# sudo systemctl restart postgresql.service

WHAT TO ON THE CLIENT MACHINE

The PostgreSQL Client can be used to connect to an external PostgreSQL database. Use this option if you already have a database server up and running, but need to be able to remotely access the database from one or more client systems. To get started, install the postgresql-client package.

root@snet[~]# sudo apt install postgresql-client

root@snet[~]# psql -h 192.168.1.2 (postgre-server) -U stephen (postgre-user)