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 mgr WITH 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 edna WITH INHERIT LOGIN PASSWORD 'ednaspw' IN ROLE admin; CREATE ROLE dick WITH INHERIT LOGIN PASSWORD 'dickspw' IN ROLE mgr; CREATE ROLE jane WITH 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
