If you’re encountering issues with Stitch displaying tables in the Tables to Replicate tab for a database integration, the root cause may the number of tables the authorizing database user has access to in the source database.

Symptoms

  • A blank Tables to Replicate tab with an Unable to load tables error

  • An extraction error similar to the following:

    main - INFO Exit status is: Discovery succeeded. Tap failed with code -9. Target succeeded.

Cause

The first phase in the replication process is called Extraction. The start of every extraction is called discovery, and at this time, Stitch detects the tables and columns available in the source. These are the same tables and columns that the Stitch database user - or the database user in the integration’s Integration Details page - has access to.

If the authorizing database user has access to a large number of databases and tables, discovery may take some time. To ensure discovery can complete in a timely manner, we recommend limiting the database user’s access only to the tables you want to replicate.

Refer to the Basic concepts and system overview guide for more info about Stitch’s replication process.

Solution

Limit the authorizing database user’s access only to the tables you want to replicate:

Step 1: Identify the database user

To see which database user Stitch is using to connect to the database:

  1. Click into the integration from the Stitch Dashboard.
  2. Click the Settings tab.
  3. Locate the User or Username field.

The name of the database user in this field will be used to complete the remaining steps.

Step 2: Revoke the database user's current access

Next, revoke the database user’s current access. This will remove the overly permissive access the user currently has in preparation for the next step.

  1. Log into your database as a user with the ability to revoke privileges.
  2. Locate your database in the table below.
  3. Run the appropriate command, replacing <schema_name> and <stitch_username> with the name of the schema and database user from Step 1, respectively:
Microsoft SQL Server REVOKE SELECT FROM <stitch_username> GO
MySQL REVOKE SELECT ON *.* TO '<stitch_username>';
PostgreSQL REVOKE SELECT ON ALL TABLES IN SCHEMA <schema_name> FROM <stitch_username>;
Oracle REVOKE SELECT ANY TABLE FROM <stitch_username>

Note: These commands will work for any database integration backed by one of the databases mentioned above. For example: The command for MySQL will also work on Amazon RDS MySQL, Amazon RDS Aurora MySQL, MariaDB, etc.

Step 3: Grant new table-level privileges

To limit this database user’s table access:

  1. Log into your database as a user with the ability to grant privileges.
  2. Locate your database in the table below.
  3. Run the appropriate command on every table you want to replicate, replacing <database_name>, <schema_name>, <table_name>, and <stitch_username> with the name of the database, schema, table, and database user from Step 1, respectively:
Microsoft SQL Server GRANT SELECT ON <schema_name>.<table_name> TO <stitch_username>; GO
MySQL GRANT SELECT ON '<database_name>'.'<table_name>' to '<stitch_username>'@'localhost';
PostgreSQL GRANT SELECT ON <schema_name>.<table_name> TO <stitch_username>;
Oracle GRANT SELECT ON <schema_name>.<table_name> to <stitch_username>

Note: These commands will work for any database integration backed by one of the databases mentioned above. For example: The command for MySQL will also work on Amazon RDS MySQL, Amazon RDS Aurora MySQL, MariaDB, etc.

Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.