#opening postgresql config file to enabling from local to everyone
#firt open this file on machine to make changes
sudo vi /etc/postgresql/12/main/postgresql.conf
#to quickly search on editor easily type as '/' & the type word to search 'listen_address'
/listen_address
#by default listen_addresses = 'localhost'
#so we are changing 'localhost' to '*' it means by placing the '*' any one can access.
listen_addresses = '*'
#now change one more config file called 'pg_hba.config' to give access, so open the file as below
sudo vi /etc/postgresql/12/main/pg_hba.conf

# add the line end of the file 
host    all             all             0.0.0.0/0               md5

#for above mentioned line reference in  detailed 
https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html

# now restart the postgres so it will restart the service and apply the changes internally
# first check the status 
 sudo service postgresql status
 
# now restart the postgresql service
 sudo service postgresql status

#create database.
CREATE DATABASE <db-name>;

#to check databases
\d

# create a user with password;
# syntax- CREATE USER <user-name> WITH ENCRYPTED PASSWORD 'user-password';
CREATE USER john WITH ENCRYPTED PASSWORD 'john123';

# TO GRANT ALL PRIVILIGES TO ABOVE CREATED USER
# syn - GRANT ALL PRIVILEGES ON DATABASE <db-name> TO <user-name>
GRANT ALL PRIVILEGES ON DATABASE test_db TO john;


#now if check database by running this '\l' command to list out the databases  in access privileges column granted user also inclued there to 

# connecting to a database with above created user  on ubuntu postgres psql terminal 
#syntax -> psql -h <host-or-ip-address-or-endpoint> -p 5432 -d <data-base-name> -U <user-name>
psql -h localhost -p 5432 -d test -U dev1


# connecting to a database from local machine with pgadmin & psql on window as well
# below is the reference of connected to remote db from windows psql, observe below..

#when we open psql terminal on window it prompt like below we have to give the all.
# starts here

Server [localhost]: 2X.1XX.2XX.1XX           # this is the endpoint-or-ip-address-of-server
Database [postgres]: test_db                 # this is the database we created above 
Port [5432]:5432						     # port
Username [postgres]: john 			         # above created user
Password for user john: 				     # what ever passwd we created.
psql (14.2, server 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
WARNING: Console code page (437) differs from Windows code page (1252)
       8-bit characters might not work correctly. See psql reference
       page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256,         	compression: off)
Type "help" for help.

test_db=>

# end here, in above observe the 'test_db' its remote and successfully we connected.