Troubleshoot

PostgreSQL

Also applicable to Redshift and RisingWave

info

Following permission errors are likely to happen if either conditions meet:

  1. Your PostgreSQL instance in hosted in the cloud, which means the configured Bytebase database instance user does not have superuser role.
  2. Your database/table has different owners.

ERROR: permission denied to set role xxx (SQLSTATE 42501)

You may encounter this error when executing the schema migration on PostgreSQL databases.

When Bytebase executes schema migration on PostgreSQL databases, it will not use the configured user in the instance to execute. Instead, it uses SET LOCAL ROLE to switch to the targeting PostgreSQL database owner.

Bytebase does this because if you are creating a table, it's likely you want the created table owner to be the database owner specific to the database instead of that shared Bytebase user.

You need to grant the database owner permission to the Bytebase user configured in the PostgreSQL instance.

-- Suppose the Bytebase user is `bytebase`
-- INHERIT FALSE is sufficient for `SET LOCAL ROLE`
GRANT <<DATABASE_OWNER>> TO bytebase WITH INHERIT FALSE

ERROR: must be owner of table xxx (SQLSTATE 42501)

You may encounter this error when executing the schema migration on PostgreSQL databases.

When Bytebase executes schema migration on PostgreSQL databases, it will not use the configured user in the instance to execute. Instead, it uses SET LOCAL ROLE to switch to the targeting PostgreSQL database owner.

Bytebase does this because if you are creating a table, it's likely you want the created table owner to be the database owner specific to the database instead of that shared Bytebase user.

However, for some sophisticated setup where database owner and table owner are different, this causes problem. Because only table owner can alter that table schema, and if the database owner is not a member of the table owner, then the execution will throw the ERROR: must be owner of table xxx error.

One way to workaround this is to grant the table owner permission to the database owner:

GRANT <<TABLE_OWNER>> TO <<DATABASE_OWNER>>

ERROR: permission denied for table xxx (SQLSTATE 42501)

See above.

ERROR: permission denied for table xxx pg_dump: detail: Query was: LOCK TABLE xxx IN ACCESS SHARE MODE

You may encounter this error when executing the schema migration on PostgreSQL databases.

When Bytebase executes schema migration, it will use pg_dump to record the before/after schema snapshot.

This error happens when the user configured in the Bytebase PostgreSQL instance doesn't have the SELECT permission on the table. Let's suppose the Bytebase user configured in the Bytebase PG instance is called bytebase. You should do grant the table owner permission to bytebase:

GRANT SELECT ON TABLE <<TABLE_NAME>> TO bytebase
Edit this page on GitHub

Subscribe toย Newsletter

By subscribing, you agree with Bytebase's Terms of Service and Privacy Policy.