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

permissionspostgresqlpostgresql-9.4

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?

Best Answer

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.
Related Question