create role/user management

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

create role/user management

Kirti Adesara
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

Reply | Threaded
Open this post in threaded view
|

Re: create role/user management

Steve Midgley-3
On Sat, May 4, 2019 at 9:38 AM Kirti Adesara <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: create role/user management

David G Johnston
In reply to this post by Kirti Adesara
On Sat, May 4, 2019 at 9:38 AM Kirti Adesara <[hidden email]> 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.

lup
Reply | Threaded
Open this post in threaded view
|

Re: create role/user management

lup


On 5/6/19 1:43 PM, David G. Johnston wrote:
On Sat, May 4, 2019 at 9:38 AM Kirti Adesara <[hidden email]> 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"
Reply | Threaded
Open this post in threaded view
|

Re: create role/user management

Steve Midgley-3
In reply to this post by David G Johnston
On Mon, May 6, 2019 at 12:44 PM David G. Johnston <[hidden email]> wrote:
On Sat, May 4, 2019 at 9:38 AM Kirti Adesara <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: create role/user management

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