Обсуждение: Question About Roles

    My highest priority has just changed to developing a multiuser database
application. The backend is postgres, of course, and everything else will be
written in Python, SQLAlchemy, and wxPython. This application is at least
one order of magnitude more complicated/sophisticated than any I have
developed in the past. Ergo, there will be many questions, despite my
reading the docs.

    My first questions regard roles (postgres-9.3.4 is what's currently
installed here). I've read Chapter 20 (Database Roles) and completely
understand what it says; but I'm missing how to apply it to my developing
application. Here's the context:

    There are three groups of users (now all lumped together in roles), plus
the local SysAdmin whose role is to add and remove users and be the local
maintainer of the application and the database. For one client, this is the
head IT at the mine site.

    The other roles are 'executive,' 'manager,' and 'technician.' Executives
are allowed to generate and view reports, retrieve and view stored documents
(such as permits), but not enter or modify data.

    Managers have authority to enter, modify, or delete data, create reports,
and do other tasks required by their responsibilities.

    Technicians are allowed to only enter data (and correct user errors within
a short time window). After that, corrections must be made by a manager.

    In the .sql file I'm creating, following the CREATE DATABASE command,  I
have these commands:

-- Set user roles

CREATE ROLE admin LOGIN;
CREATE ROLE executive;
CREATE ROLE manager;
CREATE ROLE technician;

and my question is what do I need to do so when a user is added to the
system the local administrator and the group specified during the createuser
process, she or he can be granted that group's privileges? Or, is that
automatic if the role is included in the Users table?

Rich

On 07/02/2014 06:17 AM, Rich Shepard wrote:

> -- Set user roles
>
> CREATE ROLE admin LOGIN;
> CREATE ROLE executive;
> CREATE ROLE manager;
> CREATE ROLE technician;
>
> and my question is what do I need to do so when a user is added to the
> system the local administrator and the group specified during the
> createuser
> process, she or he can be granted that group's privileges?

I am not following.

Can you show an example of what you are talking about?

Or, is that
> automatic if the role is included in the Users table?

Assuming Users is your own table, Postgres has no way of knowing that
relates to user permissions. It stores that information in the system
catalogs.


>
> Rich
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Rich Shepard <rshepard@appl-ecosys.com> writes:
> ... my question is what do I need to do so when a user is added to the
> system the local administrator and the group specified during the createuser
> process, she or he can be granted that group's privileges?

I might be misunderstanding, but I think you're looking for GRANT/REVOKE:

  GRANT rolename TO newuser;

            regards, tom lane

On Wed, 2 Jul 2014, Tom Lane wrote:

> I might be misunderstanding, but I think you're looking for GRANT/REVOKE:
>  GRANT rolename TO newuser;

tom,

   You correctly understand my question. That's the syntax I need and did not
see.

Thank you,

Rich

Tom Lane-2 wrote
> Rich Shepard <

> rshepard@

> > writes:
>> ... my question is what do I need to do so when a user is added to the
>> system the local administrator and the group specified during the
>> createuser
>> process, she or he can be granted that group's privileges?
>
> I might be misunderstanding, but I think you're looking for GRANT/REVOKE:
>
>   GRANT rolename TO newuser;

or if you want to do it as part of creating a new user:

CREATE ROLE new_management_user
[other stuff here]
IN ROLE management;

http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Question-About-Roles-tp5810176p5810189.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 07/02/2014 06:17 AM, Rich Shepard wrote:

  Or, is that
> automatic if the role is included in the Users table?

To follow up on my previous post. If you have your own user table,
having a user in that table with the same name as one of the Postgres
role does not mean they pick up the Postgres role permissions. The
permissions apply to the role that the user connects as in the
connection parameters(or is changed to once connected). This is why
something like Django has it owns permissions system. The framework
connects to the database as a single role(which has sufficient Postgres
permissions) and when users log in they are tracked by the Django
permissions not by the Postgres system.

>
> Rich
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 2 Jul 2014, David G Johnston wrote:

> or if you want to do it as part of creating a new user:
>
> CREATE ROLE new_management_user
> [other stuff here]
> IN ROLE management;
>
> http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html

David,

   I'll have to think deeply about what this is doing. Initially, I read it
as assigning a new user's role to an existing group's role; that's the
opposite of what I want.

Thanks,

Rich

On Wed, 2 Jul 2014, Adrian Klaver wrote:

> To follow up on my previous post. If you have your own user table, having a
> user in that table with the same name as one of the Postgres role does not
> mean they pick up the Postgres role permissions.

Adrian,

   OK. Now I see the issue.

   What I have created as roles are what would be considered group roles in
versions < 8.1. Usernames in the Users table are individuals; e.g., Fred
Flintstone, George Gamov, Issac Azimov, etc. What I want to do, when the
user is being added to the system, is specify the group to which this new
user should be assigned so that the group's permissions are granted to him.

> The permissions apply to the role that the user connects as in the
> connection parameters(or is changed to once connected). This is why
> something like Django has it owns permissions system. The framework
> connects to the database as a single role(which has sufficient Postgres
> permissions) and when users log in they are tracked by the Django
> permissions not by the Postgres system.

   I considered making this a Web-based application using django, but I've no
experience with this approach, the django learning curve is rather steep,
and it would add the cost and time of setting up and maintaining an in-house
(or colo) httpd server. That's not my expertise, interest, or business. So,
a stand-alone application is the route I've chosen.

Thanks,

Rich

Rich Shepard wrote
> On Wed, 2 Jul 2014, David G Johnston wrote:
>
>> or if you want to do it as part of creating a new user:
>>
>> CREATE ROLE new_management_user
>> [other stuff here]
>> IN ROLE management;
>>
>> http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html
>
> David,
>
>    I'll have to think deeply about what this is doing. Initially, I read
> it
> as assigning a new user's role to an existing group's role; that's the
> opposite of what I want.

Its the exact same outcome Tom provided, just via a different mechanism...

You want to assign all permissions to the standard group-roles and then have
new users inherit the appropriate permissions via their membership in the
appropriate group-role.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Question-About-Roles-tp5810176p5810198.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 07/02/2014 07:50 AM, Rich Shepard wrote:
> On Wed, 2 Jul 2014, David G Johnston wrote:
>
>> or if you want to do it as part of creating a new user:
>>
>> CREATE ROLE new_management_user
>> [other stuff here]
>> IN ROLE management;
>>
>> http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html
>
> David,
>
>    I'll have to think deeply about what this is doing. Initially, I read it
> as assigning a new user's role to an existing group's role; that's the
> opposite of what I want.

If you want opposite then:

http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html

ROLE role_name

     The ROLE clause lists one or more existing roles which are
automatically added as members of the new role. (This in effect makes
the new role a "group".)


>
> Thanks,
>
> Rich
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 2 Jul 2014, David G Johnston wrote:

> Its the exact same outcome Tom provided, just via a different mechanism...
> You want to assign all permissions to the standard group-roles and then
> have new users inherit the appropriate permissions via their membership in
> the appropriate group-role.

David,

   Ah, so! I'll read the docs to better understand this approach.

Thanks for clarifying,

Rich

On Wed, 2 Jul 2014, Gregory Haase wrote:

> If it made you feel better, remember that CREATE USER is an alias for
> CREATE ROLE that includes LOGIN by default.

Greg,

   Yes, I read that in the docs.

> So if you simply swap one word in your command, the context becomes a
> little more clear for what you want:
>
> CREATE USER new_management_user
> [other stuff here]
> IN ROLE management;

   Got it.

Thanks,

Rich