TL;DR: New users can create tables in the public
schema because people complained that it was too hard when they couldn't.
If you dislike the defaults, you should probably create a new template database with the initial configuration that you want. For example, you might:
DROP SCHEMA public;
or
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO public;
in your template.
If you wish the public
user to have no rights on a database, you should additionally:
REVOKE ALL ON DATABASE mydbname FROM public;
GRANT CONNECT ON DATABASE mydbname TO public;
so that the public
user cannot create schemas or use temp tables.
Personally, if I was designing this, I'd give users the TEMP
right on the database by default, but not CREATE
(schemas in database) or CREATE
(tables in the public
schema). I'd reserve those for the owner.
They're choices that were made a long time ago, though, and it's pretty hard to change them now.
As it is, there are regular complaints that it's too hard to get started with PostgreSQL because you have to create a user account and often want to create a database too. Why don't we just auto-create them and default to 'trust' as the auth-mode to make it easy? Why doesn't the postgres
user default to having the password postgres
? Why don't we just auto-create users if they exist in the OS? etc.
There are some genuine usability problems for new users - in particular, most people have no idea what peer
auth is, or why just running psql
after installing PostgreSQL tells them there's no user by the name they're logged in as.
It's also messy that pg_hba.conf
is a config file, but users are created at the SQL level. This split confuses users.
Lots of things, though, are compromises between secure defaults and easy defaults where the project isn't ever going to make everybody happy.