5.3 Managing Security5.3.1 Login RolesWhen you connect to the PEM server, you must provide role credentials that allow access to the database on which the PEM server stores data. By default, the postgres superuser account is used to initially connect to the server, but it is strongly recommended (for both security and auditing purposes) that individual roles are created for each connecting user. You can use the PEM Query Tool, the PEM web interface Create – Login/Group Role dialog, or a command line client (such as psql) to create a role.To use the Create – Login/Group Role dialog to create a role, expand the node for the server on which the role will reside in the PEM tree control, and right-click on the Login/Group Roles node to access the context menu. Then, select Login/Group Role… from the Create menu (see Figure 5.3).Use fields on the tabs of the Create – Login/Group Role dialog (see Figure 5.4) to define the role. To display the PEM online help in a browser tab, click the help (?) button located in the lower-left corner of the dialog.When you've finished defining the new role, click Save to create the role.To modify the properties of an existing login role, right click on the name of a login role in the tree control, and select Properties from the context menu. To delete a login role, right click on the name of the role, and select Delete/Drop from the context menu.For more complete information about creating and managing a role, see the PostgreSQL online documentation:5.3.2 Group RolesGroup roles can serve as containers, used to dispense system privileges (such as creating databases) and object privileges (e.g. inserting data into a particular table). The primary purpose of a group role is to make the mass management of system and object permissions much easier for a DBA. Rather than assigning or modifying privileges individually across many different login accounts, you can assign or change privileges for a single role and then grant that role to many login roles at once.Use the Group Roles node (located beneath the name of each registered server in the PEM tree control) to create and manage group roles. Options on the context menu provide access to a dialog that allows you to create a new role or modify the properties of an existing role. You can find more information about creating roles at:You can use the Login/Group Role dialog to allow a role with limited privileges to access PEM features such as the Audit Manager, Capacity Manager, or SQL Profiler. PEM pre-defined roles allow access to PEM functionality; roles that are assigned membership in these roles can access the associated feature.When defining a user, use the Membership tab to specify the roles in which the new user is a member. The new user will share the privileges associated with each role in which it is a member. For a user to have access to PEM extended functionality, the role must be a member of the pem_user role and the pre-defined role that grants access to the feature. Use the Roles field to select pre-defined role names from a drop down list.The SQL tab displays the SQL command that the server will execute when you click Save.The example shown above creates a login role named acctg_clerk that will have access to the Audit Manager; the role can make unlimited connections to the server at any given time.
5.3.4 Using a Team RoleWhen you register a server for monitoring by PEM, you can specify a Team that will be associated with the server. A Team is a group role that can be used to allow or restrict access to one or more monitored servers to a limited group of role members. The PEM client will only display a server with a specified Team to those users who are:To create a team role, expand the node for the server on which the role will reside in the PEM tree control, and right-click on the Login/Group Roles node to access the context menu. Then, select Login/Group Role… from the Create menu; when the Create - Login/Group Role dialog opens, use the fields provided to specify the properties of the team role.5.3.5 Object PermissionsA role must be granted sufficient privileges before accessing, executing, or creating any database object. PEM allows you to assign (GRANT) and remove (REVOKE) object permissions to group roles or login accounts using the graphical interface of the PEM client.Object permissions are managed via the graphical object editor for each particular object. For example, to assign privileges to access a database table, right click on the table name in the tree control, and select the Properties option from the context menu. Use the options displayed on the Privileges tab to assign privileges for the table.The PEM client also contains a Grant Wizard (accessed through the Tools menu) that allows you to manage many object permissions at once.