Sunday, 21 January 2024

What Is a Postgres Schema?

 



Schemas are collections of database objects that are logically grouped. These objects include tables, views, indexes, data types, functions, operators, and more. Schemas help segment objects in a database into separate namespaces which means objects with the same name can exist in different schemas without conflict.

Here are the important features of PostgreSQL Schemas:

==>The same database can contain one or more named schemas.
  • ==>Schemas help organize database objects into logical groups for clarity and ease of operation.

  • ==>Named database objects can share the same name in a single database but stay unique as long as they're housed in separate schemas.

  • ==>The Postgres public schema exists in every new database and is the default schema. If you create objects without specifying a schema, they are created in the public schema.

  • ==>PostgreSQL uses the schema search path order to determine which schema to check first when looking for an object. Typically, in the default search paths, the public schema gets the first look unless you change this order.


The purpose of a schema

Before you figure out how to use schemas, you need to know what the purpose of a schema is in the first place. To understand that, first take a look at how PostgreSQL is structured:

  • Instance
  • Database
  • Schema
  • Table
  • Row

An “instance” is basically what you start when you deploy PostgreSQL. The next layer is a database. In reality this is what you connect to: in PostgreSQL a connection is always bound to a database inside an instance, which happens early on, right after user authentication.
What is important is the next layer down, between databases and tables: Schemas.

Schemas group tables

Basically, schemas are a way to group tables together. Let’s assume there’s a fairly large data structure: Having 500 tables in one place is certainly harder to manage and understand than to have 10 buckets containing 50 tables each.
It’s simply like organizing pictures: You wouldn’t put all of them into the same folder, but rather group them by year, location, etc. The same logic can be applied to tables.

Schemas and PostgreSQL

Now we can focus on how this concept can be applied to PostgreSQL. The first thing we have to look at is the public schema.

Using the “public” schema

The beauty of PostgreSQL is that it doesn’t matter much if you know nothing at all about schemas. The reason is the existence of the public schema, which is there by default. How can we find out which schemas there are in PostgreSQL? 

psql provides the \dn command to display this information:


postgres=# \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 public | pg_database_owner
(1 row)


In a default scenario, a table will end up in the public schema. Here’s an example:

postgres=# CREATE TABLE t_product (
id      serial, 
name    text, 
price   numeric
);


This is a basic table. The table can be found in the desired schema. \d will reveal the truth:

postgres=# \d
                List of relations
 Schema |       Name       |   Type   |  Owner   
--------+------------------+----------+----------
 public | t_product        | table    | postgres
 public | t_product_id_seq | sequence | postgres
(2 rows)




In this case, both the schema and the sequence are found in the default schema as expected. As you can see, you don’t need any knowledge about schemas to proceed. If you happen to use the public schema, we also recommend checking out the new security policy introduced in recent versions of PostgreSQL.


Basically, there are two ways to query the table.

The first method is:

postgres=# SELECT * FROM t_product;

 id | name | price 
----+------+-------
(0 rows)


Another method to query the table

However, you can also explicitly use the schema name as a prefix to the table name, which constitutes a fully qualified name. I’ve seen a couple of ORM’s do exactly that– in order to reduce the risk of accessing the wrong table due to misconfiguration. We’ll also see it later in this post:

postgres=# SELECT * FROM public.t_product;
 id | name | price 
----+------+-------
(0 rows)

After this brief introduction to the public schema, we can move forward and create our first new schema.

Creating schemas

How can we create a schema in PostgreSQL? The CREATE SCHEMA command is the answer:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
postgres=# \h CREATE SCHEMA
Command:     CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name
[ AUTHORIZATION role_specification ]
[ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification
[ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name
[ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
 
where role_specification can be:
 
    user_name
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER
 
URL: https://www.postgresql.org/docs/15/sql-createschema.html

The syntax is quite easy and allows us to define a name as well as the schema owner. Otherwise, everything is really straightforward:


postgres=# 
postgres=# CREATE SCHEMA warehouse;
CREATE SCHEMA

Once the schema has been created, we can create a table inside the schema:

1
2
3
4
5
6
postgres=# CREATE TABLE warehouse.t_product (
   prod_number text PRIMARY KEY,
   d           date,
   in_stock    int
);
CREATE TABLE

By using a schema name as a prefix to the table name, you can define the schema you want to use. Mind that the schema itself does NOT impact the way data is stored. The data files associated with our table are still in the same PostgreSQL data directory. Therefore schemas do not impact performance and are not about storage optimization. The purpose of a schema is simply to group things together and to help organize a solid security policy by assigning permissions to schemas:


1
2
3
4
5
6
7
8
9
postgres=# \d warehouse.t_product;
              Table "warehouse.t_product"
   Column    |  Type   | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
 prod_number | text    |           | not null |
 d           | date    |           |          |
 in_stock    | integer |           |          |
Indexes:
    "t_product_pkey" PRIMARY KEY, btree (prod_number)

There are two things worth pointing out here: First of all, it is possible to have two tables with the same name in two different schemas. There is a public.t_product and a warehouse.t_product table. This is perfectly possible and actually quite common. The second important aspect is that we don’t have to prefix the table in the public schema. The reason is the following parameter:

1
2
3
4
5
postgres=# SHOW search_path;
   search_path  
-----------------
 "$user", public
(1 row)

VIMP:-

Everything that is to be found in the search_path can be accessed directly without explicitly providing the name of the schema. We can easily try this out:

1
2
postgres=# SET search_path TO warehouse;
SET

Note that the parameter is only changed in your session – it does not break your production system if you are running this in your interactive session.

From now on, the second table called t_product will be displayed, because PostgreSQL knows in which schema to look:

1
2
3
4
5
6
7
8
9
postgres=# \d t_product
              Table "warehouse.t_product"
   Column    |  Type   | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
 prod_number | text    |           | not null |
 d           | date    |           |          |
 in_stock    | integer |           |          |
Indexes:
    "t_product_pkey" PRIMARY KEY, btree (prod_number)

Now that the search_path has been changed, we have to prefix the public schema, as it is not in the path any more:

1
2
3
4
5
6
7
postgres=# \d public.t_product
                                Table "public.t_product"
 Column |  Type   | Collation | Nullable |                   Default                   
--------+---------+-----------+----------+-----------------...
 id     | integer |           | not null | …
 name   | text    |           |          |
 price  | numeric |           |          |

After this basic introduction to schemas, let’s figure out what it means to use schemas in combination with views:


Views and schemas

A view is a good way to allow developers easier access to data. The important point is that schemas are not normally a barrier (For the nitty-gritty details about views and permissions, see here). A query can freely join tables from different schemas, and the view using the query can expose the data in a schema of your choice (assuming you have the permissions to do that):

1
2
3
4
5
6
postgres=# SET search_path TO default;
SET
demo=# CREATE VIEW public.v AS
SELECT *
FROM warehouse.t_product ;
CREATE VIEW



Renaming schemas in PostgreSQL

In PostgreSQL, everything that can be created can also be renamed. The same is true for schemas:

1
2
3
4
5
6
7
8
9
postgres=# \h ALTER SCHEMA
Command:     ALTER SCHEMA
Description: change the definition of a schema
Syntax:
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
 
 
URL: https://www.postgresql.org/docs/15/sql-alterschema.html

Renaming a schema causes repercussions which are outlined in the next listing. Mind what happened to the view:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
postgres=# ALTER SCHEMA warehouse RENAME TO inventory;
ALTER SCHEMA
demo=# \d+ v
                                 View "public.v"
   Column    |  Type   | Collation | Nullable | Default | Storage  | …
-------------+---------+-----------+----------+---------+----------+ …
 prod_number | text    |           |          |         | extended |
 d           | date    |           |          |         | plain    |
 in_stock    | integer |           |          |         | plain    |
View definition:
 SELECT t_product.prod_number,
    t_product.d,
    t_product.in_stock
   FROM inventory.t_product;

The view does not reference tables directly – it references internal object IDs, which is really important here, because renaming the schema only means attaching a different text label to an internal ID. The actual view definition does not depend on names, so renaming objects does render a view invalid. By contrast, in databases such as Oracle, renaming objects can leave a view in an invalid state.

Dropping schemas

Dropping schemas in PostgreSQL follows the same logic:

1
2
3
4
5
6
7
8
postgres=# \h DROP SCHEMA
Command:     DROP SCHEMA
Description: remove a schema
Syntax:
DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
 
 
URL: https://www.postgresql.org/docs/15/sql-dropschema.html


Schema dependencies

We don’t want to orphan objects, so we can’t just drop them without collateral damage. PostgreSQL will tell us exactly what would happen, but not really drop the table – in order to avoid breaking the dependencies on the schema:

1
2
3
4
5
postgres=# DROP SCHEMA inventory;
ERROR:  cannot drop schema inventory because other objects depend on it
DETAIL:  table inventory.t_product depends on schema inventory
view v depends on table inventory.t_product
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

In case we really want to drop the schema and face all the consequences associated with it, the CASCADE option can be added:

1
2
3
4
5
postgres=# DROP SCHEMA inventory CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table inventory.t_product
drop cascades to view v
DROP SCHEMA

As you can see, all dependent objects have been dropped and we’re left with a clean, consistent database which doesn’t contain any stale or invalid objects.


Hands on :--------


postgres=# select datname from pg_database;
  datname  
-----------
 postgres
 template1
 template0
(3 rows)
postgres=# 
postgres=# \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 public | pg_database_owner
(1 row)
postgres=# \d
Did not find any relations.
postgres=# 
postgres=# 
postgres=# 
postgres=# CREATE TABLE t_product (
postgres(# id      serial, 
postgres(# name    text, 
postgres(# price   numeric
postgres(# );
CREATE TABLE
postgres=# 
postgres=# 
postgres=# \d
                List of relations
 Schema |       Name       |   Type   |  Owner   
--------+------------------+----------+----------
 public | t_product        | table    | postgres
 public | t_product_id_seq | sequence | postgres
(2 rows)
postgres=# 
postgres=# 
postgres=# SELECT * FROM t_product;
 id | name | price 
----+------+-------
(0 rows)
postgres=# 
postgres=# 
postgres=# SELECT * FROM public.t_product;
 id | name | price 
----+------+-------
(0 rows)
postgres=# 
postgres=# 
postgres=# CREATE SCHEMA warehouse;
CREATE SCHEMA
postgres=# 
postgres=# 
postgres=# 
postgres=# create database edbstore;
CREATE DATABASE
postgres=# 
postgres=# 
postgres=# 
postgres=# \c edbstore postgres
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
You are now connected to database "edbstore" as user "postgres".
edbstore=# 
edbstore=# 
edbstore=# select datname from pg_database;
  datname  
-----------
 postgres
 edbstore
 template1
 template0
(4 rows)
edbstore=# 
edbstore=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)
edbstore=# 
edbstore=# 
edbstore=# 
edbstore=# create user u01 password '123456';
CREATE ROLE
edbstore=# 
edbstore=# create user u02 password '123456';
CREATE ROLE
edbstore=# 
edbstore=# 
edbstore=#  \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 u01       |                                                            | {}
 u02       |                                                            | {}
edbstore=# 
edbstore=# select * from pg_shadow;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |                                                                pass
wd                                                                 | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+--------------------------------------------------------------------
-------------------------------------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t       | t            |                                                                    
                                                                   |          | 
 u01      |    16397 | f           | f        | f       | f            | SCRAM-SHA-256$4096:G5ATb67e7WkN5mXCkWpaaQ==$M92HrW0sTRX7XaaD2pE9UoD
sXD3JDfTr/jRojI07zFk=:xsy8fs9qZoZxCCV9KgpZzmaOiha5trLo6gqfotruqco= |          | 
 u02      |    16398 | f           | f        | f       | f            | SCRAM-SHA-256$4096:qUzgpLgPe2a2TYGFgqAtdA==$L6b03ess1L+Cj7jHiweyDpI
8JXOR5V1DKm7y74h4Vxs=:3giOIrsWNt3b8xcCKm/8YfAnhUW5OI//yqlxVJ+mN7o= |          | 
(3 rows)
edbstore=# 
edbstore=# \dn
      List of schemas
  Name  |       Owner       
--------+-------------------
 public | pg_database_owner
(1 row)
edbstore=# 
edbstore=# 
edbstore=# \c edbstore u01
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
You are now connected to database "edbstore" as user "u01".
edbstore=> 
edbstore=> create schema hr;
ERROR:  permission denied for database edbstore
edbstore=> 
edbstore=> 
edbstore=> \c edbstore postgres
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
You are now connected to database "edbstore" as user "postgres".
edbstore=# 
edbstore=# 
edbstore=# 
edbstore=# create schema hr authorization u01;
CREATE SCHEMA
edbstore=# 
edbstore=# 
edbstore=# 
edbstore=# create schema accounts authorization u02;
CREATE SCHEMA
edbstore=# 
edbstore=# 
edbstore=# \dn
       List of schemas
   Name   |       Owner       
----------+-------------------
 accounts | u02
 hr       | u01
 public   | pg_database_owner
(3 rows)
edbstore=# 
edbstore=# 
edbstore=# 
edbstore=# \c edbstore u01
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
You are now connected to database "edbstore" as user "u01".
edbstore=> 
edbstore=> 
edbstore=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)
edbstore=> 
edbstore=> 
edbstore=> set search_path to hr;
SET
edbstore=> 
edbstore=> 
edbstore=> show search_path;
 search_path 
-------------
 hr
(1 row)
edbstore=> 
edbstore=> create table hr_empinfo(empid int, empname varchar);
CREATE TABLE
edbstore=> 
edbstore=> 
edbstore=> insert into hr_empinfo values(1001,'amit');
INSERT 0 1
edbstore=> 
edbstore=> 
edbstore=> \dt
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 hr     | hr_empinfo | table | u01
(1 row)
edbstore=> 
edbstore=> 
edbstore=> \c edbstore u02 
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
You are now connected to database "edbstore" as user "u02".
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> \dn
       List of schemas
   Name   |       Owner       
----------+-------------------
 accounts | u02
 hr       | u01
 public   | pg_database_owner
(3 rows)
edbstore=> 
edbstore=> 
edbstore=> set search_path to hr;
SET
edbstore=> 
edbstore=> 
edbstore=> \dt
Did not find any relations.
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> \c edbstore u01
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
You are now connected to database "edbstore" as user "u01".
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> grant usage on schema hr to u02;
GRANT
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> \c edbstore u02
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
You are now connected to database "edbstore" as user "u02".
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> set search_path to hr;
SET
edbstore=> 
edbstore=> 
edbstore=> select * from hr_empinfo;
ERROR:  permission denied for table hr_empinfo
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> \c edbstore u01
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
You are now connected to database "edbstore" as user "u01".
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> grant select on all tables in schema hr to u02;
GRANT
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> \c edbstore u02
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
You are now connected to database "edbstore" as user "u02".
edbstore=> 
edbstore=> 
edbstore=> set search_path to hr;
SET
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> \dt
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 hr     | hr_empinfo | table | u01
(1 row)
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> select * from hr_empinfo;
 empid | empname 
-------+---------
  1001 | amit
(1 row)
edbstore=> 
edbstore=> 
edbstore=> 
edbstore=> \z hr_empinfo
ERROR:  operator is not unique: unknown || "char"
LINE 16:            E' (' || polcmd || E'):'
                          ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
edbstore=> 
edbstore=> 
edbstore=> select * from hr.hr_empinfo;
 empid | empname 
-------+---------
  1001 | amit
(1 row)
edbstore=> 
edbstore=> 
edbstore=> 





















at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Master and Slave - Sync check - PostgreSQL

  1) Run the below Query on Primary:- SELECT     pid,     usename,     application_name,     client_addr,     state,     sync_state,     sen...

  • PostgreSQL 15 Installation
    I have installed the PostgreSQL-15 on my Linux machine  Kindly use below link for the installation  https://www.postgresql.org/download/linu...
  • pg_basebackup in PostgreSQL
      Physical Backup:- pg_basebackup  pg_basebackup is a simple but powerful utility provided by PostgreSQL to take online and consistent file ...
  • How to Enable / Disable Archive Mode in PostgreSQL
      What is PostgreSQL WAL Archiving? Any transaction performed on the database is first written into a WAL file like redo logs in Oracle, the...