• Resources
  • Latest resources

Article: Role management in PostgreSQL Maestro

Last modified: Sep 1, 2006

Abstract

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

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    

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).

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   

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

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   

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    

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:

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';

Picture 5.1

Picture 5.2

Picture 5.3

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).

Picture 6. Viewing table permissions.

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

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)

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).

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:

• PostgreSQL Maestro home page
• Download free trial version of PostgreSQL Maestro
• Read about roles in PostgreSQL documentation

Back to resource list