Hacker News Reader

Comment by tpetry | Hacker News Reader

Try the lock down e.g. a user to only allow reads. Not easy, when testing if it‘s working you will see so many „strange“ behaviours e.g. you can still create tables in public namespace, databases created before/after (dont remember which one) your user are accessible etc...

Postgresql is following a blacklist access model which is really hard to get right, a whitelist approach like mysql would be much easier.

View on HN

> 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

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...

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/

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...

Reply on news.ycombinator.com