Re: [GENERAL] Proper use of Groups and Users (Roles).

[Vincent Veyron]

On Tue, 16 Feb 2016 09:14:30 -0500
Melvin Davidson  wrote:

> The problem is TRUNCATE is more of an administrative privilege. Also, it is
> not captured in a DELETE trigger, so you have a security issue with that.

Ha, well, learn something new every day


> Also, REFERENCES & TRIGGER are schema changes which should never be done by
> a normal user.

Sure. I was thinking of the data changes.

Thanks.


-- 
Bien à vous, Vincent Veyron

https://marica.fr/
Gestion des contentieux, des dossiers de sinistres assurance et des contrats 
pour le service juridique


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Proper use of Groups and Users (Roles).

[Melvin Davidson]

The problem is TRUNCATE is more of an administrative privilege. Also, it is
not captured in a DELETE trigger, so you have a security issue with that.
Also, REFERENCES & TRIGGER are schema changes which should never be done by
a normal user.

On Tue, Feb 16, 2016 at 5:39 AM, Vincent Veyron  wrote:

> On Mon, 15 Feb 2016 12:06:28 -0500
> Melvin Davidson  wrote:
>
> > I wrote a short article to explain the proper use of Group and Userss in
> the database.
>
> Hi Melvin,
>
> Thanks for the explanation, it makes things easy to understand.
>
> One question :
>
> > Although GRANT ALL, at first appears to simplify granting permissions,
> it is actually a very bad practice that is often misused. That is because
> doing so would also allow groups and ordinary users the following
> additional privileges: TRUNCATE, REFERENCES & TRIGGER.
>
> If a user has DELETE rights on a table, I don't see how granting him
> TRUNCATE makes that much of a difference? Same could be said of the other
> two, it's not like they are going to cause more damage than the previous
> rights.
>
>
>
>
> --
> Bien à vous, Vincent Veyron
>
> https://marica.fr/
> Gestion des contentieux, des dossiers de sinistres assurance et des
> contrats pour le service juridique
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Proper use of Groups and Users (Roles).

[Vincent Veyron]

On Mon, 15 Feb 2016 12:06:28 -0500
Melvin Davidson  wrote:

> I wrote a short article to explain the proper use of Group and Userss in the 
> database. 

Hi Melvin,

Thanks for the explanation, it makes things easy to understand.

One question :

> Although GRANT ALL, at first appears to simplify granting permissions, it is 
> actually a very bad practice that is often misused. That is because doing so 
> would also allow groups and ordinary users the following additional 
> privileges: TRUNCATE, REFERENCES & TRIGGER. 

If a user has DELETE rights on a table, I don't see how granting him TRUNCATE 
makes that much of a difference? Same could be said of the other two, it's not 
like they are going to cause more damage than the previous rights.




-- 
Bien à vous, Vincent Veyron

https://marica.fr/
Gestion des contentieux, des dossiers de sinistres assurance et des contrats 
pour le service juridique


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Proper use of Groups and Users (Roles).

[Melvin Davidson]

Some years ago, while working at Computer Associates as a tech support
specialist for the Ingres database, I wrote a short article to explain the
proper use of Group and Userss in the database. I thought it would be
worthwhile to do the same for PostgreSQL, as I've seen a lot of cases where
this was not implemented properly. Since I am not found of Wiki's, I've
attached it here for sharing.


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
Clarifying the use of Groups and Roles,

First, a little backgrond history. Prior to PostgreSQL v8.1, the division 
between a Group and a Role was much clearer. However, because “There is no 
CREATE GROUP statement in the SQL”, with 8.1, CREATE GROUP became an alias 
for CREATE ROLE, with the option to allow (or prevent) login, and CREATE USER 
was changed to CREATE ROLE.

Confused yet? Don’t worry, hopefully I’m going to clear up the distinction.

In the practical world of database use, a GROUP is a collection of USERS  
(ROLEs that can login. 
GROUPs normally are not given the option to login.

So to be a little more succinct:

Groups usually identify a class of users (roles) with similar functions. 
Consider the following example.

Since several users (roles) may all perform accounting functions, it makes 
sense to have an 'accounting' group (role). Likewise, managers who have extra 
privileges could be part of the 'officers' group (role). It is also possible 
for a user to belong to more than one group. However, for this example will use 
the simple case of just one group (role).

Creating Groups
Consider the following example.

A company has 3 different types of users: Order takers (group name orders), 
Administrative (group name admin) and managers (group name mgr). The list of 
users follows:

orders |admin  | mgr

Bob| Carol | Dick
Alice  | Ted   | Jane
Jocelyn| Edna  | 

According to the company rules, Order takers can only view and update the order 
table. 
Administrative users can view and update the employee table, and can view but 
not update the orders table. Managers have full access to both the orders table 
and the employee table.

To implement this, you would follow these steps: 
1.  Create the three groups:

CREATE ROLE orders WITH NOLOGIN;
CREATE ROLE admin  WITH NOLOGIN;
CREATE ROLE mgrWITH NOLOGIN;

Note: It is best to use lowercase for object names in PostgreSQL, otherwise 
they must be enclosed in quotes 

2.  GRANT the appropriate permissions to the groups:

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO orders;
GRANT SELECT ON TABLE orders TO admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE employee TO admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO mgr;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE employee TO mgr;

Note: I specifically did NOT use the GRANT ALL option in above. 
Although GRANT ALL, at first appears to simplify granting permissions, it is 
actually a very bad practice that is often misused. That is because doing so 
would also allow groups and ordinary users the following additional privileges: 
TRUNCATE, REFERENCES & TRIGGER. 
Only the table owner (usually the dba and/or postgres), should have those 
privileges. 

3.  Create the users and add them to the Groups..

CREATE ROLE bob WITH INHERIT LOGIN PASSWORD 'bobspw' IN ROLE orders;
CREATE ROLE alice   WITH INHERIT LOGIN PASSWORD 'alicespw'   IN ROLE orders;
CREATE ROLE jocelyn WITH INHERIT LOGIN PASSWORD 'jocelynspw' IN ROLE orders;
CREATE ROLE carol   WITH INHERIT LOGIN PASSWORD 'carolspw'   IN ROLE admin;
CREATE ROLE ted WITH INHERIT LOGIN PASSWORD 'tedspw' IN ROLE admin;
CREATE ROLE ednaWITH INHERIT LOGIN PASSWORD 'ednaspw'IN ROLE admin;
CREATE ROLE dickWITH INHERIT LOGIN PASSWORD 'dickspw'IN ROLE mgr;
CREATE ROLE janeWITH INHERIT LOGIN PASSWORD 'janespw'IN ROLE mgr;

Note: the INHERIT option is critical, otherwise the user would have to do a 
“SET ROLE group_name;” to obtain the group permissions

Now, any time the users/roles connect to the database, they automatically have 
the correct permissions to access the tables they need. This simplifies the 
task of maintaining user permissions, because whenever a new user enters the 
company, they only have to be created in the appropriate group, and there is no 
need to grant user specific rights to any tables.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general