Re: 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 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.

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