PostgreSQL 15: default permissions for everyone is now restricted to db owner

https://www.depesz.com/2021/09/10/waiting-for-postgresql-15-revoke-public-create-from-public-schema-now-owned-by-pg_database_owner/

by mattashii • 5 years ago

120 18 5 years ago

depesz.com

terom• 5 years ago

This is a good change. The default CREATE privileges on the `public` schema are very surprising.

megous2• 5 years ago

What's a bit more annoying is the superuser ownership of the public schema in the newly created DB.

Nothing that can't be fixed by removing the public schema from the template DB, though...

chromatin3• 5 years ago

For a typical simple monolithic web application which is likely to be the only client accessing the database, is there any value in using a dedicated schema that is not the default public?

spapas82• 5 years ago

Wow I didn't know that nor I even expected it. Can you recommend me a way to achive this behavior (i.e not allow users without permissions to create tables in the public schema) in Postgresql 13?

mattashii• 5 years ago

you can change the permissions for the default schema after databas ecreation, or change the permissions of the default schema in the template database that is used when you create a database

artembugara• 5 years ago

It reminds me how Elasticsearch changed their default index shards number form 15 to 5.

Overall, I feel like decent software that is used by millions of devs can afford being "a bit hard" in the quick start/first interaction stages.

Look at how many articles there're about intro to something, and how many about real/production cases. People leave default configs, so I find it wise to make those restricted.

Can't even imagine how many products are running with software on the default settings that are not supposed to be used in real life setups

chousuke• 5 years ago

I feel the same way. Default setting should be secure, unsurprising and suitable for at least small-time production, and good software further guides the user to use it such that they aren't likely to shoot themselves in the foot.

Unfortunately, it seems quite often software defaults get optimized for developer convenience and may do insecure things like installing development-time dependencies by default...

When someone goes and uses that software in production, they are very likely to end up using the bad defaults. That is not the user's fault.

If you can't provide at least secure defaults, the software should not run at all until the user has made the necessary choices.

PostgreSQL 15: default permissions for everyone is now restricted to db owner

Related Stories