dolthub--doltgresql/integration-tests/go-sql-server-driver/tests/sql-server-cluster-users-and-grants.yaml

parallel: true
tests:
- name: users and grants replicate
  skip: "cluster replication / MySQL grants not yet supported in Doltgres"
  multi_repos:
  - name: server1
    with_files:
    - name: server.yaml
      contents: |
        log_level: trace
        cluster:
          standby_remotes:
          - name: standby
            remote_url_template: http://localhost:{{get_port "server2_cluster"}}/{database}
          bootstrap_role: primary
          bootstrap_epoch: 1
          remotesapi:
            port: {{get_port "server1_cluster"}}
    server:
      args: ["--config", "server.yaml"]
      dynamic_port: server1
  - name: server2
    with_files:
    - name: server.yaml
      contents: |
        log_level: trace
        cluster:
          standby_remotes:
          - name: standby
            remote_url_template: http://localhost:{{get_port "server1_cluster"}}/{database}
          bootstrap_role: standby
          bootstrap_epoch: 1
          remotesapi:
            port: {{get_port "server2_cluster"}}
    server:
      args: ["--config", "server.yaml"]
      dynamic_port: server2
  connections:
  - on: server1
    queries:
    - exec: "SET dolt_cluster_ack_writes_timeout_secs = 10"
    - exec: 'create database repo1'
    - exec: "use repo1"
    - exec: 'create table vals (i int primary key)'
    - exec: 'insert into vals values (0),(1),(2),(3),(4)'
    - exec: 'create user aaron with password ''aaronspassword'''
    - exec: 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO aaron'
    - exec: 'insert into vals values (5),(6),(7),(8),(9)'
  - on: server1
    user: 'aaron'
    password: 'aaronspassword'
    queries:
    - exec: "use repo1"
    - exec: 'insert into vals values (10),(11),(12),(13),(14)'
  - on: server2
    user: 'aaron'
    password: 'aaronspassword'
    queries:
    - exec: "use repo1"
    - query: 'select count(*) as count from vals'
      result:
        columns: ["count"]
        rows: [["15"]]
- name: branch control replicates
  skip: "cluster replication / MySQL grants not yet supported in Doltgres"
  multi_repos:
  - name: server1
    with_files:
    - name: server.yaml
      contents: |
        log_level: trace
        cluster:
          standby_remotes:
          - name: standby
            remote_url_template: http://localhost:{{get_port "server2_cluster"}}/{database}
          bootstrap_role: primary
          bootstrap_epoch: 1
          remotesapi:
            port: {{get_port "server1_cluster"}}
    server:
      args: ["--config", "server.yaml"]
      dynamic_port: server1
  - name: server2
    with_files:
    - name: server.yaml
      contents: |
        log_level: trace
        cluster:
          standby_remotes:
          - name: standby
            remote_url_template: http://localhost:{{get_port "server1_cluster"}}/{database}
          bootstrap_role: standby
          bootstrap_epoch: 1
          remotesapi:
            port: {{get_port "server2_cluster"}}
    server:
      args: ["--config", "server.yaml"]
      dynamic_port: server2
  connections:
  - on: server1
    queries:
    - exec: "SET dolt_cluster_ack_writes_timeout_secs = 10"
    - exec: 'create database repo1'
    - exec: "use repo1"
    - exec: 'create table vals (i int primary key)'
    - exec: 'insert into vals values (0),(1),(2),(3),(4)'
    - exec: 'create user aaron with password ''aaronspassword'''
    - exec: 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO aaron'
    - exec: 'create user brian with password ''brianpassword'''
    - exec: 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO brian'
    - exec: 'delete from dolt_branch_control'
    - exec: 'insert into dolt_branch_control values (''repo1'', ''main'', ''aaron'', ''%'', ''admin'')'
  - on: server1
    user: 'aaron'
    password: 'aaronspassword'
    queries:
    - exec: "use repo1"
    - exec: 'insert into vals values (5),(6),(7),(8),(9)'
    - exec: 'insert into vals values (10),(11),(12),(13),(14)'
# Assert brian cannot write to `main` on server1
  - on: server1
    user: 'brian'
    password: 'brianpassword'
    queries:
    - exec: "use repo1"
    - exec: 'insert into vals values (15),(16),(17),(18),(19)'
      error_match: 'does not have the correct permissions on branch `main`'
# Failover to `server2` so we can assert brian is restricted there as well.
  - on: server1
    queries:
    - exec: "use repo1"
    - exec: "select dolt_assume_cluster_role('standby', 2)"
  - on: server2
    queries:
    - exec: "use repo1"
    - exec: "select dolt_assume_cluster_role('primary', 2)"
# Assert aaron can write to main on server2 and brian cannot.
  - on: server2
    user: 'aaron'
    password: 'aaronspassword'
    queries:
    - exec: "use repo1"
    - query: 'select count(*) as count from vals'
      result:
        columns: ["count"]
        rows: [["15"]]
    - exec: 'insert into vals values (20),(21),(22),(23),(24)'
  - on: server2
    user: 'brian'
    password: 'brianpassword'
    queries:
    - exec: "use repo1"
    - query: 'select count(*) as count from vals'
      result:
        columns: ["count"]
        rows: [["20"]]
    - exec: 'insert into vals values (25),(26),(27),(28),(29)'
      error_match: 'does not have the correct permissions on branch `main`'
# Now we are going to grant brian permission, and fail back over to server1,
# and ensure brian can write to the branch there.
  - on: server2
    user: 'aaron'
    password: 'aaronspassword'
    queries:
    - exec: "use repo1"
    - exec: 'insert into dolt_branch_control values (''repo1'', ''main'', ''brian'', ''%'', ''write'')'
    - exec: 'insert into vals values (30)'
  - on: server2
    queries:
    - exec: "select dolt_assume_cluster_role('standby', 3)"
  - on: server1
    queries:
    - exec: "use repo1"
    - exec: "select dolt_assume_cluster_role('primary', 3)"
# Here brian should be allowed to write now.
  - on: server1
    user: 'brian'
    password: 'brianpassword'
    queries:
    - exec: "use repo1"
    - exec: 'insert into vals values (31)'
- name: branch control block on write replication
  skip: "cluster replication / MySQL grants not yet supported in Doltgres"
  multi_repos:
  - name: server1
    with_files:
    - name: server.yaml
      contents: |
        log_level: trace
        cluster:
          standby_remotes:
          - name: standby
            remote_url_template: http://localhost:{{get_port "server2_cluster"}}/{database}
          bootstrap_role: primary
          bootstrap_epoch: 1
          remotesapi:
            port: {{get_port "server1_cluster"}}
    server:
      args: ["--config", "server.yaml"]
      dynamic_port: server1
  - name: server2
    with_files:
    - name: server.yaml
      contents: |
        log_level: trace
        cluster:
          standby_remotes:
          - name: standby
            remote_url_template: http://localhost:{{get_port "server1_cluster"}}/{database}
          bootstrap_role: standby
          bootstrap_epoch: 1
          remotesapi:
            port: {{get_port "server2_cluster"}}
    - name: nocluster.yaml
      contents: |
        log_level: trace
    server:
      args: ["--config", "nocluster.yaml"]
      dynamic_port: server2
  connections:
  - on: server1
    queries:
    - exec: 'create user aaron with password ''aaronspassword'''
    - exec: 'create database repo1'
    - exec: 'use repo1'
    - exec: "SET dolt_cluster_ack_writes_timeout_secs = 2"
    - exec: 'delete from dolt_branch_control'
    - query: 'show warnings'
      result:
        columns: ["Level", "Code", "Message"]
        rows: [["Warning", "3024", "Timed out replication of commit to 1 out of 1 replicas."]]
    - exec: 'insert into dolt_branch_control values (''repo1'', ''main'', ''aaron'', ''%'', ''admin'')'
  - on: server2
    restart_server:
      args: ["--config", "server.yaml"]
  - on: server1
    restart_server: {}
  - on: server1
    queries:
    - exec: "SET dolt_cluster_ack_writes_timeout_secs = 10"
    - exec: 'use repo1'
    - exec: 'delete from dolt_branch_control'
    - query: 'show warnings'
      result:
        columns: ["Level", "Code", "Message"]
        rows: []
  - on: server2
    queries:
    - query: 'show warnings'
      result:
        columns: ["Level", "Code", "Message"]
        rows: []
- name: users and grants block on write replication
  skip: "cluster replication / MySQL grants not yet supported in Doltgres"
  multi_repos:
  - name: server1
    with_files:
    - name: server.yaml
      contents: |
        log_level: trace
        cluster:
          standby_remotes:
          - name: standby
            remote_url_template: http://localhost:{{get_port "server2_cluster"}}/{database}
          bootstrap_role: primary
          bootstrap_epoch: 1
          remotesapi:
            port: {{get_port "server1_cluster"}}
    server:
      args: ["--config", "server.yaml"]
      dynamic_port: server1
  - name: server2
    with_files:
    - name: server.yaml
      contents: |
        log_level: trace
        cluster:
          standby_remotes:
          - name: standby
            remote_url_template: http://localhost:{{get_port "server1_cluster"}}/{database}
          bootstrap_role: standby
          bootstrap_epoch: 1
          remotesapi:
            port: {{get_port "server2_cluster"}}
    - name: nocluster.yaml
      contents: |
        log_level: trace
    server:
      args: ["--config", "nocluster.yaml"]
      dynamic_port: server2
  connections:
  - on: server1
    queries:
    - exec: "SET dolt_cluster_ack_writes_timeout_secs = 2"
    - exec: 'create user aaron with password ''aaronspassword'''
    - query: 'show warnings'
      result:
        columns: ["Level", "Code", "Message"]
        rows: [["Warning", "3024", "Timed out replication of commit to 1 out of 1 replicas."]]
  - on: server2
    restart_server:
      args: ["--config", "server.yaml"]
  - on: server1
    restart_server: {}
  - on: server1
    queries:
    # Because we run in parallel, do not be too agressive about the timeout...
    - exec: "SET dolt_cluster_ack_writes_timeout_secs = 10"
    - exec: 'create user brian with password ''brianspassword'''
    - query: 'show warnings'
      result:
        columns: ["Level", "Code", "Message"]
        rows: []
  - on: server2
    user: 'brian'
    password: 'brianspassword'
    queries:
    - query: "show warnings"
      result:
        columns: ["Level", "Code", "Message"]
        rows: []
- name: users and grants and branch control replicate to multiple standbys
  skip: "cluster replication / MySQL grants not yet supported in Doltgres"
  multi_repos:
  - name: server1
    with_files:
    - name: server.yaml
      contents: |
        log_level: trace
        cluster:
          standby_remotes:
          - name: standby1
            remote_url_template: http://localhost:{{get_port "server2_cluster"}}/{database}
          - name: standby2
            remote_url_template: http://localhost:{{get_port "server3_cluster"}}/{database}
          bootstrap_role: primary
          bootstrap_epoch: 1
          remotesapi:
            port: {{get_port "server1_cluster"}}
    server:
      args: ["--config", "server.yaml"]
      dynamic_port: server1
  - name: server2
    with_files:
    - name: server.yaml
      contents: |
        log_level: trace
        cluster:
          standby_remotes:
          - name: standby1
            remote_url_template: http://localhost:{{get_port "server1_cluster"}}/{database}
          - name: standby2
            remote_url_template: http://localhost:{{get_port "server3_cluster"}}/{database}
          bootstrap_role: standby
          bootstrap_epoch: 1
          remotesapi:
            port: {{get_port "server2_cluster"}}
    server:
      args: ["--config", "server.yaml"]
      dynamic_port: server2
  - name: server3
    with_files:
    - name: server.yaml
      contents: |
        log_level: trace
        cluster:
          standby_remotes:
          - name: standby1
            remote_url_template: http://localhost:{{get_port "server1_cluster"}}/{database}
          - name: standby2
            remote_url_template: http://localhost:{{get_port "server2_cluster"}}/{database}
          bootstrap_role: standby
          bootstrap_epoch: 1
          remotesapi:
            port: {{get_port "server3_cluster"}}
    server:
      args: ["--config", "server.yaml"]
      dynamic_port: server3
  connections:
  - on: server1
    queries:
    - exec: "SET dolt_cluster_ack_writes_timeout_secs = 10"
    - exec: 'create database repo1'
    - exec: "use repo1"
    - exec: 'create table vals (i int primary key)'
    - exec: 'insert into vals values (0),(1),(2),(3),(4)'
    - exec: 'create user aaron with password ''aaronspassword'''
    - exec: 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO aaron'
    - exec: 'create user brian with password ''brianpassword'''
    - exec: 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO brian'
    - exec: 'delete from dolt_branch_control'
    - exec: 'insert into dolt_branch_control values (''repo1'', ''main'', ''aaron'', ''%'', ''admin'')'
  - on: server2
    user: 'aaron'
    password: 'aaronspassword'
    queries:
    - exec: "use repo1"
    - query: 'select count(*) as count from vals'
      result:
        columns: ["count"]
        rows: [["5"]]
    - query: 'select count(*) as count from dolt_branch_control'
      result:
        columns: ["count"]
        rows: [["1"]]
  - on: server3
    user: 'aaron'
    password: 'aaronspassword'
    queries:
    - exec: "use repo1"
    - query: 'select count(*) as count from vals'
      result:
        columns: ["count"]
        rows: [["5"]]
    - query: 'select count(*) as count from dolt_branch_control'
      result:
        columns: ["count"]
        rows: [["1"]]