Master in DevOps | New Batch Starting from 19 December, 2024 @ 9 PM IST | Register for Free Demo

Know More

Schema management in Postgresql

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Schema management in Postgresql
  • User AvatarAshiwini
  • 06 Aug, 2024
  • 0 Comments
  • 7 Mins Read

Schema management in Postgresql

postgres=# \du 
                        List of roles 
Role name    |          Attributes                                       | Member of 
-------------+-----------------------------------------------------------+----------- 
learnomate   | Superuser                                                 | {} 
postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS| {} 
u2           |                                                           | {} 
tom          |                                                           | {}
postgres=# create user rajesh with password 'test123';
CREATE ROLE
postgres=# \du
                 List of roles 
Role name    |    Attributes                                             | Member of 
-------------+-----------------------------------------------------------+----------- 
learnomate   | Superuser                                                 | {} 
postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS| {} 
u1           |                                                           | {} 
u2           |                                                           | {}
tom          |                                                           | {} 
postgres=#  

postgres=#  
postgres=#  
postgres=# alter user learnomate nosuperuser; 
ALTER ROLE 
postgres=# \du 
postgres=# create user rajesh with password 'test123';
CREATE ROLE
postgres=# \du
                 List of roles 
Role name    |    Attributes                                             | Member of 
-------------+-----------------------------------------------------------+----------- 
learnomate   |                                                           | {} 
postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS| {} 
u1           |                                                           | {} 
u2           |                                                           | {}
tom          |                                                           | {} 
postgres=#  
postgres=# \c prod learnomate 
connection to server on socket "/run/postgresql/.s.PGSQL.5002" failed: FATAL: no pg_hba.conf entry for host "[local]", user "learnomate", database "prod", no encryption 
Previous connection kept 
postgres=# \q
-bash-4.2$ pwd
/app01/test_data/pg_tblspc 
-bash-4.2$ cd .. 
-bash-4.2$ vi pg_hba.conf
-bash-4.2$ pg_ctl -D /app01/test_data/ reload
server signaled
-bash-4.2$ psql -p 5002 -U postgres
psql (15.7)

Type "help" for help.
postgres=# \c prod learnomate 
Password for user learnomate: 
connection to server on socket "/run/postgresql/.s.PGSQL.5002" failed: FATAL: permission denied for database "prod" 
DETAIL: User does not have CONNECT privilege. Previous connection kept 

Previous connection kept
postgres=# \l+ prod 
postgres=# GRANT CONNECT ON DATABASE prod TO learnomate, u1; 
GRANT

postgres=# \c prod learnomate 
Password for user learnomate: 
You are now connected to database "prod" as user "learnomate". 

prod=> CREATE SCHEMA learnomate AUTHORIZATION learnomate;
ERROR: permission denied for database prod 

prod=> \c prod postgres
You are now connected to database "prod" as user "postgres". 

prod=# GRANT CREATE ON DATABASE prod TO learnomate, u1; 
GRANT 

prod=# \c prod learnomate 
Password for user learnomate: 
You are now connected to database "prod" as user "learnomate". 

prod=> CREATE SCHEMA learnomate AUTHORIZATION learnomate; 
GRANT

prod=# \c prod learnomate 
Password for user learnomate: 
You are now connected to database "prod" as user "learnomate". 

prod=> CREATE SCHEMA learnomate AUTHORIZATION learnomate; 
CREATE SCHEMA 

prod=> \dn

      List of schemas
 Name        | Owner 
-------------+------------------- 
learnomate   | learnomate 
public       | pg_database_owner 
(2 rows)
prod=> show search_path; 
search_path
----------------- 
"$user", public
(1 row)
prod=> SET search_path TO learnomate;
SET 

prod=> SHOW search_path; 

search_path
------------- 
learnomate
(1 row)
prod=> CREATE TABLE tb1(id integer, name varchar(20)); 
CREATE TABLE 

prod=> CREATE TABLE tb2(id integer, name varchar(20)); 
CREATE TABLE

prod=> CREATE TABLE tb3(id integer, name varchar(20));
CREATE TABLE
Listing tables:
prod=> \dt
      List of relations 
Schema      | Name | Type | Owner 
------------+------+-------+-----------
learnomate  | tb1  | table| learnomate 
learnomate  | tb2  | table| learnomate 
learnomate  | tb3  | table| learnomate
(3 rows)

prod=> show search_path;
search_path 
----------------- 
"$user", public (1 row)
Setting the search_path to u1:
prod=> SET search_path TO u1; 
SET
Creating Tables in the u1 Schema:
prod=> CREATE TABLE p1(id integer, name varchar(20)); 
CREATE TABLE 

prod=> CREATE TABLE p2(id integer, name varchar(20)); 
CREATE TABLE 

prod=> CREATE TABLE p3(id integer, name varchar(20));
CREATE TABLE

Inserting Data into Tables:

prod=> INSERT INTO p1 VALUES(generate_series(1,10), 'learnomate'); 
INSERT 0 10 

prod=> INSERT INTO p2 VALUES(generate_series(1,10), 'learnomate'); 
INSERT 0 10 

prod=> INSERT INTO p3 VALUES(generate_series(1,10), 'learnomate'); 
INSERT 0 10

Listing Tables in the u1 Schema:

prod=> \dt 
    List of relations 
Schema  | Name | Type  | Owner 
--------+------+-------+------- 
u1      | p1   | table | u1 
u1      | p2   | table | u1 
u1      | p3   | table | u1 
(3 rows)
prod=> select current_user;
current_user 
-------------- 
u1 
(1 row)
prod=> set search_path to learnomate; 
SET 
prod=> \dt 
Did not find any relations.

Switching to User learnomate:

prod=> \c prod learnomate
Password for user learnomate:
You are now connected to database "prod" as user "learnomate".

Granting Usage on the learnomate Schema to User u1:

prod=> GRANT USAGE ON SCHEMA learnomate TO u1; GRANT

Connecting as User u1:

prod=> \c prod u1
Password for user u1:
You are now connected to database "prod" as user "u1".

Setting the search_path:

prod=> SET search_path TO learnomate; 
SET
prod=> \dt 

        List of relations
Schema     | Name | Type  | Owner 
-----------+------+-------+-----------
learnomate | tb1  | table | learnomate
learnomate | tb2  | table | learnomate
learnomate | tb3  | table | learnomate
(3 rows)
prod=> select * from tb1; 
ERROR: permission denied for table tb1 
prod=> create table tb4(id int); 
ERROR: permission denied for schema learnomate 
LINE 1: create table tb4(id int);

Granting CREATE Privilege:

prod=> \c prod learnomate 
Password for user learnomate: 
You are now connected to database "prod" as user "learnomate".
prod=> GRANT CREATE ON SCHEMA learnomate TO u1; 
GRANT

Granting SELECT Privilege:

prod=> GRANT SELECT ON ALL TABLES IN SCHEMA learnomate TO u1; 
GRANT
 prod=> \dn+ learnomate 
             List of schemas
Name       | Owner      | Access privileges        | Description
-----------+------------+--------------------------+-------------
learnomate | learnomate | learnomate=UC/learnomate+|
           |            | u1=UC/learnomate         |
(1 row)

prod=> \z+ learnomate.tab1 
invalid command \z+ 
Try \? for help.
prod=> \z learnomate.tb1 

            Access privileges
Schema     | Name | Type  | Access privileges | Column     | Policies 
                                                privileges 
-----------+------+-------+-------------------+------------+----------
learnomate | tb1  | table |learnomate=arwdDxt/|            |
                            learnomate+
           |      |       | u1=r/learnomate   |            |
(1 row)


prod=> grant select,insert on all tables in schema learnomate to u1; 
GRANT 

prod=> \z learnomate.tb1 

                  Access privileges 
Schema     | Name | Type  | Access privileges  | Column    | Policies 
                                                privileges   
-----------+------+-------+--------------------+-----------+---------- 
learnomate | tb1  | table |learnomate=arwdDxt/ |           | 
                           learnomate+   
           |      |       | u1=ar/learnomate   |           | 
(1 row)

Switching to User u1:

prod=> \c prod u1 
Password for user u1: 
You are now connected to database "prod" as user "u1". 
prod=> set search_path to learnomate 
prod-> ; 
SET 

prod=> select * from tb1;

id | name 
---+------------
 1 | learnomate  
 2 | learnomate 
 3 | learnomate 
 4 | learnomate 
 5 | learnomate 
 6 | learnomate 
 7 | learnomate 
 8 | learnomate 
 9 | learnomate 
 10| learnomate 
(10 rows)

prod=> insert into tb1 values(generate_series(11,15),'learnomate'); 
INSERT 0 5 
prod=> select * from tb1;
id | name 
---+------------
 1 | learnomate  
 2 | learnomate 
 3 | learnomate 
 4 | learnomate 
 5 | learnomate 
 6 | learnomate 
 7 | learnomate 
 8 | learnomate 
 9 | learnomate 
 10| learnomate
 11| learnomate
 12| learnomate
 13| learnomate
 14| learnomate
 15| learnomate 
(15 rows)

prod=> create table tb4(id integer,name varchar(20));
CREATE TABLE 
prod=> \dt 
      
    List of relations 
Schema     | Name| Type  | Owner 
-----------+-----+-------+-------- 
learnomate | tb1 | table | learnomate 
learnomate | tb2 | table | learnomate 
learnomate | tb3 | table | learnomate 
learnomate | tb4 | table | u1

(4 rows)

prod=> \c prod learnomate 
Password for user learnomate: 
You are now connected to database "prod" as user "learnomate". 

prod=> set search_path to learnomate; 
SET

prod=> select * from tb4; 
ERROR: permission denied for table tb4 
prod=> \dn+ learnomate 

          List of schemas
Name      | Owner      | Access privileges        | Description
----------+------------+--------------------------+-------------
learnomate| learnomate | learnomate=UC/learnomate+|
          |            | u1=UC/learnomate         |
(1 row)


prod=> \z tb1
                            Access privileges 
Schema     | Name | Type  | Access privileges  | Column privileges | Policies 
-----------+------+-------+--------------------+-------------------+----------
learnomate | tb1  | table | learnomate=arwdDxt
                                   /learnomate+|                  |
           |       |      | u1=ar/learnomate   |                  | 
(1 row)
prod=> \c prod u1 
Password for user u1:  
You are now connected to database "prod" as user "u1". 

prod=> show search_path; 

search_path  
----------------- 
"$user", public 
(1 row) 
prod=> \c prod postgres
You are now connected to database "prod" as user "postgres". 

prod=# alter user learnomate set search_path to learnomate;
ALTER ROLE
prod=# alter user u1 set search_path to u1;
ALTER ROLE

prod=# select pg_reload_conf();
pg_reload_conf 
----------------
t
(1 row)


prod=# \c prod learnomate 
Password for user learnomate:  
You are now connected to database "prod" as user "learnomate". 

prod=> show search_path; 
search_path  
------------- 
learnomate
(1 row)

prod=> \dt

List of relations 
Schema     | Name | Type  | Owner 
-----------+------+-------+-------- 
learnomate | tb1  | table | learnomate 
learnomate | tb2  | table | learnomate 
learnomate | tb3  | table | learnomate 
learnomate | tb4  | table | u1 
(4 rows) 


prod=> \c prod u1 
Password for user u1: 
You are now connected to database "prod" as user "u1". 

prod=> \dt 
     List of relations 
Schema | Name | Type   | Owner 
--------+------+-------+-------- 
u1     | p1   | table  | u1 
u1     | p2   | table  | u1 
u3     | p3    | table | u1 
(3 rows) 
prod=> \c prod learnomate 
Password for user learnomate: 
You are now connected to database "prod" as user "learnomate".

prod=> create schema dept; 
CREATE SCHEMA  

prod=> \dn

Name          | Owner 
--------------+-------------------
dept          | learnomate 
learnomate    | learnomate 
public        | pg_database_owner 
u1            | u1 
(4 rows)

prod=> set search_path to dept; 
SET 
prod=> create table dep1(id integer,name varchar(20)); 
CREATE TABLE 
prod=> create table dep2(id integer,name varchar(20)); 
CREATE TABLE 
prod=> create table dep3(id integer,name varchar(20)); 
CREATE TABLE 
prod=> set search_path to dept,learnomate; 
SET 

prod=> \dt

       List of relations
Schema     | Name | Type | Owner 
-----------+------+-------+------------
dept       | dep1 | table | learnomate 
dept       | dep2 | table | learnomate 
dept       | dep3 | table | learnomate 
learnomate | tb1  | table | learnomate 
learnomate | tb2  | table | learnomate 
learnomate | tb3  | table | learnomate 
learnomate | tb4  | table | u1 
(7 rows)
prod=> create table test(id integer,name varchar(20)); 
CREATE TABLE 
prod=> \dt 

    List of relations 
 Schema    | Name | Type | Owner 
-----------+------+-------+-------- 
dept       | dep1 | table | learnomate 
dept       | dep2 | table | learnomate 
dept       | dep3 | table | learnomate 
dept       | test | table | learnomate 
learnomate | tb1  | table | learnomate 
learnomate | tb2  | table | learnomate 
learnomate | tb3  | table | learnomate 
learnomate | tb4  | table | u1 
(8 rows)

 

Introduction to PostgreSQL: A Beginner’s Guide
Overview of the Program Global Area (PGA) and User Global Area