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