Postgresql in Putty
Uploaded by
mr.anonymous08102003Postgresql in Putty
Uploaded by
mr.anonymous08102003POSTGRESQL IN PUTTY
** PRE – REQUIIREMENTS**:
• Create two databases, and schemas for each. Create tables in both the
databases.
• Make a Super user and a Read-only user for the data.
- A Super user is the one which can have all the access in the database.
i.e, a Super user can read, update, delete, and drop the values in the
corresponding tables.
- A Read-only user is the one who can see all the data from the tables but
cannot edit such as update, delete, or drop the values in the table.
• Database1 user shouldn’t be able access the data from the Database2
** COMPLETE SETUP **
STEP 1: Set Up PostgreSQL
- sudo apt update
- sudo apt install postgresql postgresql-contrib -y
- enter the user id and password of the server in putty
STEP 2: Create Database, Schemas, Tables, and Insert Data
1. Connect to root server
-enter username and password
Command: su -
2. Switch to PostgreSQL user
- sudo -i -u postgres
- psql
3. . Create the databases
- CREATE DATABASE mydb1;
- \c mydb1
- CREATE DATABASE mydb1;
-\c mydb2
4. Showing the current databases
To show the database we use
- \l
5. Creating users with passwords:
-create user u1 with password ‘user@123’
-create user u2 with password ‘user@456’
-grant all permissions on database 1 to user1
and all permissions on database 2 to user 2
6. Creating tables and inserting values
-create separate tables in both the tables
-create table u2 (id serial primary key, name text, role text)
-insert into u2( id serial primary key, name text, role text)
values(1,’abc’,’xyz’);
7. Create schemas
- Create schema schema1 authorization u1
- Set searchpath to schema1
8. Super user and read only user
-create user s1 with superuser password ‘admin@123’
- create user s1 with read only password ‘read@123’
-grant all permissions of database2 to user2 and vice versa.
9. Removing access of user1 from database 2
-revoke all on database db1 from user2
-revoke all on schema schema1 from u2
10.Display of users(superuser, read only user):
- To check for the list of roles that are provided in the databases
are given below
- \du
11. Display of schema
- Select the database and change the path to it.
- \dt schema1.*;
