3 ms·
My statement was not correct, you are right. But every user/role in postgres has by default the rights to create objects in the public schema and to connect to
by tpetry 6y ago
My statement was not correct, you are right. But every user/role in postgres has by default the rights to create objects in the public schema and to connect to every database [1]. The only way to remove this is to remove the rights on the public role affecting every other user. In this case the mysql concept is better as every new user has by default absolutely no rights.
[1] https://aws.amazon.com/de/blogs/database/managing-postgresql-users-and-roles/ https://aws.amazon.com/de/blogs/database/managing-postgresql...
- e12e 6y agoSee also: https://www.postgresql.org/docs/12/ddl-schemas.html#DDL-SCHEMAS-PATTERNS https://www.postgresql.org/docs/12/ddl-schemas.html#DDL-SCHE... In particular, note the justification for the default: > Keep the default. All users access the public schema implicitly. This simulates the situation where schemas are not available at all, giving a smooth transition from the non-schema-aware world. However, this is never a secure pattern. It is acceptable only when the database has a single user or a few mutually-trusting users. And: https://www.postgresqltutorial.com/postgresql-schema/ https://www.postgresqltutorial.com/postgresql-schema/
- anarazel 6y agoI agree that the whole PUBLIC thing is pretty confusing, and I doubt anybody would design it the way it is if we were start from scratch. Note that you can change the set of default privileges, including the grant to PUBLIC. E.g. ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM PUBLIC; > The only way to remove this is to remove the rights on the public role affecting every other user. In this case the mysql concept is better as every new user has by default absolutely no rights. This seems a bit contradictory...