3 ms·

> Postgresql is following a blacklist access model which is really hard to get right, a whitelist approach like mysql would be much easier. I don't think this

> Postgresql is following a blacklist access model which is really hard to get right, a whitelist approach like mysql would be much easier. I don't think this is quite right. When a new user is created in Postgres, I don't believe any permissions are implicitly GRANTed by default aside from CONNECT. And I believe the only default privileges granted on a given database are to its owner. Is it possible your experience has only been with configurations in which your PG user was either the owner of the database or a superuser? EDIT: clarification

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 ago
  See 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 ago
  I 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...

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 ago
See 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 ago
I 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...