Re: create role/user management

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Steve Midgley <science(at)misuse(dot)org>
Cc: Kirti Adesara <kdadesara(at)hotmail(dot)com>, "pgsql-sql(at)lists(dot)postgresql(dot)org" <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: Re: create role/user management
Date: 2019-05-06 20:08:19
Message-ID: CAKFQuwZzDqi9Ci4HgxxBxZccuguskx_9e6+Xmq5y+VMOqf3O=A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

On Mon, May 6, 2019 at 12:52 PM Steve Midgley <science(at)misuse(dot)org> wrote:

> On Mon, May 6, 2019 at 12:44 PM David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Sat, May 4, 2019 at 9:38 AM Kirti Adesara <kdadesara(at)hotmail(dot)com>
>> wrote:
>>
>>> I have a database with multiple tables, functions and triggers in public
>>> schema.
>>>
>>
>> You should stop using the public schema.
>>
>> I want to create a user that can login to database and select on specific
>>> tables and cannot access to any functions, triggers and specific tables.
>>>
>>
>> Maybe provide an example what what is not presently working the way you
>> expect and describe how you would like it to work - after refraining from
>> using the public schema.
>>
>
> Thanks as always David for your contributions here. My question is mildly
> off-topic, but our engineering team was recently discussing this great wiki
> page (https://wiki.postgresql.org/wiki/Don%27t_Do_This)
>
> Can you explain why this user should stop using public schema? Is that a
> general principle or related to this user's situation? (If general, should
> we add it to the "Don't Do This" page?)
>

In general the default permissions around the PUBLIC "group" and public
schema mean that its difficult to know for certain whether you are dealing
with an exclude-everything based permission tree. Avoiding anything to do
with the "public" role/schema beyond initial learning of the system is
something I recommend generally.

Any system of non-trivial complexity should use schemas to describe
different categories of objects. "public" is a catch-all category that
should go unused as everything should be assigned to a well defined
category/schema.

David J.

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Leandro Carnio 2019-05-16 11:50:47 XML with nodes
Previous Message Steve Midgley 2019-05-06 19:51:50 Re: create role/user management