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
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=#
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)
<<< Preview Next>>>
Comments
Post a Comment