Schema management in Postgresql

• Ashiwini
• 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)