Enforcing users to write schemas when creating tables

[Gabriel Furstenheim Milerud <furstenheim@xxxxxxxxx>]

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