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
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? |