Closed frol closed 3 years ago
it is explained that even a new user with no permission granted has access to the public schema sufficient to create new tables
That's true. That's why it's better to use non-default schema for the project.
public
is not just regular schema, it has some exceptions as the default option.
Ideally, we need to invoke this command from admin account: REVOKE CREATE ON SCHEMA public FROM PUBLIC;
It's enough to invoke this command once, and all users except admin will have read-only access to the tables. With that, we will not need to edit our instruction.
My commit was tested it only in the sandbox. I need another account (with more permissions) to test it in real DB.
To sum up: We have new migration, we also need to invoke
REVOKE CREATE ON SCHEMA PUBLIC FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC FROM PUBLIC;
In my opinion, it should not be a part of migration
@frol I deployed these changes to mainnet and testnet
Unfortunately, the instructions we used (and provided in the PR) are not sufficiently strict, and allow creating new tables, inserting values there, creating new materialized views etc.
Thanks @telezhnaya for reporting it. @telezhnaya May I ask you to find the right set of incantations to achieve the true read-only mode? :smile:
I have seen a few places where it is explained that even a new user with no permission granted has access to the
public
schema sufficient to create new tables (and thus owning those), and they suggest revoking permissions topublic
, but I was not very successful. Some references:readonly_access
role)