• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Databases

      • Home
      • All courses
      • Databases
      • PostgreSQL Database
      CoursesIT & SoftwareDatabasesPostgreSQL Database
      • BASIC POSTGRESQL
        6
        • Lecture1.1
          PostgreSQL Brief History 30 min
        • Lecture1.2
          PostgreSQL Features 20 min
        • Lecture1.3
          PostgreSQL Monitoring Tools Overview 30 min
        • Lecture1.4
          PostgreSQL – Oracle Vs PostgreSQL 30 min
        • Lecture1.5
          PostgreSQL Data Types 30 min
        • Lecture1.6
          PostgreSQL Maximum Objects Size 30 min
      • VMWARE & POSTGRESQL INSTALLATION
        9
        • Lecture2.1
          Vmware Installation 30 min
        • Lecture2.2
          Creating Linux Virtual Machine 01 hour
        • Lecture2.3
          PostgreSQL Installation Types 30 min
        • Lecture2.4
          PostgreSQL GUI Installation 30 min
        • Lecture2.5
          PostgreSQL Text Mode Installation 30 min
        • Lecture2.6
          PostgreSQL Unattended Mode Installation 30 min
        • Lecture2.7
          Configure the network & Disk Partition 30 min
        • Lecture2.8
          How to install PostgreSQL10 On Linux Server ? 20 min
        • Lecture2.9
          PostgreSQL -11 Installation (rpm & source code) 30 min
      • POSTGRESQL DATABASE
        6
        • Lecture3.1
          Connect Postgres Server 10 min
        • Lecture3.2
          PostgreSQL startup / shutdown /restart the postgresql server 30 min
        • Lecture3.3
          PostgreSQL .bash_profile Set Up 30 min
        • Lecture3.4
          PostgreSQL Database Creation 30 min
        • Lecture3.5
          PostgreSQL Connect Database 30 min
        • Lecture3.6
          PostgreSQL Drop Database 30 min
      • POSTGRESQL TABLE
        16
        • Lecture4.1
          PostgreSQL Languages 30 min
        • Lecture4.2
          PostgreSQL Create Table 30 min
        • Lecture4.3
          PostgreSQL Select Table 30 min
        • Lecture4.4
          PostgreSQL Alter Table 30 min
        • Lecture4.5
          PostgreSQL Drop Table 30 min
        • Lecture4.6
          PostgreSQL Truncate Table 30 min
        • Lecture4.7
          PostgreSQL Rename 30 min
        • Lecture4.8
          PostgreSQL Comment 30 min
        • Lecture4.9
          PostgreSQL Insert 30 min
        • Lecture4.10
          PostgreSQL Update 30 min
        • Lecture4.11
          PostgreSQL Table Delete 30 min
        • Lecture4.12
          PostgreSQL Merge Table 30 min
        • Lecture4.13
          PostgreSQL UNION 30 min
        • Lecture4.14
          PostgreSQL UNION ALL 30 min
        • Lecture4.15
          PostgreSQL INTERSECT 30 min
        • Lecture4.16
          PostgreSQL DISTINCT 30 min
      • USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
        3
        • Lecture5.1
          PostgreSQL Grant 30 min
        • Lecture5.2
          PostgreSQL Revoke 30 min
        • Lecture5.3
          Postgres user creation and restrict DDL statement & other database access 30 min
      • TRANSACTIONS - MVCC
        3
        • Lecture6.1
          PostgreSQL Commit 30 min
        • Lecture6.2
          PostgreSQL Rollback 30 min
        • Lecture6.3
          PostgreSQL Savepoint 30 min
      • POSTGRESQL USER/SCHEMA MANAGEMENT
        9
        • Lecture7.1
          PostgreSQL User Creation 30 min
        • Lecture7.2
          PostgreSQL User Creation Using Utility 30 min
        • Lecture7.3
          PostgreSQL Drop user 30 min
        • Lecture7.4
          PostgreSQL Drop User Using Utility 30 min
        • Lecture7.5
          PostgreSQL Password Changing and Views 30 min
        • Lecture7.6
          PostgreSQL Group/role 30 min
        • Lecture7.7
          Alter PostgreSQL User/Role/group 30 min
        • Lecture7.8
          PostgreSQL Schema 30 min
        • Lecture7.9
          PostgreSQL user creation and restrict DDL & database access 30 min
      • POSTGRESQL CONSTRAINTS
        6
        • Lecture8.1
          PostgreSQL Constraints 30 min
        • Lecture8.2
          PostgreSQL Primary Key 30 min
        • Lecture8.3
          PostgreSQL UNIQUE Constraint 30 min
        • Lecture8.4
          PostgreSQL CHECK Constraint 30 min
        • Lecture8.5
          PostgreSQL NOT NULL Constraint 30 min
        • Lecture8.6
          PostgreSQL Foreign Key 30 min
      • POSTGRESQL ADVANCE DATA TYPE
        5
        • Lecture9.1
          PostgreSQL DOMAIN Data Type 30 min
        • Lecture9.2
          PostgreSQL Alter Domain 30 min
        • Lecture9.3
          PostgreSQL Drop DOMAIN 30 min
        • Lecture9.4
          PostgreSQL Json Data Type 30 min
        • Lecture9.5
          PostgreSQL Sequence 30 min
      • POSTGRESQL VIEWS
        1
        • Lecture10.1
          How to Create PostgreSQL View 30 min
      • POSTGRESQL MONITORING OBJECT USUAGE/SIZE
        1
        • Lecture11.1
          How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database 30 min
      • POSTGRESQL DATABASE ARCHITECTURE
        4
        • Lecture12.1
          PostgreSQL Tablespace 30 min
        • Lecture12.2
          PostgreSQL UnderStanding Of Objects 30 min
        • Lecture12.3
          PostgreSQL Architecture 30 min
        • Lecture12.4
          PostgreSQL Query Flow 30 min
      • POSTGRESQL BACKUP AND RECOVERY
        13
        • Lecture13.1
          PostgreSQL Backup & Recovery Overview 30 min
        • Lecture13.2
          PostgreSQL Database Backup Tools – Pg_dump 30 min
        • Lecture13.3
          PostgreSQL Database Backup Tools – Pg_dumpall 30 min
        • Lecture13.4
          PostgreSQL Database Backup Tools – Pg_basebackup 30 min
        • Lecture13.5
          PostgreSQL COPY data From Text File 30 min
        • Lecture13.6
          PostgreSQL COPY data to Text File 30 min
        • Lecture13.7
          PostgreSQL Clone Table 20 min
        • Lecture13.8
          Postgres Database Schema Migration 30 min
        • Lecture13.9
          PostgreSQL Database clone/migration 30 min
        • Lecture13.10
          PostgreSQL Compress backup format 30 min
        • Lecture13.11
          PostgreSQL Archivelog 30 min
        • Lecture13.12
          PostgreSQL Point In Time Recovery 30 min
        • Lecture13.13
          Taking Backup On Postgres Slave (Standby) Server 30 min
      • POSTGRESQL PERFORMANCE TUNING
        5
        • Lecture14.1
          PostgreSQL Index 30 min
        • Lecture14.2
          PostgreSQL Reindex 30 min
        • Lecture14.3
          PostgreSQL PerformanceTuning 30 min
        • Lecture14.4
          Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 min
        • Lecture14.5
          Postgres Autovacuum Configuration/Setup 30 min
      • HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
        11
        • Lecture15.1
          POSTGRESQL STREAMING REPLICATION 30 min
        • Lecture15.2
          SSL Streaming replication with PostgreSQL 10 30 min
        • Lecture15.3
          How to make Linux partition and configure the network on Redhat Linux7.3 30 min
        • Lecture15.4
          How To Fix Firewall Issues while connecting the other server? 30 min
        • Lecture15.5
          How to install the PostgreSQL10 On Linux ? 30 min
        • Lecture15.6
          How to Configure the cascade replication On PostgreSQL 10.3 ? 30 min
        • Lecture15.7
          How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 min
        • Lecture15.8
          PostgreSQL Switchover 30 min
        • Lecture15.9
          Postgres Failover 30 min
        • Lecture15.10
          Postgres Upgrade 30 min
        • Lecture15.11
          PostgreSQL Upgrade 9.5 to 11.3 . 30 min

        PostgreSQL Schema

        • PostgreSQL schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions.
        • Each user have their own schema and multiple schema
        • The schema search path can be altered so that each user find their tables first and access the other user tables also.
        • schema as a namespace for tables that also provides security.
        • By creating a schema for users and granting them rights on that schema,
        • The user will create tables on that schema instead of public.
        • schema search path is set which users objects  set in postgreSQL database.
        • default for all users $user, public.
        • search path can be set on a users basis, to look thorough any random list of schemas.
        Schema advantage:
        • To allow many users to use one database without interfering with each other.
        • To organize database objects into logical groups to make them more manageable.
        • Third-party applications can be put into separate schemas so they do not collide with the names of other objects.
        • Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.
        Syntax:

        create schema schema_name;
        CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ];
        CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ];
        CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ];
        CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name;
        PRACTICAL 1. CREATING SCHEMA

        --To create "benz" schema
        postgres=# create schema benz;
        CREATE SCHEMA
        
        --to  create a "dept" table in spcefic schema of "benz" as user default
        postgres=# create table benz.emp(dept_id integer,department varchar);       
        CREATE TABLE
        what is search path:
        search path which consist of just the table name. The system determines which
        table is meant by following a search path, which is a list of schemas to look in.
        The first matching table in the search path is taken to be the one wanted.
        If there is no match in the search path, an error is reported, even if matching
        table names exist in other schemas in the database.
        The first schema named in the search path is called the current schema. Aside
        from being the first schema searched, it is also the schema in which new tables

        will be created if the CREATE TABLE command does not specify a schema name.

        --Check the current search_path,By default such tables (and other objects) are 
        automatically put into a schema named "public"
        
        postgres=# show search_path;
          search_path   
        ----------------
         "$user",public
        (1 row)
        
        Below command is search the "emp" table in current search path that's why it shown 
        error if you want to see "emp" table you need to specify ("benz.emp") schema name 
        after that specify table name
        
        postgres=# \d emp
        Did not find any relation named "dept".
        Note:you cannot view the (emp table)another schema table from current schema 
        without specify schema name like following command
        
        postgres=# \d benz.emp;
                      Table "benz.emp"
           Column   |       Type        | Modifiers 
        ------------+-------------------+-----------
          dept_id   | integer           |

        PRACTICAL 2. SCHEMA VIEWS

        Here we gave some example for list down the roles,current Search path,Who is the owner of the table,user information and which schema is taken that table this is for just understanding purpose

        --List down the role & roles privilege 
        postgres=# \du
                                     List of roles
         Role name |                   Attributes                   | Member of 
        -----------+------------------------------------------------+-----------
         postgres  | Superuser, Create role, Create DB, Replication | {}
         u1        |                                                | {}
         u2        |                                                | {}
        
        --show the current search path
        postgres=# show search_path;
         "$user",public
        
        --List down the table owner,schemaname,tablename,and tablespace name
        postgres=# select schemaname,tablename,tableowner,tablespace from pg_tables where tablename='emp';                          
          schemaname | tablename | tableowner | tablespace 
        ------------+-----------+------------+------------
         benz       | emp       | postgres   | 
        (1 row)
        
        --List down user & schema
        postgres=# select schemaname,relname from pg_stat_user_tables;           
         schemaname |    relname    
        ------------+---------------
         public     | school_bk2
         public     | school_bk
         public     | us_snail_addy
         benz       | dept
         public     | student
         benz       | emp
         public     | school
        (7 rows)
        --insert some data into the "emp" table
        
        postgres=# insert into benz.emp values (1,'nijam');
        INSERT 0 1
        postgres=# insert into benz.emp values (1,'nijam');
        INSERT 0 1
        postgres=# insert into benz.emp values (2,'john'); 
        INSERT 0 1
        
        postgres=# select * from benz.emp;
         dept_id | department 
        ---------+------------
               1 | nijam
               1 | nijam
               2 | john
        (3 rows
        PRACTICAL 3. CREATE SAME NAME TABLE INTO THE DIFFRENT SCHEMA:
        you can create already exists table name but schema should be different inside the single schema you cannot create more than one table with same name

        --create dept table to benz2 schema
        postgres=# create table benz2.dept (dept_id integer, department varchar);
        CREATE TABLE
        
        --insert some data
        postgres=# insert into benz2.dept values(1002,'HR');
        INSERT 0 1
        postgres=# insert into benz2.dept values(1013,'Manager');
        INSERT 0 1
        
        --view the dept table from benz2 schema
        postgres=# select * from benz2.dept;
         dept_id | department 
        ---------+------------
            1002 | HR
            1013 | Manager
        (2 rows)
        
        --Create another dept table into the benz schema
        postgres=# create table benz.dept (dept_id integer, department varchar);
        CREATE TABLE
        
        --Check the "dept" table for which schema that is own
        postgres=# select schemaname,tablename,tableowner,tablespace from pg_tables where tablename='dept';
         schemaname | tablename | tableowner | tablespace 
        ------------+-----------+------------+------------
         benz       | dept      | postgres   | 
         benz2      | dept      | postgres   | 
        (2 rows)
        
        --fetch the two table
        postgres=# select * from benz.dept;
         dept_id | department 
        ---------+------------
        (0 rows)
        
        postgres=# select * from benz2.dept;
         dept_id | department 
        ---------+------------
            1002 | HR
            1013 | Manager
        (2 rows
        Note:- A user can create two table as similar name but different schemas
        PRACTICAL 4. LISTING THE SCHEMA

        --We can get a list of all tables in all schemas
        postgres=# \dt *.*
                                List of relations
               Schema       |          Name           | Type  |  Owner   
        --------------------+-------------------------+-------+----------
         benz2              | dept                    | table | postgres
         benz2              | t1                      | table | u7
         benz2              | t10                     | table | u3
         benz2              | t11                     | table | u5
         benz2              | t6                      | table | u2
         benz2              | t9                      | table | u2
         books              | databases               | table | u2
         information_schema | sql_features            | table | postgres
         information_schema | sql_implementation_info | table | postgres
         information_schema | sql_languages           | table | postgres
         information_schema | sql_packages            | table | postgres
         information_schema | sql_parts               | table | postgres
         information_schema | sql_sizing              | table | postgres
         information_schema | sql_sizing_profiles     | table | postgres
         pg_catalog         | pg_aggregate            | table | postgres
         pg_catalog         | pg_am                   | table | postgres
         pg_catalog         | pg_amop                 | table | postgres
         pg_catalog         | pg_amproc               | table | postgres
         pg_catalog         | pg_attrdef              | table | postgres
         pg_catalog         | pg_attribute            | table | postgres
         pg_catalog         | pg_auth_members         | table | postgres
         pg_catalog         | pg_authid               | table | postgres
         pg_catalog         | pg_cast                 | table | postgres
         pg_catalog         | pg_class                | table | postgres
         pg_catalog         | pg_collation            | table | postgres
         pg_catalog         | pg_constraint           | table | postgres
        -
        
        --WE can get a list of all tables In a particular schema:
        postgres-# \dt benz2.* 
                List of relations
         Schema | Name | Type  |  Owner   
        --------+------+-------+----------
         benz2  | dept | table | postgres
         benz2  | t1   | table | u7
         benz2  | t10  | table | u3
         benz2  | t11  | table | u5
         benz2  | t6   | table | u2
         benz2  | t9   | table | u2
        (6 rows)
        
        --USING VIEWS
        postgres=# select * from pg_tables where schemaname='benz2';
         schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers 
        ------------+-----------+------------+------------+------------+----------+-------------
         benz2      | dept      | postgres   |            | f          | f        | f
         benz2      | t6        | u2         |            | f          | f        | f
         benz2      | t9        | u2         |            | f          | f        | f
         benz2      | t10       | u3         |            | f          | f        | f
         benz2      | t1        | u7         |            | f          | f        | f
         benz2      | t11       | u5         |            | f          | f        | f
        (6 rows)
        
        --We can get a list of particular tables In a particular schema with expression
        postgres-# \dt (benz2|sara).(t*|t*)
               List of relations
         Schema | Name | Type  | Owner 
        --------+------+-------+-------
         benz2  | t1   | table | u7
         benz2  | t10  | table | u3
         benz2  | t11  | table | u5
         benz2  | t6   | table | u2
         benz2  | t9   | table | u2
         sara   | t1   | table | u2
        (6 rows)
        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
        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. BY USING CASCADE OPTION
        2. SIMPLE DROP METHOD
        1. BY USING 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)
        
        Prev Alter PostgreSQL User/Role/group
        Next PostgreSQL user creation and restrict DDL & database access

        ADVERTISEMENT

        Latest Courses

        PostgreSQL Database

        PostgreSQL Database

        $600.00 $500.00
        Greenplum Database

        Greenplum Database

        $2,000.00 $1,500.00

        Preview Course

        Free

        Latest Posts

        PostgreSQL Patching version 9, 10,11
        10Oct2019
        Tools for PostgreSQL
        16Sep2019
        Postgres user creation and restrict DDL & database access
        13Sep2019

        Recent Forum Topics

        • Are you going to take your first ste
        • How to start working on an application?
        • please let me know pre requirements to increase work_mem
        • how to copy some data in one table to another table in postgres
        • postgres script for finding queries more than 1 hours ?

        2ndquadrant.in

        (+91) 8838953252

        ITsupport@rayafeel.com

        Company

        • About Us
        • Contact
        • Our Team
        • Blog

        COURSES

        • List Of Course
        • Become An Instructor
        • Events
        • Postgres Support Blog

        Support

        • DBA Support
        • Consultancy Services
        • Postgres Migration Blogs
        • Forum

        Recommend

        • Groups
        • Login
        • FAQs
        • SignUp

        IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

        • Privacy
        • Terms

        Become An Instructor?

        Join thousand of instructors and earn money hassle free!

        Get Started Now

        Login with:

        Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

        Login with your site account

        Lost your password?

        Not a member yet? Register now

        Register a new account

        Are you a member? Login now

        Modal title

        Message modal