6

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?

2
  • 3
    You are not connected to test when revoking schema permissions do it does not apply to that db. Sep 23 '15 at 21:24
  • 1
    Also note the user/role executing the revoke also needs to be the owner of the schema (so you can check that as well). Refer to postgresql.org/message-id/… Apr 22 '16 at 2:04
6

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

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.

2
  • Welcome! Thanks for your contribution - however, I suggest taking a little look at the help pages dba.stackexchange.com/help to familiarise yourself with the site before posting answers. You may have noticed this question is pretty old, and has a contemporary up-voted answer already that's fairly comprehensive. I don't want to discourage you from posting answers at all (please do!) but take care to check that you're adding something new and useful - this is of particular necessity on older questions. Your time is much better spent on new or unanswered questions. Good luck and welcome!
    – Ian_H
    Dec 4 '18 at 10:08
  • 2
    I had a similar problem with PostgreSQL 10.5 recently and existing answer did not work for me neither on PostgreSQL 9.4 nor on newer version. That's why I decided to post my own answer. Dec 4 '18 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.