Re: create role/user management

On Mon, May 6, 2019 at 12:52 PM Steve Midgley <[email protected]> wrote:

> On Mon, May 6, 2019 at 12:44 PM David G. Johnston <
> [email protected]> wrote:
>
>> On Sat, May 4, 2019 at 9:38 AM Kirti Adesara <[email protected]>
>> 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.