PostgreSQL plugin

The PostgreSQL plugin allows you to connect to and visualize any PostgreSQL resource.

For more information about what this plugin does and the data streams it retrieves, see:

To add a data source click on the + next to Data Sources on the left-hand menu in SquaredUp. Search for the data source and click on it to open the Configure data source page.

Before you start

The PostgreSQL plugin is a "hybrid" plugin, meaning it is available in SquaredUp as both a cloud and an on-prem plugin.

Use the cloud plugin if your PostgreSQL database is available on the internet. You do not need to configure a relay agent.
Use the on-prem plugin to access a PostgreSQL database on a server on-prem. You will need to configure a relay agent before you configure the PostgreSQL on-prem plugin.
An on-prem data source uses a relay agent to connect SquaredUp to a data source running on your internal network.
A relay agent is installed on a server on your internal network, and has access to your data source.
Using a relay agent means that you don't need to open your firewall to allow access.

Configuring and deploying an agent

For an on-prem plugin you will need a relay agent that can access the server hosting your on-prem data source. You do not need a relay agent for cloud plugins.

If you have already created a relay agent in SquaredUp that can access this data source, then you can skip this step and choose the agent group you want to use while Configuring the data source.

You can install an agent on either Windows or Linux:

Configuring the data source

Display name:
Enter a name for your data source. This helps you to identify this data source in the list of your data sources.
Agent group:
Select the Agent Group that contains the agent(s) you want to use.
Username:
Enter the PostgreSQL account username
Password:
The PostgreSQL account password
Server:
The server address or IP to connect to. Do not include a port with the SERVER value.
If you are adding the PostgreSQL on-prem data source, the server must be accessible from the machine running the SquaredUp on-prem relay agent.
Database:
The name of the database to initially connect to. Any queries executed without a database explicitly set will be run against this database.
Port:
The port to use to connect to PostgreSQL. The default is 5432, but can be changed as needed.
Ignore certificate errors:
If you activate this checkbox the data source will ignore certificate errors when accessing the server. This is useful if you have self-signed certificates.
Enable Object Indexing (optional):
Object Query (optional):
The PostgreSQL connection can create objects in the Knowledge Graph index and these can be used to create context sensitive objects and drill-downs.
This plugin supports the creation of objects from a database query. Multiple separate queries can be configured, using semi-colons to separate the queries, e.g.
```
SELECT 'Author'    AS Type, author_id    AS rowId, name  AS Name FROM authors;
SELECT 'Publisher' AS Type, publisher_id AS rowId, name  AS Name FROM publishers;
SELECT 'Genre'     AS Type, genre_id     AS rowId, name  AS Name FROM genres;
SELECT 'Book'      AS Type, book_id      AS rowId, title AS Name, height AS Height FROM books;
```
The resulting table must contain rowId, name, and type fields. Additional fields are allowed, and any non-null fields will be included with the object when imported into the Knowledge Graph.
rowId
name
Height
type
1
Conan Doyle, Arthur
Author
2
Penguin
Publisher
1
Fiction
Genre
3
The Final Problem
22.86
Book
Relationship Query (optional):
If you are creating objects with the Object Query set in the previous step, you can relate those objects in the Knowledge Graph.
Along with an Object Query an additional Relationship Query can also be used. The result of this query must contain the fields outType, outRowId, inType, inRowId, and label.
```
SELECT 'Publisher' AS outType, publisher_id AS outRowId, 'Publishes' AS Label, 'Book' as inType, book_id AS inRowId FROM books;
SELECT 'Genre'     AS outType, genre_id     AS outRowId, 'Includes'  AS Label, 'Book' as inType, book_id AS inRowId FROM books;
SELECT 'Author'    AS outType, author_id    AS outRowId, 'Wrote'     AS Label, 'Book' as inType, book_id AS inRowId FROM books;
```
After the import is complete the objects created from the Object Query will now be linked.
outType
outRowId
label
inType
inRowId
Author
22
Wrote
Book
17

Optional: Restrict access to this data source:
You can enable this option if you only want certain users or groups to have access to the data source, or the permission to link it to new workspaces. See data source access control for more information.

The term data source here really means data source instance. For example, a user may configure two instances of the AWS data source, one for their development environment and one for production. In that case, each data source instance has its own access control settings.

By default, Restrict access to this data source is set to off. The data source can be viewed, edited and administered by anyone. If you would like to control who has access to this data source, switch Restrict access to this data source to on.

Use the Restrict access to this data source dropdown to control who has access to the workspace:

By default, the user setting the permissions for the data source will be given Full Control and the Everyone group will be given Link to workspace permissions.
Tailor access to the data source, as required, by selecting individual users or user groups from the dropdown and giving them Link to workspace or Full Control permissions.
If the user is not available from the dropdown, you are able to invite them to the data source by typing in their email address and then clicking Add. The new user will then receive an email inviting them to create an account on SquaredUp. Once the account has been created, they will gain access to the organization.
At least one user or group must be given Full Control.
Admin users can edit the configuration, modify the Access Control List (ACL) and delete the data source, regardless of the ACL chosen.

Access level	Permissions
Link to workspace	User can link the data source to any workspace they have at least Editor permissions for. Data from the data source can then be viewed by anyone with any access to the workspace. User can share the data source data with anyone they want. User cannot configure the data source in any way, or delete it.
Full Control	User can change the data source configuration, ACL, and delete the data source.

See Access control for more information.

Click Test and add to validate the data source configuration. SquaredUp will now attempt to connect to SquaredUp using the provided authentication method.
- Testing passed – a success message will be displayed and then the configuration will be saved.
- Testing passed with warnings – warnings will be listed and potential fixes suggested. You can still use the data source with warnings. Select Save with warnings if you believe that you can still use the data source as required with the warnings listed. Alternatively, address the issues listed and then select Rerun tests to validate the data source configuration again. If the validation now passes, click Save.
- Testing Failed – errors will be listed and potential fixes suggested. You cannot use the data source with errors. You are able to select Save with errors if you believe that a system outside of SquaredUp is causing the error that you need to fix. Alternatively, address the issues listed and then select Rerun tests to validate the data source configuration again. If the validation now passes, click Save.
You can edit any data source configurations at any time from Settings > Data Sources.

Next steps

Data streams

You can use these data streams to create new tiles to show data, or if there are preconfigured dashboards installed you can copy or edit those.

Data streams standardize data from all the different shapes and formats your tools use into a straightforward tabular format.

While creating a tile you can tweak data streams by grouping or aggregating specific columns.

Depending on the kind of data, SquaredUp will automatically suggest how to visualize the result, for example as a table or line graph.

Data streams can be either global or scoped:

Global data streams are unscoped and return information of a general nature (e.g. "Get the current number of unused hosts").
A scoped data stream gets information relevant to the specific set objects supplied in the tile scope (e.g. "Get the current session count for these hosts").

See Data Streams for more information.

The following data streams are installed with this plugin.

When using the data source to create data streams you have the option to create three different query types, as detailed below.

This allows for any tabular data to be pulled into SquaredUp and be displayed in a standard table.

This configurable data stream type allows for data to be displayed in a visual format, such as a line graph. When writing a query for use as a Metrics query, the below three columns are mandatory and all others will be ignored.

Column Name (case sensitive)	Content
label	The label for the value passed in, for example ‘Percent Utilization’. Each distinct ‘label’ value will get its own line color which includes only values for that label.
timestamp	A DateTime value of when this reading occurred
value	A numerical non-null value to be visualized

This configurable data stream allows for data to be displayed as a state tile (Red, Yellow, or Green blocks). When writing a query for use in a State query the below three columns are mandatory and all others will be ignored.

Column Name (case sensitive)	Content
label	The label for the state passed in, for example ‘Deliveries’. These ‘label’ values must be distinct and will each produce their own status block
id	If this state will be tied to an object, then that object’s rowId should be used, otherwise passing the ‘label’ value a second time will work effectively.
state	The state of the labeled object, options are ‘Success’, ‘Warning’, ‘Error’, and ‘Unknown’

Was this article helpful?

Have more questions or facing an issue?

Submit a ticket

rowId	name	Height	type
1	Conan Doyle, Arthur		Author
2	Penguin		Publisher
1	Fiction		Genre
3	The Final Problem	22.86	Book

outType	outRowId	label	inType	inRowId
Author	22	Wrote	Book	17