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