Creating a schema in PostgreSQL

A schema in PostgreSQL is a logical entity used to group together a list of tables, serving a specific purpose. It is not mandatory to create schemas in PostgreSQL. There exists a default public schema that can be used to create all tables. However, creating schemas has several advantages. Let's look at a few:

1. Let's say there are multiple applications writing to the same database. Each application's logic may serve a specific purpose. If we have a separate schema for each application's logic, it makes the lives of administrators and developers easier. This is because, if there is a need to perform maintenance on the tables related to a specific application, an admin can distinguish it using the schema.
2. In some applications, when a new user is created, the user is allocated a new set of objects that share the same name as the objects of another user in the same database. It is not possible to have two objects with the same name in a single schema. But, when we have two schemas, an object with the same name can exist in different schemas, for example:
   Table employee in user1 schema ⇒ user1.employee
   Table employee in user2 schema ⇒ user2.employee
3. User management can be simplified when we use schemas in PostgreSQL. If there are multiple application modules connecting to the same database, each module may need to access a certain set of tables. And each developer may need access to a specific set of objects related to that application module but not all. If schemas are used, users and applications can be granted read or read-write access to specific schemas through roles in a more simplified way.

In this recipe, we shall discuss the purpose of a schema and how it can be created.

Getting ready

To create a schema, we must have a PostgreSQL cluster that is running and can be connected using psql. Additionally, it requires either a user with the superuser role or ownership of the database to create a schema in the database.

How to do it...

The following are the steps involved in creating a schema in a PostgreSQL database:

1. Connect to the database:

$ psql -d percona
psql (13.1)
Type "help" for help.

percona=#

2. Create the schema using the CREATE SCHEMA command:

percona=# CREATE SCHEMA foo;
CREATE SCHEMA

3. Use IF NOT EXISTS for scripting purposes:

percona=# CREATE SCHEMA foo;
ERROR:  schema "foo" already exists
percona=# CREATE SCHEMA IF NOT EXISTS foo;
NOTICE:  schema "foo" already exists, skipping
CREATE SCHEMA

How it works...

In order to create a schema, you should first choose the database where you need to create the schema and connect to the database using psql, as seen in Step 1. Once you have connected to the database, you could simply use the command seen in Step 2 to create the schema.

Sometimes, you may wish to automate schema creation through scripting and avoid errors if the schema already exists. For that purpose, we could use the option seen in Step 3, to avoid printing an error when the schema already exists and create it only if it does not exist.

When you create a schema in PostgreSQL, the user who issued the CREATE SCHEMA command gets the ownership by default. This can be modified by using AUTHORIZATION. The following is the log that shows that the ownership is automatically assigned to current_user:

percona=# select current_user;
 current_user 
--------------
 postgres
(1 row)

percona=# CREATE SCHEMA foo;
CREATE SCHEMA
percona=# 
percona=# SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n 
WHERE n.nspname = 'foo';
 Name |  Owner   
------+----------
 foo  | postgres
(1 row)

In order to assign ownership of a schema to another user, either execute CREATE SCHEMA as that user or use AUTHORIZATION. You would need to grant the CREATE privilege to a user to execute CREATE SCHEMA. In the following log, user1 was already granted the CREATE privilege so CREATE SCHEMA succeeded with no errors:

$ psql -d percona -U user1
psql (13.1)
Type "help" for help.

percona=> select current_user;
 current_user 
--------------
 user1
(1 row)

percona=> CREATE SCHEMA foo;
CREATE SCHEMA

percona=> SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n 
WHERE n.nspname = 'foo';
 Name | Owner 
------+-------
 foo  | user1
(1 row)
percona=>

There's more...

When you are connecting as a superuser (postgres), you could grant the ownership to any user as you can see in the following log:

percona=# select current_user;
 current_user 
--------------
 postgres
(1 row)
percona=# CREATE SCHEMA foo AUTHORIZATION user1;
CREATE SCHEMA
percona=# 
percona=# SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n 
WHERE n.nspname = 'foo';
 Name | Owner 
------+-------
 foo  | user1
(1 row)

When you use AUTHORIZATION to give ownership of a schema to a non-superuser (user2) as a non-superuser (user1), the user executing CREATE SCHEMA (user1 here) should be a member of the role (user2) who should own the schema. In the following log, you can see an error when user1 is giving authorization to user2 without being a member of user2:

percona=> CREATE SCHEMA foo AUTHORIZATION user2;
ERROR:  must be member of role "user2"
postgres=# GRANT user2 to user1;
GRANT ROLE

percona=> CREATE SCHEMA foo AUTHORIZATION user2;
CREATE SCHEMA