Error: 'Role Does Not Exist'

Issue

When setting up a PostgreSQL connection for Prisma Postgres using the logical replication update method, setup tests fail with the following error:

role does not exist

Environment

• Connector: PostgreSQL
• Source: Prisma Postgres
• Update method: Logical replication

Resolution

To resolve this issue, switch your connector to the Query-Based update method:

1. In Fivetran, go to your PostgreSQL connection page.
2. Select the Settings tab.
3. Click Edit connection.
4. Click Incremental sync.
5. Under Incremental sync method, select Query-Based.
6. Click Save & Test.

For new connections, select Query-Based as the incremental sync method during setup.

You can also ask Prisma whether they offer direct, non-proxy connection endpoints for replication purposes. For more information, see the Prisma Postgres Direct Connection documentation.

Cause

Prisma Postgres uses a proxy layer for database connections. During logical replication setup, we run PostgreSQL permission checks that use functions such as pg_has_role(). This issue occurs when the Prisma proxy doesn't translate the proxy username to the underlying PostgreSQL role in this context.

For example, a permission check like the following can fail when the proxy username isn't mapped to the underlying role:

SELECT rolname, rolreplication FROM pg_roles WHERE pg_has_role('<proxy_username>', oid, 'member')

Query-Based sync uses standard SQL queries instead of PostgreSQL logical replication, so it avoids these replication-specific permission checks. However, Query-Based sync may require periodic table scans rather than log-based change capture.