PostgreSQL Version 15+: Changes in Schema Permissions and Object Creation

PostgreSQL, a robust and feature-rich open-source relational database system, has continually evolved to enhance security and refine user permissions. One significant alteration introduced in version 15 and onwards involves the handling of schema permissions and object creation within the public schema.

Permission Changes and Implications

Previously, in PostgreSQL versions prior to 15, users could implicitly create objects within the public schema upon database creation. However, starting from version 15, attempting to create objects within the public schema without explicit permissions will result in a permission-denied error.

This shift aims to bolster security by enforcing stricter access controls, requiring users to be explicitly granted permissions to create objects within the public schema.

Granting Permissions to Create Objects

To enable a user to create objects like tables within the public schema, PostgreSQL administrators must explicitly grant the necessary permissions. The command GRANT ALL ON SCHEMA public TO <DB_user>; becomes essential in this context.

By executing this command as a privileged user (like the root or a superuser) after database creation and user setup, the specified user (<DB_user>) gains the required permissions to create objects within the public schema.

Implementation and Workaround

Consider the following scenario:

• Initial Attempt: A user (usertest) attempts to create a table (my_table) within the public schema but encounters a permission-denied error due to the lack of explicit permissions.

• Workaround: To resolve this, an administrator or a user with adequate privileges (e.g., root) grants all permissions on the public schema to usertest using the command below:

GRANT ALL ON SCHEMA public TO usertest;

• Successful Creation: After the necessary permissions are granted, the usertest user can successfully create the my_table table within the public schema without encountering permission-related errors.

Conclusion

PostgreSQL's version 15+ brings a significant change in handling permissions for object creation within the public schema. Administrators and users need to adapt to this shift by explicitly granting schema-level permissions to enable object creation, ensuring a more secure and controlled database environment.

Adhering to this change helps PostgreSQL users maintain a robust security posture and effectively manage database access and permissions.

Remember, while PostgreSQL evolves to enhance security and functionality, understanding and adapting to these changes are crucial for maintaining a secure and efficient database environment.