Hi,
I'm trying to enforce db users to write a schema when creating a table. That is:
create table some_schema.my_table (a int); -- should succeed
create my_table (a int); -- should fail
I don't know if that is possible.
What I've tried so far is to create a schema which is first in the search path and where the user has no permission to create tables. I've done the following (and failed):
create schema no_table_schema;
alter schema no_table_schema owner to another_user; -- just in case
revoke all on schema no_table_schema from my_user cascade;
set search_path = no_table_schema;
create table test_table (a int); -- this should fail because user should not have permission in no_table_schema, but it does not
drop table no_table_schema.test_table; -- This succeeds, the table was created
One thing that might affect is that my_user is a superuser.
So I have two questions, first is how do I revoke create on a schema for a certain user. I guess there is something that I'm not doing properly. Then, is that enough my purpose? Or maybe there are easier ways to force users to provide schema when creating.
Thanks
Gabriel Fürstenheim