Enforcing users to write schemas when creating tables

From: Gabriel Furstenheim Milerud <furstenheim(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Enforcing users to write schemas when creating tables
Date: 2017-12-20 09:05:41
Message-ID: CAJN3DWqK31jQeyz_P_d2Ztnv_kPKhx8+21ARS75gfg6kwS7MdA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2017-12-20 10:01:27 Re: Enforcing users to write schemas when creating tables
Previous Message Kellner Thiemo 2017-12-20 07:02:40 RE: PostgreSQL suitable?