Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Schema -3

PRACTICAL 9.SCHEMA PRIVILEGES:
  • By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema. To allow users to make use of the objects in the schema, additional privileges might need to be granted, as appropriate for the object.
  • by default, everyone has CREATE and USAGE privileges on the schema public. This allows all users that are able to connect to a given database to create objects in its public schema. If you do not want to allow that, you can revoke that privilege

--Revoke create privilege from all user. Here  first "public" is the schema, 
the second "public" means "every user" after revoke privilege newly created user(U7) 
cannot create any objects on public schema also
postgres=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;

--Create u7 user
postgres=# create user u7 with password 'u7';
CREATE ROLE

--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        |                                                | {}
--connect as u7 user
[postgres@r1 ~]$ psql -U u7 -d postgres

--Create table on public & benz2 schema but we can't create table becouse we already 
revoke the privilege("REVOKE CREATE ON SCHEMA public FROM PUBLIC;")

postgres=> create table t1(id int);
ERROR:  permission denied for schema public
postgres=> create table benz2.t1(id int);
ERROR:  permission denied for schema benz2
--List down the "u7" privilege Here user u7 have not any privilege  so we need grant 
the privileges are "usage,create"

postgres=> \dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 benz2   | postgres | postgres=UC/postgres+| 
         |          | u3=UC/postgres      +| 
         |          | u5=U/postgres       +| 
         |          | u6=U/postgres        | 
 public  | postgres | postgres=UC/postgres+| standard public schema
         |          | =U/postgres          | 
 sara    | u3       |                      | 
 schema1 | u2       |                      | 
 schema2 | u2       |                      | 
 schema5 | postgres |                      | 
 schema6 | u2       |                      | 
(7 rows)
--Now grant the privilege create,usage to u7 user

postgres=# GRANT USAGE ON SCHEMA benz2 TO u7;
GRANT
postgres=# GRANT create ON SCHEMA benz2 TO u7;
GRANT

Note:To access a schema at all,for any action, the user must be granted 
"usage" rights. Before a user can select, insert, update, or delete, a user 
must first be granted "usage" to a schema.
--List the schemas privileges just see the user "u7" privilege UC is here U 
means "usage"privilege C mean "create"privilege

postgres=> \dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 benz2   | postgres | postgres=UC/postgres+| 
         |          | u3=UC/postgres      +| 
         |          | u5=U/postgres       +| 
         |          | u6=U/postgres       +| 
         |          | u7=UC/postgres       | 
 public  | postgres | postgres=UC/postgres+| standard public schema
         |          | =U/postgres          | 
 sara    | u3       |                      | 
 schema1 | u2       |                      | 
 schema2 | u2       |                      | 
 schema5 | postgres |                      | 
 schema6 | u2       |                      | 
(7 rows)
--Here we can create t1 table on benz2 schema only but we can't create t1 table on 
public schema becouse we didn't grant create privilege on public schema we already 

postgres=> revoked create privilege from public schema

postgres=> create table benz2.t1(id int);
CREATE TABLE

postgres=> create table t1(id int);
ERROR:  permission denied for schema public
--We have a group role called "staff" and would like to grant all (or certain) privileges 
to this role on tables in a particular schema

ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT SELECT ON TABLES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo REVOKE ...;

ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo GRANT ...;
ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo REVOKE ...;
This will set privileges automatically for objects created in the future - and not affect already-existing objects.

Note that default privileges are only applied to objects created by the targeted user (FOR ROLE my_creating_role). If that clause is omitted, it defaults to the current user (that executes ALTER DEFAULT PRIVILEGES).

PRACTICAL 10.DROPPING SCHEMA:
--check the connectoin info,search_path and schema informtion
postgres=> \dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 benz2   | postgres | postgres=UC/postgres+| 
         |          | u3=UC/postgres      +| 
         |          | u5=U/postgres       +| 
         |          | u6=U/postgres       +| 
         |          | u7=UC/postgres       | 
 public  | postgres | postgres=UC/postgres+| standard public schema
         |          | =U/postgres          | 
 sara    | u3       |                      | 
 schema1 | u2       |                      | 
 schema2 | u2       |                      | 
 schema5 | postgres |                      | 
 schema6 | u2       |                      | 
(7 rows)
postgres=# show search_path; 
   search_path    
------------------
 schema1, schema2
(1 row)

postgres=# \conninfo
You are connected to database "postgres" as user "u2" via socket in "/tmp" at port "5432".
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        | 

Note:i connected as u2 superuser he having two schema one is schema1 and another is schema2
--then create a t1 table and check the t1 table where to stored i'm telling that table 

stored whether schema1 or schema2

postgres=# create table t1 as select * from pg_tables;
SELECT 59

postgres=# select schemaname,relname from pg_stat_all_tables where relname='t1';
 schemaname | relname 
------------+---------
 public     | t1
 schema1    | t1
(2 rows)

Note:t1 table is stored in primary schema of schema1
Here We can drop schema two method one they are 

1.CASCADE OPTION

2.SIMPLE DROP METHOD

1.CASCADE OPTION:
if schema having any objects means when you dropping you need to specify Cascade Option Here i explained about cascade method  following example
mind it when you dropping schema1 the table t1 also will be droped.

postgres=# drop schema schema1;
ERROR:  cannot drop schema schema1 because other objects depend on it
DETAIL:  table t1 depends on schema schema1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

postgres=# drop schema schema1 cascade;
NOTICE:  drop cascades to table t1
DROP SCHEMA
--Check the table t1 And schema1 is properly droped or not
postgres=# \dt
No relations found.


postgres=> \dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 benz2   | postgres | postgres=UC/postgres+| 
         |          | u3=UC/postgres      +| 
         |          | u5=U/postgres       +| 
         |          | u6=U/postgres       +| 
         |          | u7=UC/postgres       | 
 public  | postgres | postgres=UC/postgres+| standard public schema
         |          | =U/postgres          | 
 sara    | u3       |                      | 
 schema2 | u2       |                      | 
 schema5 | postgres |                      | 
 schema6 | u2       |                      | 
(7 rows)
2.SIMPLE DROP METHOD

Here schema2 have not any objects also t1 table is stored in previous droped schema1 so you can use simple drop methode like following example
postgres=# drop schema schema2;
DROP SCHEMA
PRACTICAL 11.ASSIGN PARTICULAR SCHEMA FOR PARTICULAR USER: 
if we set a schema then created tables as user never going to public it always belongs to the particular schema we can maintain safley.
postgres=# set search_path to schema5;
SET
postgres=# show search_path;
 search_path 
-------------
 schema5
(1 row)
postgres=# \dt
                     List of relations
 Schema |           Name            | Type  |    Owner     
--------+---------------------------+-------+--------------
 schema3| dept                      | table | jack
 sys    | dual                      | table | enterprisedb
 sys    | edb$session_wait_history  | table | enterprisedb
 sys    | edb$session_waits         | table | enterprisedb
 sys    | edb$snap                  | table | enterprisedb
 sys    | edb$stat_all_indexes      | table | enterprisedb
 sys    | edb$stat_all_tables       | table | enterprisedb
 sys    | edb$stat_database         | table | enterprisedb
 sys    | edb$statio_all_indexes    | table | enterprisedb
 sys    | edb$statio_all_tables     | table | enterprisedb
 sys    | edb$system_waits          | table | enterprisedb
 sys    | plsql_profiler_rawdata    | table | enterprisedb
 sys    | plsql_profiler_runs       | table | enterprisedb
 sys    | plsql_profiler_units      | table | enterprisedb
 sys    | product_component_version | table | enterprisedb
(15 rows)

postgres=# create table tony(id number);
CREATE TABLE

postgres=# \dt
                     List of relations
 Schema |           Name            | Type  |    Owner     
--------+---------------------------+-------+--------------
 schema3| dept                      | table | jack
 schema3| tony                      | table | jack
 sys    | dual                      | table | enterprisedb
 sys    | edb$session_wait_history  | table | enterprisedb
 sys    | edb$session_waits         | table | enterprisedb
 sys    | edb$snap                  | table | enterprisedb
 sys    | edb$stat_all_indexes      | table | enterprisedb
 sys    | edb$stat_all_tables       | table | enterprisedb
 sys    | edb$stat_database         | table | enterprisedb
 sys    | edb$statio_all_indexes    | table | enterprisedb
 sys    | edb$statio_all_tables     | table | enterprisedb
 sys    | edb$system_waits          | table | enterprisedb
 sys    | plsql_profiler_rawdata    | table | enterprisedb
 sys    | plsql_profiler_runs       | table | enterprisedb
 sys    | plsql_profiler_units      | table | enterprisedb
 sys    | product_component_version | table | enterprisedb
(16 rows)

Comments

Popular posts from this blog

VMWARE WORKSTATION 3,4,5,6,7,8,9,10,11,12,14,15...etc LICENSE KEYS COLLECTION

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

Oracle to Postgresql migration

VBOX installation full guide