4

I created a read-only user in PostgreSQL and it is still able to create tables:

I created a test DB and then created a readonly2 user. Gave it only select privileges on 2 tables. When I log into this DB as readonly2 user I am still able to create tables:

create database test1
create user readonly2 with password 'readonly';
grant select on test1 to readonly2
grant select on test2 to readonly2

where test1 and test2 are 2 tables in test DB.

Now when I log into the test DB as readonly2 user, I am able to create tables:

test=> create table test55 (id int);
CREATE TABLE

I just want to create a read-only user with select permissions. I do not want to grant create table permissions.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
WinSupp
  • 361
  • 2
  • 6
  • 20
  • http://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql – Mihai Jun 20 '14 at 17:35

1 Answers1

8

Every table is created in a schema in Postgres. To create a table, a role must have the CREATE privilege for the schema. Per documentation:

CREATE

... For schemas, allows new objects to be created within the schema.

The default schema for a table to be created in is the first schema of the current search_path.

The first schema in the search_path is typically the schema with the same name as the user or the schema public.

And the public schema comes with default privileges:

A user can also be allowed to create objects in someone else's schema. To allow that, the CREATE privilege on the schema needs to be granted. Note that by default, everyone has CREATE and USAGE privileges on the schema public.

Bold emphasis mine.

You can change that:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Be sure to think about consequences first ...

(Either that, or the role is a superuser.)

Volodymyr Vintonyak
  • 105
  • 1
  • 5
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 2
    I did the same.. Revoked create privileges from this user `revoke create on schema public from readonly2` restarted DB engine and even then i was able to create table in the test DB – WinSupp Jun 20 '14 at 21:31
  • 4
    @user3669651: But it's *not* the same. Revoking privileges from `readonly2` doesn't change the fact that *every* user can create tables in the `public` schema until you revoke from role `public` ... – Erwin Brandstetter Jun 20 '14 at 23:32