Managing a PEM server v9

Some of the tasks related to managing the PEM server include:

• Restarting the PEM server and agent
• Controlling the PEM server or PEM agent
• Controlling the HTTPD service on Linux and Windows
• Controlling the HTTPD server
• Managing PEM authentication and security
• Modifying the pg_hba.conf file
• Modifying PEM to use a proxy server

Starting and stopping the PEM server and agents

The PEM server starts, stops, and restarts when the Postgres server instance where it resides starts, stops, or restarts. Use the same commands to control the PEM server that you use to control the Postgres server. On Linux platforms, the command that stops and starts the service script varies by platform and OS version.

The PEM agent is controlled by a service named pemagent.

The Windows operating system includes a graphical service controller that displays the server status and offers interactive server control. You can access the Services utility through the Windows Control Panel. When the utility opens, navigate through the listed services and select the service name.

Select Stop, Pause, Start, or Restart to control the state of the service.

Any user or client application connected to the Postgres server is abruptly disconnected if you stop the service. For more information about controlling a service, see EDB Postgres Advanced Server.

Controlling the PEM server or PEM agent on Linux

On Linux platforms, the name of the service script that controls:

• A PEM server on EDB Postgres Advanced Server is edb-as-<x> or ppas-<x>.
• A PEM server on PostgreSQL is postgresql-<x>.
• A PEM agent is pemagent.

Where x indicates the server version number.

You can use the service script to control the service.

To control a service on RHEL 8.x, at the command prompt, assume superuser privileges and issue the command:

```shell
systemctl <service_name> <action>
```

Where:

service_name is the name of the service.

action specifies the action taken by the service. Specify:

• start to start the service.
• stop to stop the service.
• restart to stop and then start the service.
• status to check the status of the service.

Controlling the PEM server or PEM agent on Windows

The Windows operating system includes a graphical service controller that displays the server status and offers interactive server control. The registered name of the service that controls:

• A PEM server host on PostgreSQL is postgresql-<x>.
• A PEM server host on Advanced Server is edb-as-<x> or ppas-<x>.
• A PEM agent is Postgres Enterprise Manager - pemAgent.

Where x indicates the server version number.

In the Windows Control Panel, open the Services utility to see the list of services.

Select Stop the service to stop a service. Any user or client application connected to the server is abruptly disconnected if you stop the service.

Select Pause the service to reload a service's configuration parameters. Pause the service is an effective way to reset parameters without disrupting user sessions for many of the configuration parameters.

Select Start the service to start a service.

Controlling the HTTPD server

On Linux, you can confirm the status of the PEM-HTTPD service by entering the following command at the command prompt:

ps -ef | grep httpd

If Linux responds with an answer like the following, httpd isn't running:

user 13321 13267 0 07:37 pts/1 00:00:00 grep httpd

To start the service on a RHEL 8.x system, use the command:

systemctl start httpd

On Windows, you can use the Services applet to check the status of the PEM HTTPD service. After opening the Services applet, select the PEM HTTPD service from the list.

The Status column displays the current state of the server. Select Start to start PEM HTTPD if the service isn't running.

Managing PEM authentication

Postgres supports a number of authentication methods:

• Secure password (md5)
• GSSAPI
• SSPI
• Kerberos
• Ident
• LDAP
• RADIUS
• Certificate (SSL)
• PAM

Postgres and PEM authentication is controlled by the pg_hba.conf configuration file. Entries in the configuration file specify:

• Who can connect to a specific database
• The type of authentication required before that user is allowed to connect

A typical entry in the pg_hba.conf file that allows a user named postgres to connect to all databases from the local host (127.0.0.1/32) using secure password (md5) authentication connections takes the form:

host all postgres 127.0.0.1/32 md5

Depending on your system configuration, you might also need to create a password file for the user account that the PEM agent uses to connect to the server. This file allows the agent to properly respond to the server’s authentication request. An entry in the password file for a user named postgres, with a password of 1safepwd takes the form:

localhost:5432:*:postgres:1safepwd

The password file is usually named ~root/.pgpass on Linux systems or %APPDATA%\postgresql\pgpass.conf on Windows. For more information about configuring a password file, see the PostgreSQL website.

For more information about the authentication methods supported by Postgres, see the PostgreSQL core documentation.

Editing the PEM server configuration

You can use the PEM client to manage the configuration parameters of the PEM server to enable features or modify default settings. To open the Server Configuration dialog box, select Management > Server Configuration. Then edit the parameter values as needed.

Managing security

PEM provides an interface for managing your Postgres roles and servers.

Login roles

When 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 we strongly recommend (for both security and auditing purposes) creating individual roles for each connecting user. You can use the PEM Query tool, the PEM web interface Create – Login/Group Role dialog box, or a command line client (such as psql) to create a role.

To use the Create – Login/Group Role dialog box to create a role:

1. In the PEM tree, expand the node for the server where you want to create the role.
2. Right-click the Login/Group Roles node.
3. From the context menu, select Create > Login/Group Role.
4. Use the tabs of the Create – Login/Group Role dialog box to define the role.
5. After you finish defining the new role, to create the role, select Save.

To modify the properties of an existing login role, right-click the name of a login role in the tree and select Properties from the context menu. To delete a login role, right-click 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.

Group roles

Group roles can serve as containers and are used to dispense system privileges, such as creating databases, and object privileges, such as inserting data into a table. The primary purpose of a group role is to make the mass management of system and object permissions 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) to create and manage group roles. Options on the context menu provide access to a dialog box that allows you to create a new role or modify the properties of an existing role. You can find more information about creating roles in the PostgreSQL documentation.

Using PEM predefined roles to manage access to PEM functionality

You can use the Login/Group Role dialog box to allow a role with limited privileges to access PEM features such as the Audit Manager, Capacity Manager, or SQL Profiler. PEM predefined 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 shares the privileges associated with each role in which it's 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 predefined role that grants access to the feature. Use the Roles field to select predefined role names from a list.

The SQL tab displays the SQL command that the server executes when you select Save.

This example creates a login role named acctg_clerk that has access to Audit Manager. The role can make unlimited connections to the server at any given time.

You can use PEM predefined roles to allow access to the capabilities listed in the table.

Using a team role

When you register a server for monitoring by PEM, you can specify a team to associate with the server. A team is a group role that you can use to allow or restrict access to one or more monitored servers to a limited group of role members. The PEM client displays a server with a specified team only to those users who are:

• A member of the team role
• The role that created the server
• A role with superuser privileges on the PEM server

To create a team role:

1. In the PEM tree, expand the node for the server where you want to create the role.
2. Right-click the Login/Group Roles node.
3. From the context menu, select Create > Login/Group Role.
4. In the Create - Login/Group Role dialog box, use the fields to specify the properties of the team role.

Object permissions

A 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 PEM client interface.

Object permissions are managed with the graphical object editor for each particular object. For example, to assign privileges to access a database table, right-click the table name in the tree and select Properties from the context menu. Use 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.

Server configuration

You can use the Server Configuration dialog box to modify values of user-configurable parameters that control PEM behavior. To access the Server Configuration dialog box, connect to the PEM server, and select File > Server Configuration.

Enter a parameter name in the search box in the upper-right corner of the dialog to locate a specific parameter in the list.

To modify a parameter value, edit the content displayed in the Value field to the right of a parameter name. To save your changes, select the Save icon in the upper-right corner of the dialog box.

Server configuration parameters - reference

You can use global configuration options to modify aspects of the PEM Server's behavior. The list of configuration parameters is subject to change.