Why do all databases have a public schema in PostgreSQL?
PostgresqlPostgresql Problem Overview
I was just curious why all databases in PostgreSQL have a public schema that is accessible to all users. I know I can revoke privileges and grant them to one user but why is that not the default?
Postgresql Solutions
Solution 1 - Postgresql
There isn't much justification given, but see section [5.7.6][1] in the manual, but I think the following answers your question:
> If you do not create any schemas then all users access the public schema implicitly. This simulates the situation where schemas are not available at all. This setup is mainly recommended when there is only a single user or a few cooperating users in a database. This setup also allows smooth transition from the non-schema-aware world.
Solution 2 - Postgresql
My guess would be the SQL spec. But, this is fairly logical what would create user do otherwise, require you to explicitly say what schema the user had access too?
If your users don't have access to any single shared resource, why not just create a new DB for them? Unlike in MySQL, a new database is a new database, and not an alias for a new schema.
I just wanted to clarify this, it means that each database has its own public. schema.
Solution 3 - Postgresql
The concept of public schema appeared in version 7.3, when the concept of schema appeared in Postgresql. And it was necessary to support backward compatibility. Therefore, the public schema appears invisible everywhere. It is likely that the public scheme is no longer needed for anything else and it can be eliminated. https://severalnines.com/database-blog/postgresql-privileges-and-security-locking-down-public-schema