Connect to PostgreSQL

Sigma supports connections to the PostgreSQL database.

Requirements

  • Admin privileges in your Sigma organization; see Account types.
  • A PostgreSQL data warehouse
  • A configured PostgreSQL cluster
  • We recommend that you avoid granting excessive permissions to the account you use when connecting to your data store; for example, you do not require SYSADMIN-level access.

Limitations

We do not support the following features and interactions:

  • Dataset warehouse views
  • Export to cloud storage
  • Connecting to the warehouse through OAuth
  • Sigma result IDs cache
  • Input tables
  • Geography functions

Create a PostgreSQL connection

Follow these basic steps to create a connection:

  1. Click the user icon at the top right of your screen. 
    The user icon is usually composed of your initials.

  2. In the drop-down menu, select Add connection.
    Select the Add connection option

  3. The Add new connections page appears.
    The connections page lists all supported database types, as icons

  4. In the Connection details, specify these:

    Name
    Specify the Name of the new connection. Sigma displays this name in the connection list.
    Here, we use PostgreSQL connection.
    Type
    Select the tile that represents the data warehouse you use.
    Here, click the PostgreSQL tile.
  5. In the Connection Credentials section, specify the following fields.

    Host
    The path to your database.
    This can be a URL, or an IP address. 
    See Connection credentials.
    Port
    The port that Sigma uses to connect to the host.
    The default port for PostgreSQL is 5432
    User
    The username, or account, for connecting to the PostgreSQL data warehouse.
    For example, test.
    Password
    Enter the password that corresponds to the User on the PostgreSQL account.
    Database
    The name of the database you plan to query.
    Enable TLS
    Optional.
    This switch enables or disables TLS encryption on your connection.
    Enabled by default.
    SSH Tunnel
    Optional.
    This switch enables the SSH protocol for secure remote login. For details, see Connect through SSH.
    Disabled by default.
    If on, specify the Tunnel host and Tunnel port fields.
    Tunnel host
    The path to the tunnel server.
    This can be a URL, or an IP address.
    Appears only if SSH Tunnel is on.
    Tunnel port
    The port where the tunnel connects.
    Appears only if SSH Tunnel is on.

    Connection credentials for MySQL

  6. In the Connection Features section, specify the following:

    Connection timeout
    The time before timeout (or cancellation), in seconds, that Sigma waits for the query to return results.
    Default is 120, or 2 minutes.
    Maximum is 600, or 10 minutes.
    Use friendly names
    This switch makes column names from the data source more readable.
    For example, a database column ORDER_NUMBER appears as Order Number.
    On by default.

    Connection features for MySQL

  7. In the Write Access section, decide if you require write access.
    See Set up write access.

    Enable write access
    Necessary for CSV upload and Materialization.
    Off by default.
    If on, specify the Write schema field.
    Write schema
    The schema where Sigma writes tables. 
    Appears only if Enable write access is on.

    Write access for the connection

  8. After you specify all the parameters of the connection, click Create.
    Create the connection

  9. After you successfully create your connection, Sigma displays it on the screen.
    Connection successfully created

  10. To verify your connection, click Browse Connection, and then explore the visible databases and tables.

  11. Click Add Permission to grant data access for users in your organization.
    See Data permissions.

  12. The new connection also appears in the list of connections you have in your account. 

Configure PostgreSQL for Sigma

Follow the steps below to configure PostgreSQL to connect to Sigma.

  1. In the EC2 Console, navigate to the Elastic IP.
    company apps
  2. Create an Elastic IP Address.
    This step is necessary to make the IP address publicly accessible.
  3. Click Allocate Elastic IP address
    company apps
  4. Click Allocate.
  5. Go back to your Redshift Cluster, and click Actions.
  6. Click Modify publicly accessible setting.
  7. Click Enable and select the Elastic IP address you created in the Elastic IP Address dropdown.
    company apps
  8. In the EC2 Console, navigate to Security Groups.
    company apps
  9. On the Create security group page, name and describe the security groups. 
    Note: a VPC is an elastic network that's populated by infrastructure, platform, and application services that share common security and interconnection.
    company apps
  10. Add Sigma’s IP addresses to both the Inbound rules and Outbound rules.
    GCP Sigma hosting : 104.197.169.18, 104.197.193.23
    AWS Sigma hosting: 44.229.241.60, 54.188.54.135
    company apps
  11.  Return to your PostgreSQL cluster, and click the Properties tab.
    Click Edit on the Network and security settings tab. company apps
  12. Select the VPC Security Group you created. and click Save Changes.
    company apps

Data permissions

If don’t want to use an existing PostgreSQL user, create a new user for Sigma. If you want to leverage write access, create a schema for Sigma and grant all privileges to your user on that schema. Be sure to GRANT USAGE on all schemas, and GRANT SELECT on all tables that you  plan to access in Sigma.

create user sigma\_user password ‘123’ create schema sigma\_write; grant all privileges on schema sigma\_write to sigma\_user; grant usage on schema public to sigma\_user; grant select on all tables in schema public to sigma\_user;

company apps

Connection credentials

In the General Information section of your PostgreSQL cluster, find and copy the Endpoint specification. This is the value of the Host field in Sigma.

company apps

Related resources