Skip to content

PostgreSQL

Export Configuration#

Exporting a PostgreSQL server via a Tranquil Data peer extends the configuration object with the following fields:

Field Name Type Required Default
database string tranquil_db
endpoint string defaultServiceHost:5432
user string
password string
certificate string

The value of type must be postgres.

The value of database is the name of the Postgres Database (created via the Postgres CREATE DATABASE command) that should be used. All operations will be run on this named database. If the named database does not exist, and createDatabase is set to true, the database will be automatically created.

The value of endpoint is a string of the form HOST:PORT, where the value for PORT is 5432 if absent.

The values of user and password are used to authenticate to the backing datastore. If unspecified, Tranquil Data uses the standard PostgreSQL user postgres, and an empty password string as defaults.

The value of certificate is the PEM-encoded X.509 Public Key Certificate used to connect to the running Postgres instance. If present, connections will be authenticated and run over TLS. If absent, then connections will be unsecured.

Security Support#

The endpoint served by Tranquil Data will accept TLS connections if requested by a connecting Postgres client. The local peer's certificate is used as the server identity in any TLS exchange.

Context Interaction#

The PostgreSQL record identity is based on the PRIMARY KEY. The PRIMARY KEY is an explicitly designated (set of) column(s) in the table schema. If no columns are specified, the first column with UNIQUE values is used for the record identity. In the unlikely case where the table schema contains neither PRIMARY KEY column(s) nor a UNIQUE column, the associated record will not become part of context.

Tranquil Data supports context formation and policy evaluation on a subset of SQL statement syntax. For INSERT, the following variants are supported:

    INSERT INTO table_name
[( column_name [, ...] )]
VALUES ( expression1 [, ...] ) [, ( expression2 [, ...] ), ... ]
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING expression ]

For UPDATE, the following varients are supported:

    UPDATE table_name
SET {column_name = expression} |
    (column_name [, ...] ) = ( expression [, ...] )
[ FROM from_list ]
WHERE condition

For SELECT, the following varients are supported:

    SELECT [ * | expression ] 
FROM table_name
[ [ FULL|LEFT|RIGHT [OUTER] | INNER | CROSS ] JOIN
 table1 ON table2 (expression [, ...] )
[ WHERE condition ]
[ ORDER BY expression ]
[ LIMIT {count | ALL} ]
[ OFFSET start [ ROW | ROWS ] ]

Any Postgres operations not called out above, or any of the above operations applied to system or temporary tables, will be passed to the backing Postgres server for execution outside the context model. This includes:

  • Nested statements, such as a SELECT on the results of a subquery
  • Statements which are based on server-side functions or aggregations