Introduction to PostgreSQL Schemas

‍

In PostgreSQL, a schema is like a folder that holds database objects such as tables, views, functions, sequences, and indexes. It helps organize data into logical groups, making database management easier.  

For example, in an online store application, we could have one schema for customer data, another for product information, and another for orders, ensuring better organization and accessibility.

By default, PostgreSQL creates a public schema where all objects are stored unless a different schema is specified. However, creating multiple schemas improves organization, enhances security, and simplifies access management.

‍

‍

Key Features of PostgreSQL Schemas

• Logical Grouping: Organizes database objects to avoid clutter.

• Flexibility: Allows us to create, delete, or rename schemas easily.

• Access Control: Provides better security and permissions management.

• Multi-Tenancy Support: Allows different users or applications to work independently.

• Namespace Management: Prevents naming conflicts between tables and other objects.

‍

‍

‍

‍

Benefits of Using Schemas in PostgreSQL

• Better Organization: Keeps related tables and objects grouped together.

• Avoid Naming Conflicts: Different schemas can have tables with the same name.

• Enhances Security:  We can set schema-level permissions to restrict access to sensitive data.

• Simplifies Management: We can maintain a cleaner and more organized database using schemas.

‍

Common Operations with Schemas

Some basic SQL commands for working with schemas:

Creating a Schema

CREATE SCHEMA schema_name;

Dropping a Schema

DROP SCHEMA schema_name;
DROP SCHEMA  schema_name CASCADE;   --- Drops the schema along with all objects inside it  

Listing All Schemas

\dn

Switching Between Schemas

The search path defines the order in which PostgreSQL looks for schemas when executing queries. By default, it includes the public schema unless modified. We can change the search path as needed.

To modify the search path

SET search_path TO schema_name;

Creating Tables in Different Schemas

CREATE TABLE sales.orders (  
    order_id SERIAL PRIMARY KEY,  
    order_date DATE NOT NULL  
);

Understanding the Public Schema

The public schema is the default schema in PostgreSQL. If we create a table or any object without specifying a schema, it is placed in the public schema.

Example:

CREATE TABLE users (  
    id SERIAL PRIMARY KEY,  
    name VARCHAR(50)  
);

SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'users';

 schemaname | tablename  
-------------------+---------------
 public             | users  

‍

Since no schema was specified, PostgreSQL placed the users table in the public schema.

To avoid clutter and improve security, it’s a best practice to create and use custom schemas.

Schema Permissions and Security Tips

Restrict Public Schema Usage

In PostgreSQL, the public schema is the default schema accessible to all users. Before PostgreSQL 15, any user could create objects (such as tables and views) in the public schema by default, which could lead to security and management issues, including:

Unorganized Growth: The public schema can become a collection of unrelated objects, making it difficult to manage.

Security Risks: Allowing any user to create objects in the public schema increases the risk of:

• Unauthorized users creating tables or other objects.
• Accidental object creation leading to a database mess up.
• Privilege escalation attacks, where an attacker gains access by creating malicious objects.

To mitigate these risks, it is a best practice to restrict the ability to create objects in the public schema. This can be done by revoking the CREATE permission from all users (PUBLIC).

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

‍

From PostgreSQL 15 onwards, this restriction is applied by default, However, for older versions, manual revocation is essential. For more details, refer to the PostgreSQL 15 documentation

Granting Access

GRANT USAGE ON SCHEMA schema_name TO user_name;

‍

This allows the user to access the schema but not modify objects inside it.

Revoking Access

REVOKE USAGE ON SCHEMA schema_name FROM user_name;

This revokes the user's access to the schema.

Grant Full Control to a Specific User

GRANT ALL PRIVILEGES ON SCHEMA schema_name TO user_name;‍

To grant full control (all privileges) to a specific user

Granting and Revoking Table-Level Access

GRANT SELECT ON schema_name.table_name TO user_name;

To provide a user with read access to a specific table within a schema.

REVOKE SELECT ON schema_name.table_name FROM user_name;

To revoke read access from the user.

Restricting Table Creation in a Schema for a Specific User

REVOKE CREATE ON SCHEMA schema_name FROM user_name;

To prevent a specific user from creating tables in a schema

Security Tips:

• Avoid giving unnecessary permissions to users.
• Use schemas to separate sensitive data from public data.
• Regularly review and update permissions.

Common Use Cases for PostgreSQL Schemas

• Multi-Tenant Applications: Use separate schemas to isolate data for different tenants while using a single database.

• Environment Separation: Maintain different schemas for development, testing, and production environments.

• Better Database Organization: Divide large databases into smaller, logical sections for easy management.

• Third-Party Integrations: Store external tool data in a separate schema to prevent conflicts with core application data.

Best Practices for Working with Schemas

• Avoid Overusing the Public Schema: Create custom schemas instead of relying on the default public schema.

• Avoid overusing schemas: Avoid excessive schema creation, as it can complicate database management.

• Document Your Schema Structure: Keep a record of what each schema contains for easier maintenance.

• Regularly clean up unused schemas: Remove unnecessary schemas to keep the database clean.
• Regularly Review Permissions: Periodically check and update schema permissions to ensure only authorized users have access.

• Backup Schemas Individually: Back up schemas individually to simplify restoration when needed

Conclusion

PostgreSQL schemas offer a structured and efficient approach to organizing database objects. They streamline database management for large applications, prevent naming conflicts, enhance security, and support multi-tenant architectures. By effectively using schemas, developers and administrators can ensure better database organization, security, and performance—making PostgreSQL a powerful choice for scalable applications.

Let Mydbops help you navigate the complexities of PostgreSQL. Contact Us for tailored PostgreSQL Services.

{{cta}}

‍