Обсуждение: Enforcing users to write schemas when creating tables

Gabriel Furstenheim Milerud wrote:
> 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
tocreate 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,
butit 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
thatI'm not doing properly.
 
> Then, is that enough my purpose? Or maybe there are easier ways to force users to provide schema when creating.

There is no way to deny a superuser access to a schema.
Don't use superusers for anything else than administration.

One way I can think of to force users to create tables with
schema qualified names is to set "search_path" to "pg_catalog".
Then only the temporary schema and the catalog schema can be
used without qualification.

Every user can use "SET search_path = ..." to change the setting,
but a script that does that documents at least where the table
*might* be created.

Yours,
Laurenz Albe