Why can read only users create objects inside postgres database?

Question

I've just started working with Postgres and I noticed the following.

If I create a user they automatically have the ability to do whatever they want inside the database. User was created with:

CREATE ROLE read_access 
    NOLOGIN NOSUPERUSER INHERIT 
    NOCREATEDB NOCREATEROLE NOREPLICATION;

So I tried:

REVOKE ALL ON DATABASE dbname FROM USER username ;
GRANT CONNECT ON DATABASE dbname TO username ;

I expected the user should be able to connect to the db with no further privileges on the underlying objects. But this user is still able to create objects inside the database. What am I missing here?

started with REVOKE ALL ON DATABASE mytestdb2 FROM PUBLIC; REVOKE USAGE,CREATE ON SCHEMA PUBLIC FROM PUBLIC; followed by CREATE ROLE read_access NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; followed by CREATE ROLE app_read LOGIN ENCRYPTED PASSWORD 'abc123' IN ROLE read_access NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; followed by GRANT CONNECT ON DATABASE mytestdb2 TO read_access; followed by GRANT USAGE ON SCHEMA PUBLIC TO read_access; — KSpice, Jul 20 '16 at 18:31
After this I noticed the user app_read could create objects within the postgres database on the db server so I tried REVOKE ALL ON DATABASE postgres FROM app_read followed by GRANT CONNECT ON DATABASE postgres TO app_read. And this is where I'm finding this strange issue with the user still being able to create objects inside postgres database. — KSpice, Jul 20 '16 at 18:35
Read this documentation page: Schemas, where it states: "by default, everyone has CREATE and USAGE privileges on the schema public." — ypercubeᵀᴹ, Jul 20 '16 at 18:41
Read this answer: http://stackoverflow.com/questions/20459504/postgresql-create-a-new-user-with-no-privileges — ypercubeᵀᴹ, Jul 20 '16 at 18:50
Would it be best practice to drop public schema, create application schema and explicitly grant controlled access to application schema? It seems like a security risk to grant access to public schema by default for all new users. — KSpice, Jul 20 '16 at 18:57
Yes, you could drop the public schema. Or use the ALTER DEFAULT PRIVILEGES, as suggested in the linked answer. — ypercubeᵀᴹ, Jul 20 '16 at 18:59
Or for detailed instructions with code: How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA? — Erwin Brandstetter, Jul 21 '16 at 00:34

Why can read only users create objects inside postgres database?

0 Answers0