Why is a new user able to create tables in PostgreSQL?

Question

I followed two tutorials to create a DB with:

1. a fully privileged user link
2. a read-only user link

I then got a tip from CJ Estel's tutorial stating that "you may have inherited the ability to create tables even though we never explicitly gave it to our new user". Sure enough, the read-only user is able to create and own tables!

CJ Estel has pointed the root cause very well, namely a template database. But the ability to create tables undermines most tutorials you get from googling "read only user postgres" including one hosted on postgresql.org. Your user has more than read-only privileges!

Why does a new user have this ability? After revoking this privilege, is the database truly read-only for that user?

Answer 1

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.