Postgres CDC Permissions are not accurately validated with groups

[edeesis]

Describe the bug

The docs describe the various permissions required for the Postgres user supplied for a CDC source:

For RDS:

GRANT rds_replication TO <username>;`

However, if the user is part of a group that has the rds_replication role granted to it, it should work.

For both self-hosted and RDS:

If the user doesn't have explicit select permissions because of the group owning the table, you get an error:

[2024-10-18 15:31:13] [XX000] ERROR: Failed to execute the statement
[2024-10-18 15:31:13] Caused by these errors (recent errors listed first):
[2024-10-18 15:31:13] 1: gRPC request to meta service failed: Internal errort[2024-10-18 15:31:13] 2: source cannot pass validation
[2024-10-18 15:31:13] 3: INVALID_ARGUMENT: Postgres user must have select privilege on table 'public.test'

Error message/log

2024-10-18T19:31:14.112929978Z  INFO risingwave_connector_node: source_id: 0, is_cdc_source_job: false, is_backfill_table: true thread="Thread-84" class="com.risingwave.connector.source.SourceValidateHandler"
2024-10-18T19:31:14.11303265Z  INFO risingwave_connector_node: table column names: [id, first_name] thread="Thread-84" class="com.risingwave.connector.api.TableSchema"
2024-10-18T19:31:14.143332386Z  INFO risingwave_connector_node: replication slot 'rw_cdc_01af19007cf3477cb3681f00d4369597' already exists, just use it thread="Thread-84" class="com.risingwave.connector.source.common.PostgresValidator"
2024-10-18T19:31:14.152261269Z  INFO risingwave_connector_node: rds memberof: [rds_replication, platform-primary-io-112-group] thread="Thread-84" class="com.risingwave.connector.source.common.PostgresValidator"
2024-10-18T19:31:14.15903862Z  WARN risingwave_connector_node: Source validation failed: io.grpc.StatusRuntimeException: INVALID_ARGUMENT: Postgres user must have select privilege on table 'public.test'
        at io.grpc.Status.asRuntimeException(Status.java:525)
        at com.risingwave.connector.source.common.ValidatorUtils.invalidArgument(ValidatorUtils.java:35)
        at com.risingwave.connector.source.common.PostgresValidator.validateTablePrivileges(PostgresValidator.java:319)
        at com.risingwave.connector.source.common.PostgresValidator.validatePrivileges(PostgresValidator.java:300)
        at com.risingwave.connector.source.common.PostgresValidator.validateUserPrivilege(PostgresValidator.java:129)
        at com.risingwave.connector.source.common.DatabaseValidator.validateAll(DatabaseValidator.java:21)
        at com.risingwave.connector.source.SourceValidateHandler.validateSource(SourceValidateHandler.java:113)
        at com.risingwave.connector.source.JniSourceValidateHandler.validate(JniSourceValidateHandler.java:34)
 thread="Thread-84" class="com.risingwave.connector.source.JniSourceValidateHandler"
2024-10-18T19:31:14.159373847Z ERROR risingwave_meta::rpc::ddl_controller_v2: failed to create streaming job id=2047 error=source cannot pass validation: INVALID_ARGUMENT: Postgres user must have select privilege on table 'public.test'
2024-10-18T19:31:14.177295044Z  WARN risingwave_meta::rpc::ddl_controller_v2: aborted streaming job id=2047

### To Reproduce

Postgres:

```sql
CREATE USER test LOGIN REPLICATION;
CREATE GROUP test_group WITH USER test;

GRANT CREATE ON DATABASE postgres TO test_group;

CREATE TABLE test (id bigint primary key );
ALTER TABLE test OWNER TO test_group;

RisingWave DB:

CREATE SOURCE primary_cdc WITH (
    connector = 'postgres-cdc',
    hostname = 'postgres-0',
    port = '5432',
    username = 'test',
    password = '',
    database.name = 'postgres'
);

CREATE TABLE test (id bigint primary key) FROM primary_cdc TABLE 'public.test';

This gives you an error the error above.
Note that you cannot grant select to the group.

GRANT SELECT ON test TO test_group;

does not work.

The only thing that does work is granting select to the user.

GRANT SELECT ON test TO test;

Expected behavior

I should be able to control access to my tables through ROLEs and GROUPs without having to explicitly GRANT permissions to the users being logged into.

How did you deploy RisingWave?

via Helm to K8s.

Here is the redacted values.yaml file

auth:
  existingSecretName: <risingwave root user secret>
  rootUser: risingwave
compactorComponent:
  resources:
    limits:
      cpu: 2
      memory: 4Gi
computeComponent:
  resources:
    limits:
      cpu: 4
      memory: 8Gi
frontendComponent:
  resources:
    limits:
      cpu: 1
      memory: 2Gi
image:
  registry: docker.risingwave.com
  repository: risingwavelabs/risingwave
  tag: v2.0.1
metaComponent:
  resources:
    limits:
      cpu: 1
      memory: 2Gi
metaStore:
  postgresql:
    authentication:
      existingSecretName: <credentials>
    database: <rising wave metastore database>
    enabled: true
    host: <rds host>
    port: 5432
monitor:
  podMonitor:
    enabled: true
    interval: 20s
serviceAccount:
  annotations:
    eks.amazonaws.com/role-arn: <role arn>
stateStore:
  s3:
    authentication:
      useServiceAccount: true
    bucket: <rising wave bucket>
    enabled: true
    region: us-east-1

The version of RisingWave

PostgreSQL 13.14.0-RisingWave-2.0.1 (0d15632)

Additional context

No response

[github-actions]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean.
Don't worry if you think the issue is still valuable to continue in the future.
It's searchable and can be reopened when it's time. 😄