Hacker News Reader

Hacker News

Comment by pyrophane | Hacker News Reader

Parent Full thread

pyrophane

Have used both extensively. I generally prefer MySQL because I find it easier to manage for a few reasons:

1. MySQL has a simpler permission model, so user management is less of a headache.

2. Connections are cheaper in MySQL so you don't have to use an external connection pooler like pgbouncer.

3. There's more/better documentation on MySQL performance tuning, especially from Percona.

That's really it for me.

View on HN

Permissions management in Postgres is indeed a pain.

I agree, but can you enumerate a bit more what specifically is painful?

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.

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

A significant annoyance is that permissions cannot be encapsulated within a database but are global to the instance, requiring extra consideration at every step, from the very logic of your migrations, to how you dump/restore.

To the point number two: even though I love to use PostgreSQL, when I hit the point where I need pgBouncer and its transaction mode, things get quite ugly especially if you've been using a lot of prepared statements. Now you need to clean the statements from the connection after every use or get conflict errors if another connection using the same connection from the bouncer wants to declare a statement with the same name.

Or you just use the text protocol and escape your parameters in the app...

Reply on news.ycombinator.com