PRACTICAL 5.VIEW CREATION INTO THE SCHEMA
--Create a view from "benz2.dept" table
postgres=# create view dept_v as select * from benz2.dept;
CREATE VIEW
postgres=# select * from dept_v;
dept_id | department
---------+------------
1011 | Research
1012 | Sales
(2 rows)
postgres=# \dv
List of relations
Schema | Name | Type | Owner
--------+--------+------+----------
public | dept_v | view | postgres
(1 row)
-- first grant the superuser privilege to "u2" user
postgres=# alter user u2 superuser;
ALTER ROLE
--connect as u2 user
[postgres@r1 ~]$ psql -U u2 -d postgres
Password for user u2:
--Create a schema and create a table and view within it as a super user
postgres=# CREATE SCHEMA books
CREATE TABLE databases (title text, release date, rating text)
CREATE VIEW postgres_view AS SELECT title, release FROM databases WHERE rating IS NOT NULL;
CREATE SCHEMA
--set the book search path for objects read
postgres=# set search_path to books;
SET
postgres=# show search_path;
search_path
-------------
books
(1 row)
postgres=# \dv
List of relations
Schema | Name | Type | Owner
--------+-------------+------+-------
books | postgres_view | view | u2
(1 row)
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+-------
books | databases | table | u2
(1 row)
PRACTICAL 6.CHANGING SEARCH_PATH
--Change the search path from "public" to "benz" First check the search path postgres=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | school | table | postgres public | school_bk | table | postgres public | school_bk2 | table | postgres public | student | table | postgres public | us_snail_addy | table | postgres (5 rows) --set search_path as benz postgres=# set search_path to benz; SET --see what search path you are currently using postgres=# show search_path; search_path ------------- benz (1 row) --list down benz schema's table postgres=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- benz | dept | table | postgres benz | emp | table | postgres (2 rows) --Now you can select emp table without pointing dot(benz.emp) but if you want see (public or benz2) another schema table you need to specify schema name like "benz2.dept" postgres=# select * from emp; dept_id | department ---------+------------ 1 | nijam 1 | nijam 2 | john (3 rows)
PRACTICAL 7.ASSIGN SEARCH_PATH PERMANANTLY AND PARTICULAR USER:
--List down the users postgres-# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- john | Superuser | {} postgres | Superuser, Create role, Create DB, Replication | {} u1 | | {} u2 | Superuser | {} u3 | | {} u5 | | {} u6 | | {} u7 | | {} u8 | --check the user u6 serach path after connected u6 user postgres=> show search_path ; search_path ---------------- "$user",public (1 row) --connect as super user(u2)then assign search path permanently for a user,here we are assigning two search path (schema) to particular user u6 postgres=# alter user u6 set search_path to sara,benz2; ALTER ROLE postgres=# GRANT create ON SCHEMA sara TO u6; GRANT It's from u6 user postgres=# \q [root@r1 bin]# ./psql -U u6 -d db2 postgres=> show search_path; search_path ------------- sara, benz2 (1 row) postgres=# \conninfo You are connected to database "postgres" as user "u6" via socket in "/tmp" at port "5432". postgres=# \q --ReConnect as "U6" user and check the search path correctly assigned or not [postgres@r1 ~]$ psql -U u6 -d postgres Password for user u6: postgres=> show search_path ; search_path ------------- sara, benz2 (1 row) postgres=> \dn+ List of schemas Name | Owner | Access privileges | Description ---------+----------+----------------------+------------------------ benz2 | postgres | postgres=UC/postgres+| | | u3=UC/postgres +| | | u5=UC/postgres +| | | u6=U/postgres +| | | u7=UC/postgres | books | u2 | | public | postgres | postgres=UC/postgres+| standard public schema | | =U/postgres | sara | u3 | u3=UC/u3 +| | | u5=C/u3 +| | | u6=C/u3 | schema1 | u2 | | schema2 | u2 | | schema5 | postgres | | schema6 | u2 | | (8 rows)
PRACTICAL 8.MULTIPLE SCHEMA ASSIGNING CURRENT USER:
--connect as u2 user using command line [postgres@r1 ~]$ psql -U u2 -d postgres Password for user u2: --check the search path of u2 user postgres=# show search_path; search_path ------------- books (1 row) --create two schema names are schema,schema2 postgres=# create schema schema1; CREATE SCHEMA postgres=# create schema schema2; CREATE SCHEMA --Now assigning multiple schema for current user postgres=# set search_path to schema1,schema2; SET --Now u2 user got two schema's search path postgres=# show search_path; search_path ------------------ schema1, schema2 (1 row) --if u2 user search the table in the above schema the user did'nt see any table becouse schema1 and schema2 are new schema currently nobody did not create objects(tables,views...etc) postgres=# \dt No relations found. Also, since schema1 is the first element in the path, new objects would by default be created in it
No comments:
Post a Comment