Removing the public Schema from search_path

Removing the public Schema from search_path

In PostgreSQL, the search_path is a key feature that defines which schemas the database should search for objects like tables and functions. By default, it includes the $user and public schemas, meaning if no schema is specified, PostgreSQL first looks in the user-specific schema and then in the public schema. However, this default setup might not always be the most secure option. In environments with multiple users, the public schema can lead to uncontrolled data access. In this article, we’ll explore how to disable the public schema by modifying the search_path and discuss the impact this change can have on database security.

Understanding search_path and Its Importance

The search_path controls where PostgreSQL searches for objects when a schema is not explicitly provided. The default includes the $user and public schemas. This can lead to security risks if not properly managed, especially in environments where users should have restricted access to certain schemas. In some cases, you may want to remove the public schema from the search_path to tighten security. Let’s walk through an example to understand how this works.

Example: Working with search_path

First, let’s create an employee table in the default public schema and insert some data:

CREATE TABLE employee(name varchar(15), surname varchar(15), number int);


INSERT INTO employee (name, surname, number) 
VALUES ('Kemal', 'Oz', 12), ('Ali', 'Oz', 13);

Now, querying this table will return the expected results:

SELECT * FROM employee;

 name  | surname | number
-------+---------+--------
 kemal | oz      |     12
 ali   | oz      |     13
(2 rows)

But if we change the search_path to exclude the public schema, the table becomes inaccessible:

SET search_path TO "$user", used_schema;

Result:

SELECT * FROM employee;

ERROR:  relation "employee" does not exist

This happens because the employee table is in the public schema, which is no longer part of the search_path. Removing the public schema from the search_path can help restrict user access to specific schemas, enhancing data security.

Creating and Using a Custom Schema

Next, let’s create a new schema called used_schema and create an employee table within it:

CREATE SCHEMA used_schema;
CREATE TABLE used_schema.employee(name varchar(15), surname varchar(15), number int);

Now that our search_path is set to include used_schema, we can insert and query data from the new employee table:

INSERT INTO used_schema.employee (name, surname, number) VALUES ('Ayse', 'Yilmaz', 14);

SELECT * FROM used_schema.employee;

name  | surname | number
-------+---------+--------
 Ayse | Yilmaz  |   14
(1 rows)

Setting search_path Permanently

Instead of manually adjusting the search_path in each session, it’s more practical to set it permanently. This ensures consistency and enhances security.

At the Database Level:

ALTER DATABASE mydatabase SET search_path TO "$user", used_schema;

At the User Level:

ALTER ROLE myuser SET search_path TO "$user", used_schema;

Why Permanent search_path Configuration Matters

Permanently configuring the search_path is crucial, especially in systems with multiple users. Manually setting the search_path in each session is error-prone and time-consuming. Moreover, excluding the public schema by default helps prevent unauthorized access, contributing to a more secure database environment.

Conclusion

This article demonstrates the importance of configuring the search_path in PostgreSQL to enhance security. By removing the public schema from the search_path, you can limit user access to specific schemas, protecting your data and maintaining database integrity. If your setup requires restricted schema access, configuring the search_path permanently is a best practice to follow. For more detailed and technical articles like this, keep following our blog on Medium. If you have any questions or need further assistance, feel free to reach out in the comments below and directly.