HOW TO CREATE DATABASE AND USER CREATION & GRANT PRIVILEGES TO USER & LOGIN FROM PSQL
Mon Feb 21 2022 07:03:56 GMT+0000 (Coordinated Universal Time)
Saved by
@irfan309
#psql
#database-creation
#login-to-database
#user-creation
#grant-privileges-to-user
#login-to-db-as-create-user
#user-creation-with-password
#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.
content_copyCOPY
Comments