how do you revoke create table from a user on postgresql 9.4?

Question

I'm trying to create a postgresql user that can select but can't create or drop, insert and so on on a database but I cannot for the life of me figure out how to prevent a user from creating its own tables.

What I've done so far:

create database test;
create role readonly;
alter role readonly with login;
alter role readonly with encrypted password 'test';
revoke all on schema public from public;
revoke all on schema public from readonly;
revoke all on database test from public;
revoke all on database test from readonly;
grant connect on database test to readonly;

Yet when I log in to the database as readonly I'm still able to create tables with impunity. What am I missing?

Answer 1

According to documentation you need to execute following query:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

It revokes CREATE privilege on schema public from all users(except owner and superuser), so you have to grant it explicitly to users who need this privilege.

Answer 2

According to the documentation, what one needs for creating tables in a schema is CREATE on that schema. This you think you revoked, but as you experience it didn't really happen - the only plausible explanation is that from Daniel's comment: you are revoking access on the schemas of another database.

When you clear this up, you'll still need to grant/revoke the following:

• GRANT USAGE ON SCHEMA public TO readonly; to be able to access the objects in there
• REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public, readonly;
• GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
• and, if you plan to add new objects (tables) later, you have to set the default privileges, too.