agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedcreate role/user management
6+ messages / 4 participants
[nested] [flat]
* create role/user management
@ 2019-05-04 16:38 Kirti Adesara <kdadesara@hotmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Kirti Adesara @ 2019-05-04 16:38 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Hi
I have a database with multiple tables, functions and triggers in 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.
Thanks,
Kirti
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: create role/user management
@ 2019-05-06 19:35 Steve Midgley <science@misuse.org>
parent: Kirti Adesara <kdadesara@hotmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Steve Midgley @ 2019-05-06 19:35 UTC (permalink / raw)
To: Kirti Adesara <kdadesara@hotmail.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Sat, May 4, 2019 at 9:38 AM Kirti Adesara <kdadesara@hotmail.com> wrote:
> Hi
>
> I have a database with multiple tables, functions and triggers in 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.
>
Can you clarify your intention/goal? You write that you want a user role to
"select on specific tables" but "cannot access... specific tables." Do you
mean you want by default a user role that can't access any functions,
triggers or tables, and then can be "whitelisted" into access to a limited,
defined list of tables?
Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: create role/user management
@ 2019-05-06 19:43 David G. Johnston <david.g.johnston@gmail.com>
parent: Kirti Adesara <kdadesara@hotmail.com>
1 sibling, 2 replies; 6+ messages in thread
From: David G. Johnston @ 2019-05-06 19:43 UTC (permalink / raw)
To: Kirti Adesara <kdadesara@hotmail.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Sat, May 4, 2019 at 9:38 AM Kirti Adesara <kdadesara@hotmail.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.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: create role/user management
@ 2019-05-06 19:49 Rob Sargent <robjsargent@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rob Sargent @ 2019-05-06 19:49 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
On 5/6/19 1:43 PM, David G. Johnston wrote:
> On Sat, May 4, 2019 at 9:38 AM Kirti Adesara <kdadesara@hotmail.com
> <mailto:kdadesara@hotmail.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.
>
> David J.
>
And it's really easy to move a table to a different schema with "alter
table"
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: create role/user management
@ 2019-05-06 19:51 Steve Midgley <science@misuse.org>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Steve Midgley @ 2019-05-06 19:51 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Kirti Adesara <kdadesara@hotmail.com>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Mon, May 6, 2019 at 12:44 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Sat, May 4, 2019 at 9:38 AM Kirti Adesara <kdadesara@hotmail.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?)
Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: create role/user management
@ 2019-05-06 20:08 David G. Johnston <david.g.johnston@gmail.com>
parent: Steve Midgley <science@misuse.org>
0 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2019-05-06 20:08 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: Kirti Adesara <kdadesara@hotmail.com>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Mon, May 6, 2019 at 12:52 PM Steve Midgley <science@misuse.org> wrote:
> On Mon, May 6, 2019 at 12:44 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Sat, May 4, 2019 at 9:38 AM Kirti Adesara <kdadesara@hotmail.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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-05-06 20:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-05-04 16:38 create role/user management Kirti Adesara <kdadesara@hotmail.com>
2019-05-06 19:35 ` Steve Midgley <science@misuse.org>
2019-05-06 19:43 ` David G. Johnston <david.g.johnston@gmail.com>
2019-05-06 19:49 ` Rob Sargent <robjsargent@gmail.com>
2019-05-06 19:51 ` Steve Midgley <science@misuse.org>
2019-05-06 20:08 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox