Postgres
Create a connection
To create a connection you need the required permission ManageConnections.
More on permissions
Navigate to Admin > Connections and click Add Connection.
There you will find an option to connect among others a PostgreSQL.
The bellow parameters are required for the connection:
| Form Parameter | Description | Required | Notes |
|---|---|---|---|
| Name | The name of the connection | YES | String between 1-127 Characters. Only dashes are accepted . |
| Tags | Metadata for your connection | NO | |
| Host | The PostgreSQL DB hostname | YES | |
| Port | The port for the PostgreSQL | YES | |
| DB Name | The name of the DB | YES | |
| Username | The user to connect with. | YES | |
| Password | The password for that user | NO | |
| SSL Mode | The security of the connection | YES | Enum (Disable, Allow, Prefer, Require, Verify-ca, Verify-full) |
Please refer to the official PostgreSQL Documentation for an in depth explanation of the supported SSL Mode values and how they might affect your connection security.
Security Recommendations
In order to minimise security risks, we recommend to connect to your database through a READ ONLY user. This can be created with the following commands, by a DB User with the appropriate permissions. If your DB contains other schemas than the default one (i.e. public), you might have to grant individual access for each of them.
CREATE USER lensesreadonly WITH ENCRYPTED PASSWORD 'mypassword';
GRANT SELECT ON ALL TABLES in schema public to lensesreadonly;
Grant Access
The PostgreSQL data connection is subject to namespace permissions,
via Lenses security Groups. You can then assign User and Service accounts to those Groups.
You can use Namespaces to authorise access to specific datasets or use wildcard to enable all. Lenses permissions are independent of your database permissions.
There are 4 granular permissions for the Elasticsearch connection:
| Form Parameter | Description |
|---|---|
| ShowTable | Can view the Table, but cannot query Data or Schema |
| QueryTable | Can view the Table and query its Data |
| ViewSchema | Can view the Table and its Schema |
| UpdateMetadata | Can view the Table and its Metadata |
Add protection policies
Use Data Policies to obfuscate data retrieved from Lenses via the UI, CLI, or API without affecting how the underlying data is stored [1].
When the policy is applied, Lenses will automatically obfuscate all fields that match. For each connection you can see which indices are using those fields and the policy is applied.