REST API Usage in EDB Postgres Enterprise Manager (PEM)

Overview

One of the standout features of EDB Postgres Enterprise Manager (PEM) is the REST API, which offers efficient data reading and writing capabilities, as well as access to various functionalities without the need to log in to the PEM UI. This blog will guide you through the process of utilizing the REST API in PEM for streamlined database management.

Note that for all the examples, we assume that you are working on the machine where PEM is installed. In a production cluster, this is unlikely to be the case and you should replace `127.0.0.1` with the network address of your PEM server.

Accessing Swagger Editor

To start your journey, you need to access the REST API documentation via the following URL:

https://127.0.0.1:8443/pem/api

This documentation, presented through the Swagger Editor, serves as a reference point for understanding the available API endpoints and their functionalities.

Accessing the Endpoints:

Token-Based Authentication

Security is a top priority, and PEM ensures it through a token-based authentication mechanism for accessing its REST APIs. This authentication method not only secures your interactions but also simplifies exploration of the APIs without requiring UI login.

Step-by-Step Guide:

Let's dive into the step-by-step process of using the REST API in PEM:

1.Token Generation:

The initial step involves generating a token for authentication. To achieve this, follow these instructions:

• Make a POST request to `https://127.0.0.1:8443/pem/api/token/` with the following information:
  • Headers: `accept: application/json`, `Content-Type: application/json`
  • Request body: `{"username":"pemuser","password":"xyz"}`
• Retrieve the token from the `X-Subject-Token` field in the response.
• Example to generate token: Let, Username: pemuser and Password: xyz

Request: 

curl -iX POST "https://127.0.0.1:8443/pem/api/token/" -H "accept: application/json"
-H "Content-Type: application/json" -d
"{\"username\":\"pemuser\",\"password\":\"xyz\"}"

Response:

Copy the token from X-Subject-Token.

2.Accessing the v9/server/{server_id} endpoint:

The latest version PEM API, v9, offers the most up-to-date features and improvements.

The `v9/server/{server_id}` endpoint is a powerful space within the REST API. As an example, let's look at changing the password of a server:

• Utilize the token obtained from Step 1.
  • “server_id” is by default 1 for pem server but to fetch the server id of other servers, the command below can be executed:

curl -iX POST "https://127.0.0.1:8443/pem/api/token/" -H "accept: application/json"
application/json" -H "X-Auth-Token: 72e3ec07-1e15-46c8-a4d9-96ebff029a7d"

• Execute a PUT request to `https://127.0.0.1:8443/pem/api/v9/server/1` with the following headers:
  • `accept: */*`
  •  `X-Auth-Token: `
  • `Content-Type: application/json`
• In the request body, provide the new password: `{"asb_password":"new_password"}`.

curl --insecure -X PUT "https://127.0.0.1:8443/pem/api/v9/server/1" -H "accept:
*/*" -H "X-Auth-Token: 72e3ec07-1e15-46c8-a4d9-96ebff029a7d" -H "Content-Type:
application/json" -d "{\"asb_password\":\"postgres\"}"

This command will change the password of the specified server (server with ID 1 in this case).

In conclusion, EDB Postgres Enterprise Manager's REST API offers a powerful and efficient way to manage databases. By following the steps outlined above, you can navigate the API's capabilities, authenticate securely and perform operations. Harness the power of the PEM REST API to enhance your database management experience.

In addition to the steps mentioned above, let’s explore a practical example of how to achieve server password change using Python and the requests module. This approach adds hands-on experience to the theoretical understanding we’ve discussed.

# requests library provides a simple API for interacting with HTTP operations
# such as GET , POST , PUT etc
import requests

# suppress ssl warnings

requests.packages.urllib3.disable_warnings( )

# Step 1: Token Generation

def generate_token(username, password):
  url = "https://127.0.0.1:8443/pem/api/token/"
  headers = {
    "accept": "application/json",
    "Content-Type": "application/json"
  }
  data = {
    "username" :username,
    "password" password
  }
  response = requests.post(url, headers=headers, json=data, verify=False)

# extracting the token from the response

token = response.headers["X-Subject-Token"]
return token

# Step 2: Changing Server Password

def change_server_password(token, server_id, new_password):
  url = f"https://127.0.0.1:8443/pem/api/v9/server/{server_id}""
  headers = {
    "accept": "*/",
    "X-Auth-Token": token,
    "Content-Type": "application/json"
  }
  data = {
    "asb_password"": new_password
  }
  response = requests.put(url, headers=headers, json=data, verify=False)
  return response.status_code

# Main Execution

if __name__ == "__main__":

# taking user input through cmd

username = input ("Enter PEM User Username: ")

# You can also use getpass for hidden input

password = input("Enter PEM User Password: ")
token = generate_token(username, password)
status_code = change_server_password(token, server_id, new_password)

if status_code == 200:
  print("Server password changed successfully!")
else:
  print("Server password change failed.")

Handling HTTPS Requests with SSL

In the scenarios mentioned above, we employed token-based authentication for HTTPS requests and used the --insecure flag to disable SSL verification. However, in many cases, SSL verification is crucial. Let's explore what to do in such situations.

1. Provide a Client Certificate

--cert Option

Your client certificate identifies you to the server and is crucial for authentication. To provide your client certificate, use the --cert option:

curl --cert client.crt https://example.com

2. Include the Private Key

--key Option

To authenticate using your client certificate, you need to include the private key associated with it. Use the --key option for this:

curl --cert client.crt --key client.key https://example.com

3. Handling Intermediate CAs

--cacert Option

If the server's certificate is signed by an intermediate CA (Certificate Authority) or isn't in your system's trusted CA list, include the CA certificate file using the --cacert option. This ensures that certificate chain validation works correctly:

curl --cert client.crt --key client.key --cacert ca.crt https://example.com

Here's a Python code snippet demonstrating how to make an HTTPS request while utilizing SSL verification:

import requests

# Define the URL of the HTTPS endpoint

url = 'https://example.com/api/resource'

# Specify the path to your trusted Certificate Authority (CA) certificate

ca_cert_file = '/path/to/ca.crt'

# Specify your client certificate and private key (if required)

client_cert = ('/path/to/client_certificate.crt', '/path/to/client_private_key.key')

# Create a session with custom SSL settings

session = requests.Session( )

# Configure SSL certificate verification

session.verify = ca_cert_file

# If client certificate and private key are needed,  provide them

if client_cert[0] and client_cert[1]:

session.cert = client_cert

# Define headers or authentication tokens as needed

headers = {
  'Authorization': 'Bearer YourToken'
}

# Make an HTTPS GET request

response = session.get(url, headers=headers)

# Check the response

if response.status_code == 200:
  print ('Request was successful!')
  print ('Response:', response.text)
else:
  print ('Request failed with status code:', response.status_code)

I hope you found these tips helpful for utilizing the REST API in Postgres Enterprise Manager for streamlined database management! You can learn more about the features and functionality in PEM in our documents and release notes.  If you’re new to PEM, you can  get a high level overview here.