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
