Postgres


PostgreSQL to Lenses.io

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.

Postgres Connection

The bellow parameters are required for the connection:

Form ParameterDescriptionRequiredNotes
NameThe name of the connectionYESString between 1-127 Characters. Only dashes are accepted .
TagsMetadata for your connectionNO
HostThe PostgreSQL DB hostnameYES
PortThe port for the PostgreSQLYES
DB NameThe name of the DBYES
UsernameThe user to connect with.YES
PasswordThe password for that userNO
SSL ModeThe security of the connectionYESEnum (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.

PosgreSQL Lenses Permissions

There are 4 granular permissions for the Elasticsearch connection:

Form ParameterDescription
ShowTableCan view the Table, but cannot query Data or Schema
QueryTableCan view the Table and query its Data
ViewSchemaCan view the Table and its Schema
UpdateMetadataCan 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].

Postgre Data Policies

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.

Read on data protection policies.