SQL Maestro Group / Products / PostgreSQL / PostgreSQL Maestro / Resources / Role Management

Article: Role management in PostgreSQL Maestro

Last modified: Sep 1, 2006

Abstract

This article discloses the ways of role management provided by PostgreSQL Maestro. Learn how to create, edit, grant and carry out other manipulations with roles with reliable and convenient facility. Due to the simplicity of narration it will be understandable for beginners though the readers are supposed to know the main principles of database management. The attention is placed mainly on practical aspects, so the reader will pass to the general points without spending time on verbose discussions.

Introduction

The management of database access permissions in PostgreSQL 8.1 is based on the idea of using roles. This idea subsumes the concept of Users and Groups implemented in earlier versions, because a role can be taken as either a database user, or a group of database users, depending on how the role is set up, so we can say that roles erase basic distinctions between users and groups.

Attributes

During the server installation process PostgreSQL creates one predefined role with superuser status. By default it is named as operating system user that initialized the database cluster. So if you want to create more roles you have to connect as this initial role. A number of role attributes is enlarged in comparison with the user attributes. For example to create a new user it was necessary to have a superuser status; now a new additional attribute CREATEROLE is a separate privilege which can be given to any role.

Postgres=# CREATE ROLE admin CREATEROLE; 
CREATE ROLE

(the role admin can create other roles)

This approach avoids the dangers of operating as a superuser for tasks that do not really require it. Another additional attribute is a Login privilege, only roles that have this attribute can be used as the initial role name for a database connection.

Postgres=# CREATE ROLE staff LOGIN;
CREATE ROLE

(the role staff can connect to the server and ready to work with database)

In fact this can simplify the management of access limitations and group privileges. (e.g. you know that some employee is at vacation, to guarantee that no one would connect the database under his role you can void the Login attribute.)

The control over role's attributes in PostgreSQL Maestro is produced as a list of role properties (Picture 1) where you can include or exclude given attributes with a help of usual checkboxes (ticks).

create new role
Picture 1. Setting role's attributes.

If something doesn’t fit your conditions you can always adjust settings by means of Role editor (Picture 2),or even by entering an ALTER ROLE statement in the SQL editor.

Picture 2. Using Role editor.

Privileges

As an object owner, the role is able to do anything with the object. To allow object manipulating for other roles or specify their activities, the privileges must be granted. Any role may have privileges directly granted to it, privileges of a role it is a member of, and privileges granted to all roles (granted to PUBLIC).

 Postgres=# GRANT INSERT ON TABLE postal_address TO PUBLIC;
 GRANT ROLE

(now all the roles can insert records into table postal_address)

If you want to provide a role (which is not an object owner) with an ability of giving privileges to other roles you have to set a grant option (Picture 3). A distinctive feature of grant option is that it cannot be granted to PUBLIC.

Postgres=# GRANT SELECT ON sales.specialoffer TO admin WITH GRANT OPTION;
GRANT ROLE

(role admin has rights to grant SELECT privilege on table sales.specialoffer)

grant with grant option
Picture 3. Granting with grant option.

In PostgreSQL Maestro the privileges for one or another database object are represented as a simple table where the possible for a certain object privilege is indicated by the white cell, and unavailable one by the gray cell.

Membership

Any role can act as a user, a group, or both. Roles can own database objects (for example, tables or functions) and can assign privileges on those objects to other roles to control who has access to which objects using the GRANT statement.

Postgres=# GRANT developers TO staff;
 GRANT ROLE

(role staff is a member of role developers)

PostgreSQL Maestro includes all abilities of PostgreSQL role management. It is possible to grant membership in a role to another role, thus allowing the member role use of privileges assigned to the role it is a member of. The control checkbox in the admin option column is responsible for giving a role the rights to grant membership to others (Picture 4).

Postgres=# GRANT developers TO maintenance WITH ADMIN OPTION;
GRANT ROLE

(role maintenance has rights to grant a membership in role developers)

memebership grant
Picture 4. Granting with Admin option.

You can grant membership to other group roles too (since there isn't really any distinction between group roles and non-group roles). The only restriction is that you can't set up circular membership loops. (E.g. group role A containing role B cannot be a member of role B.). Membership rules can be really convenient when designing a tree of your database users especially when their number and privileges are constantly changing.

PostgreSQL Maestro offers you an excellent solution to use all the advantages of the Role concept. Each time you create a new role Maestro gives a list of existing roles where you can choose which of them you would like to add in this new role. At the same time you can make the new role a member of an existing role or leave it completely stand-alone.

A Complex Example

The regulations described above can be set in the psql console. For example we want to create a new role with canlogin, createdb and createrole attributes then limit a number of possible connections, restrict password's validation period, grant a membership in another role to it and overwrite some specific server variables. Taking into consideration that we have already connected to the database the statement will be as follows:

Listing 1.

CREATE ROLE maintenance
  LOGIN
  VALID UNTIL 'August 31, 2006'
  CREATEDB
  CREATEROLE
  CONNECTION LIMIT 20
  ENCRYPTED PASSWORD 'maintenance';
 
GRANT maintenance TO "Staff" WITH ADMIN OPTION;
 
GRANT admins TO maintenance;
 
ALTER ROLE maintenance
  SET default_with_oids TO 'on';
 
ALTER ROLE maintenance
  SET log_duration TO 'on';

Let's compare it with actions that the PostgreSQL Maestro demands to achieve the same result (to look through the whole process see Picture 5.1 - 5.4):

Step1. Select "Create New Role..." in the pop-up menu.

create new role
Picture 5.1

Step 2. Set the necessary attributes.

create new role
Picture 5.2

Step 3. Set the membership relations with other roles.

create new role
Picture 5.3

Step 4. Customize the environment of the role.

create new role
Picture 5.4

Retrieving Information

To make the advantages of PostgreSQL Maestro even more evident we would like to make next examples. Suppose you need to look through the role's privileges for exact database object.

postgres=# z sales.store
                         Access privileges for database "postgres"
 Schema |  Name | Type  |                       Access privileges
--------+-------+-------+----------------------------------------------------------------
 sales  | store | table | {postgres=arwdRxt/postgres,admins=awdRxt/postgres,=r/postgres}
(1 row)

And this is how you can get it by clicking a corresponding table in PostgreSQL Maestro (Picture 6).

role's privileges
Picture 6. Viewing table permissions.

To retrieve the information about roles attributes and its membership in other roles you may execute the following queries:

Listing 2.

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles;

       rolname       | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin
---------------------+----------+------------+---------------+-------------+-------------
 admins              | f        | t          | f             | f           | f
 Sales_department    | f        | t          | f             | f           | t
 postgres            | t        | t          | t             | t           | t
 Accounts_department | t        | t          | f             | t           | t
 Developers          | f        | t          | f             | f           | f
 maintenance         | f        | t          | f             | t           | t
 managers            | f        | t          | t             | f           | f
 Staff               | f        | t          | f             | f           | f
(10 rows)

and

Listing 3.

SELECT pg_get_userbyid(member) AS rolename, 
       pg_get_userbyid(roleid) AS name, 
       pg_get_userbyid(grantor) AS grantor, 
       admin_option 
FROM pg_auth_members 
WHERE 
  (pg_get_userbyid(member) = 'Staff') 
ORDER BY name;

 rolename |    name     | grantor  | admin_option
----------+-------------+----------+--------------
 Staff    | Developers  | postgres | t
 Staff    | admins      | postgres | f
 Staff    | maintenance | postgres | t
(3 rows)

To get the same information about a role in PostgreSQL Maestro you just need to click the role name situated in the explorer tree (Picture 7).

membership in other roles
Picture 7. Getting the information about role's attributes and membership.

Means of PostrgeSQL Maestro effectively use all abilities of PostgreSQL. Many of procedures are accessible in a few mouse clicks. If you are looking for a professional interface to work with PostgreSQL, there is no need to keep on searching, because you have found it.

Resources:

Back to resource list