Revoked All Privileges On Database But Still The User Can Create Table

Question

I am new at Postgresql that's why I can't figure out some privileges here.

Database called Denemedb23 is already created. I created the user NoxUser.

I executed these commands:

1. REVOKE ALL PRIVILEGES ON DATABASE Denemedb23 FROM public;
2. GRANT CONNECT ON DATABASE Denemedb23 TO NoxUser;

I want Noxuser just to be able to login to the database and he should not be able to create table/function etc. But NoxUser can create a table in the database.

Could you help me to figure out why?

Answer 1

The CONNECT permission in PostgreSQL enables a user to establish a connection to a database, but it does not provide the user the power to create tables or carry out other tasks.

In your instance, you have given NoxUser the CONNECT privilege and removed all database rights from the public role. However, the CREATE privilege on the schema is required in order to create tables.

So, make sure the user does not have the CREATE privilege on the schema in order to accomplish your aim of enabling NoxUser to connect to the database but prevent them from creating tables.

Run these commands, these guarantee that although NoxUser can connect to the Denemedb23 database but  cannot create tables in the public schema.

REVOKE CREATE ON SCHEMA public FROM public;

GRANT CONNECT ON DATABASE Denemedb23 TO NoxUser;

Hope it's helpful :)

Answer 2

Privileges on the database are one thing. Privileges on things in it is another. This:

REVOKE ALL PRIVILEGES ON DATABASE Denemedb23 FROM public;

Effectively means this:

REVOKE CREATE ON DATABASE Denemedb23 FROM public;
REVOKE CONNECT ON DATABASE Denemedb23 FROM public;
REVOKE TEMPORARY ON DATABASE Denemedb23 FROM public;

Meanings of these you can see below and in the doc. I'm guessing you could've expected CREATE to mean something else.

CREATE For databases, allows new schemas and publications to be created within the database, and allows trusted extensions to be installed within the database.

CONNECT Allows the grantee to connect to the database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf).

TEMPORARY Allows temporary tables to be created while using the database.

If you wanted to lock them out, you need to revoke their privileges on schema/object level, for example:

revoke all on all tables in schema public from NoxUser;
revoke all on all routines in schema public from NoxUser;
revoke all on all sequences in schema public from NoxUser;
revoke all on schema public from NoxUser;

On schema level, create means something else, as outlined in the doc linked earlier:

CREATE For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.

The last command will revoke that on public, but you might want to inspect other schemas as well. You can also create a schema named NoxUser, and by default their non-schema-qualified commands will target that schema thanks to search_path, before taking a look at public. Refer to schema usage patterns for more.

If you only wish to stop them from creating in public while retaining all access beside that, use only that one revoke.

revoke create on schema public from NoxUser;