# Before you create a PostgreSQL workspace

The source and destination databases must exist before you connect to them. They should use the same version of PostgreSQL and must have the same extensions installed.

Use the following instructions to create database accounts that have the minimum required permissions that are needed for Tonic Structural to function.

## Creating a source database account

The following is an example of how to create a new user, called `tonic`, and grant the necessary permissions on the `public` schema.

For the source database, we recommend that you use a backup instead of connecting directly to your production environment.

{% hint style="info" %}
If your database contains additional schemas that are included, then you must run the same commands for those schemas. 
{% endhint %}

{% code overflow="wrap" %}

```
--create a user. It's better to use a user with limited permissions as opposed to an rds_superuser
CREATE USER tonic WITH PASSWORD 'tonic_password';

--ensure that the tonic user gets the correct access to new tables as they are added
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO tonic;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO tonic;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO tonic;

--add USAGE GRANTs on all schemas in the DB 
GRANT USAGE ON SCHEMA public TO tonic; 

--add SELECT and GRANTs ON ALL TABLES in each schema in the DB. 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tonic; 

--add USAGE GRANTs on all sequences in the DB 
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO tonic; 

--add SELECT GRANTs on all sequences in each schema 
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO tonic; 
```

{% endcode %}

## Creating a destination database account

The following is an example of how to create a new user, called `tonic`, grant the user ownership of the `public` schema, and make the user a superuser.

{% hint style="info" %}
If your database contains additional schemas that are included, then you must run the same commands for those schemas. 
{% endhint %}

```
--create a new user. 
CREATE USER tonic WITH PASSWORD 'tonic_password'; 

--make this user the schema owner of whichever schemas are in the database. 
ALTER SCHEMA public OWNER TO tonic; 

--allow user to create new schemas. 
GRANT CREATE ON DATABASE test_data_output TO tonic; 

--This user must be given the SUPERUSER role (or azure_pg_admin in Azure Postgres).
--The exact command depends on if you use Amazon RDS.

--On Azure Postgres
GRANT tonic TO azure_pg_admin;

--On Postgres RDS 
GRANT RDS_SUPERUSER TO tonic;

--On Google Cloud SQL Postgres
GRANT cloudsqlsuperuser to tonic;

--On Postgres (non-RDS) 
ALTER USER tonic WITH SUPERUSER;
```

## Configuring handling of infinity date and time values <a href="#infinity-date-time-config" id="infinity-date-time-config"></a>

### Default behavior

By default, when Structural:

* Displays data on the application
* Processes data during data generation

It converts `-infinity` and `infinity` values in date or time columns to the closest representable date or time.

When Structural writes data to the destination:

* It writes date or time values that are the maximum representable value, such as `9999-12-31` in a date column, as `infinity`.
* It writes date or time values that are the minimum representable value as `-infinity`.

### Disabling the default behavior

To opt out of the default behavior, set the [environment setting](https://docs.tonic.ai/app/admin/environment-variables-setting) `TONIC_POSTGRES_DISABLE_DATETIME_INFINITY_CONVERSIONS` to `true`.

You configure the setting in the Structural web server and worker.

When the setting is `true`, then during data processing, Structural drops any source database rows that contain `-infinity` or `infinity` values.

### Example

The source database contains a `date` column with the values `infinity` and `9999-12-31`.

By default, on the application and during data processing, both values appear as `9999-12-31`.

If no generator is applied, then in the destination database, both values are written as `infinity`.

If the default behavior is disabled:

* The row containing `infinity` is dropped.
* `9999-12-31` is processed and written to the destination as `9999-12-31`.

## Configuring whether Structural creates the destination database schema <a href="#postgresql-schema-creation" id="postgresql-schema-creation"></a>

By default, during each data generation job, Structural creates the database schema for the destination database tables, then populates the database tables based on the workspace configuration.

If you prefer to manage the destination database schema yourself, then set the [environment setting](https://docs.tonic.ai/app/admin/environment-variables-setting) `TONIC_POSTGRES_SKIP_CREATE_DB` to true. You can add this setting manually to the **Environment Settings** list on **Structural Settings**.

You can override this setting manually in individual workspaces. For more information, go to [advanced-overrides](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/advanced-overrides "mention").

When `TONIC_POSTGRES_SKIP_CREATE_DB` is `true`, or the workspace overrides the setting to `true`, then Structural does not create the destination database schema. Before you run data generation, you must create the destination database with the full schema.

During data generation, Structural deletes the data from the destination database tables, except in the following cases:

* Tables that use Preserve Destination mode.
* Upsert data generation.

It then populates the tables with the new destination data.

For a diagram of the data generation process when you manage the destination schema, go to [#data-process-customer-schema](https://docs.tonic.ai/app/workflows/data-generation-run-job/data-generation-process#data-process-customer-schema "mention").