The Public Schema in PostgreSQL < 15 : An Underestimated Security Risk

The Public Schema in PostgreSQL: Security Risks and Changes in Version 15

If you use PostgreSQL, you probably think your database is secure and reliable. But what if I told you that PostgreSQL comes with a default security risk that could jeopardize your most sensitive data? Welcome to the Public Schema, the often-overlooked Achilles' heel of many PostgreSQL databases. Let’s explore how this issue impacts versions <15 and how it changed with version 15 and later.

What Is the Public Schema?

The Public Schema is a pre-installed schema that exists in every new PostgreSQL database. Its purpose is to provide a default location for objects like tables and functions. However, its behavior differs significantly depending on your PostgreSQL version.

In Versions <15

• All users in the database automatically have access to it.
• Any user created in the database can create objects in the Public Schema.
• These objects are visible and accessible to other users by default unless you take steps to restrict access.

These default permissions can lead to significant security vulnerabilities, especially in multi-user environments.

In Versions >=15

• Default CREATE permissions are revoked: Users no longer have the ability to create objects in the Public Schema unless explicitly granted.
• The Public Schema is now owned by the database owner, improving control and security.
• Usage and access to the Public Schema must be explicitly defined.

These changes address many of the vulnerabilities associated with the Public Schema in earlier versions.

A Realistic Scenario: Data Leaks via the Public Schema

Imagine the following scenario in PostgreSQL versions <15:

1. You create a new user dev_user in your PostgreSQL database.
2. Without your knowledge, dev_user creates a table in the Public Schema:

   CREATE TABLE public.sensitive_data (id SERIAL, secret TEXT);

3. Another user in the database, such as analyst_user, automatically has access to this table:

   SELECT * FROM public.sensitive_data; -- Success!

In versions >=15, this scenario is no longer possible by default because the CREATE permissions are revoked for all users in the Public Schema.

How to Secure the Public Schema

The good news? You can secure the Public Schema in any PostgreSQL version with a few targeted steps:

For Versions <15

1. Revoke default access to the Public Schema:

   REVOKE ALL ON SCHEMA public FROM PUBLIC;
   REVOKE USAGE ON SCHEMA public FROM PUBLIC;

2. Grant access only to specific users:

   GRANT USAGE, CREATE ON SCHEMA public TO admin_user;

3. Create dedicated schemas:

   CREATE SCHEMA app_user_schema;
   GRANT USAGE ON SCHEMA app_user_schema TO app_user;
   GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA app_user_schema TO app_user;

For Versions >=15

1. Review and adjust schema permissions: Ensure only necessary users have access to the Public Schema.

   GRANT USAGE ON SCHEMA public TO trusted_user;

2. Use the database owner role: Manage permissions through the `pg_database_owner` role to maintain control over the Public Schema.
3. Regularly review permissions:

   SELECT grantee, privilege_type 
   FROM information_schema.role_schema_grants 
   WHERE schema_name = 'public';

Be Cautious During Migrations

When migrating databases, be aware that the default behavior of the Public Schema can be carried over depending on the migration method. For example:

• Dump-and-restore methods may replicate old permissions unless explicitly modified.
• Replication tools can preserve Public Schema configurations, unintentionally introducing outdated security risks into the new environment.

Always review and adjust schema permissions as part of your migration process to ensure the new setup complies with your security standards.

Conclusion: The Public Schema – Convenience or Catastrophe?

The Public Schema has been a longstanding security concern in PostgreSQL, especially in versions <15. With the changes introduced in version 15, many of these issues have been mitigated, but it remains essential to review and configure permissions to suit your specific needs.

By understanding the differences between PostgreSQL versions and applying best practices, you can ensure that your database is both secure and efficient.

Secure your data – whether you’re using PostgreSQL <15 or >=15!