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