I tried running the Snowflake Openflow Connector for PostgreSQL on SPCS and connecting it to RDS for PostgreSQL

AWSTemplateFormatVersion: '2010-09-09'

Parameters:
  ProjectName:
    Type: String
    Default: openflow-pg-nlb

  VpcCidr:
    Type: String
    Default: 10.1.0.0/16

  PublicSubnet1Cidr:
    Type: String
    Default: 10.1.0.0/24

  PublicSubnet2Cidr:
    Type: String
    Default: 10.1.1.0/24

  PrivateSubnet1Cidr:
    Type: String
    Default: 10.1.10.0/24

  PrivateSubnet2Cidr:
    Type: String
    Default: 10.1.11.0/24

  BastionInstanceType:
    Type: String
    Default: t3.micro

  LatestAmiId:
    Type: AWS::SSM::Parameter::Value<AWS::EC2::Image::Id>
    Default: /aws/service/ami-amazon-linux-latest/al2023-ami-kernel-default-x86_64

Resources:
  Vpc:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: !Ref VpcCidr
      EnableDnsSupport: true
      EnableDnsHostnames: true
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-vpc'

  InternetGateway:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-igw'

  IgwAttachment:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      VpcId: !Ref Vpc
      InternetGatewayId: !Ref InternetGateway

  PublicSubnet1:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref Vpc
      CidrBlock: !Ref PublicSubnet1Cidr
      AvailabilityZone: !Select [0, !GetAZs '']
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-public-1'

  PublicSubnet2:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref Vpc
      CidrBlock: !Ref PublicSubnet2Cidr
      AvailabilityZone: !Select [1, !GetAZs '']
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-public-2'

  PrivateSubnet1:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref Vpc
      CidrBlock: !Ref PrivateSubnet1Cidr
      AvailabilityZone: !Select [0, !GetAZs '']
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-private-1'

  PrivateSubnet2:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref Vpc
      CidrBlock: !Ref PrivateSubnet2Cidr
      AvailabilityZone: !Select [1, !GetAZs '']
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-private-2'

  PublicRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref Vpc
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-rt-public'

  DefaultRoute:
    Type: AWS::EC2::Route
    DependsOn: IgwAttachment
    Properties:
      RouteTableId: !Ref PublicRouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref InternetGateway

  PublicSubnet1RtAssoc:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PublicSubnet1
      RouteTableId: !Ref PublicRouteTable

  PublicSubnet2RtAssoc:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PublicSubnet2
      RouteTableId: !Ref PublicRouteTable

  PrivateRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref Vpc
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-rt-private'

  PrivateSubnet1RtAssoc:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PrivateSubnet1
      RouteTableId: !Ref PrivateRouteTable

  PrivateSubnet2RtAssoc:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref PrivateSubnet2
      RouteTableId: !Ref PrivateRouteTable

  NlbSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: internet-facing NLB - inbound 5432 from Snowflake egress IPs (added later).
      VpcId: !Ref Vpc
      SecurityGroupEgress:
        - IpProtocol: -1
          CidrIp: 0.0.0.0/0
          Description: Allow all outbound (forward to RDS target + health checks).
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-nlb-sg'

  RdsSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: RDS PostgreSQL (private) - inbound 5432 from NLB SG and bastion only.
      VpcId: !Ref Vpc
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 5432
          ToPort: 5432
          SourceSecurityGroupId: !Ref NlbSecurityGroup
          Description: PostgreSQL from the internet-facing NLB nodes.
        - IpProtocol: tcp
          FromPort: 5432
          ToPort: 5432
          SourceSecurityGroupId: !Ref BastionSecurityGroup
          Description: PostgreSQL from bastion (psql admin / postgres setup SQL).
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-rds-sg'

  BastionSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Bastion host - outbound only (SSM Session Manager, dnf, psql to RDS).
      VpcId: !Ref Vpc
      SecurityGroupEgress:
        - IpProtocol: -1
          CidrIp: 0.0.0.0/0
          Description: Allow all outbound.
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-bastion-sg'

  LambdaSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: IP-sync Lambda - outbound only (ELB + Logs via interface endpoints).
      VpcId: !Ref Vpc
      SecurityGroupEgress:
        - IpProtocol: -1
          CidrIp: 0.0.0.0/0
          Description: Allow all outbound (HTTPS to interface endpoints, DNS).
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-lambda-sg'

  VpcEndpointSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: VPC interface endpoints - inbound 443 from the Lambda SG.
      VpcId: !Ref Vpc
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 443
          ToPort: 443
          SourceSecurityGroupId: !Ref LambdaSecurityGroup
          Description: HTTPS from the IP-sync Lambda.
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-vpce-sg'

  ElbInterfaceEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      VpcId: !Ref Vpc
      ServiceName: !Sub 'com.amazonaws.${AWS::Region}.elasticloadbalancing'
      VpcEndpointType: Interface
      PrivateDnsEnabled: true
      SubnetIds:
        - !Ref PrivateSubnet1
        - !Ref PrivateSubnet2
      SecurityGroupIds:
        - !Ref VpcEndpointSecurityGroup

  LogsInterfaceEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      VpcId: !Ref Vpc
      ServiceName: !Sub 'com.amazonaws.${AWS::Region}.logs'
      VpcEndpointType: Interface
      PrivateDnsEnabled: true
      SubnetIds:
        - !Ref PrivateSubnet1
        - !Ref PrivateSubnet2
      SecurityGroupIds:
        - !Ref VpcEndpointSecurityGroup

  BastionRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal:
              Service: ec2.amazonaws.com
            Action: sts:AssumeRole
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-bastion-role'

  BastionInstanceProfile:
    Type: AWS::IAM::InstanceProfile
    Properties:
      Roles:
        - !Ref BastionRole

  BastionInstance:
    Type: AWS::EC2::Instance
    Properties:
      InstanceType: !Ref BastionInstanceType
      ImageId: !Ref LatestAmiId
      IamInstanceProfile: !Ref BastionInstanceProfile
      SubnetId: !Ref PublicSubnet1
      SecurityGroupIds:
        - !Ref BastionSecurityGroup
      UserData:
        Fn::Base64: !Sub |
          #!/bin/bash
          # PostgreSQL client (psql) for running postgres/*.sql against RDS.
          dnf install -y postgresql16 || dnf install -y postgresql15
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-bastion'

Outputs:
  VpcId:
    Value: !Ref Vpc
    Export:
      Name: !Sub '${ProjectName}-VpcId'
  PublicSubnet1Id:
    Value: !Ref PublicSubnet1
    Export:
      Name: !Sub '${ProjectName}-PublicSubnet1Id'
  PublicSubnet2Id:
    Value: !Ref PublicSubnet2
    Export:
      Name: !Sub '${ProjectName}-PublicSubnet2Id'
  PrivateSubnet1Id:
    Value: !Ref PrivateSubnet1
    Export:
      Name: !Sub '${ProjectName}-PrivateSubnet1Id'
  PrivateSubnet2Id:
    Value: !Ref PrivateSubnet2
    Export:
      Name: !Sub '${ProjectName}-PrivateSubnet2Id'
  NlbSecurityGroupId:
    Description: Attach Snowflake egress IPs here (aws/scripts/update-snowflake-egress-sg.sh --sg-id <this>).
    Value: !Ref NlbSecurityGroup
    Export:
      Name: !Sub '${ProjectName}-NlbSecurityGroupId'
  RdsSecurityGroupId:
    Value: !Ref RdsSecurityGroup
    Export:
      Name: !Sub '${ProjectName}-RdsSecurityGroupId'
  BastionSecurityGroupId:
    Value: !Ref BastionSecurityGroup
    Export:
      Name: !Sub '${ProjectName}-BastionSecurityGroupId'
  LambdaSecurityGroupId:
    Value: !Ref LambdaSecurityGroup
    Export:
      Name: !Sub '${ProjectName}-LambdaSecurityGroupId'
  BastionInstanceId:
    Description: Connect with `aws ssm start-session --target <id>`.
    Value: !Ref BastionInstance
    Export:
      Name: !Sub '${ProjectName}-BastionInstanceId'

AWSTemplateFormatVersion: '2010-09-09'

Parameters:
  ProjectName:
    Type: String
    Default: openflow-pg-nlb

  EngineVersion:
    Type: String
    Default: '16.14'

  ParameterGroupFamily:
    Type: String
    Default: postgres16

  DBInstanceClass:
    Type: String
    Default: db.t4g.micro

  AllocatedStorage:
    Type: Number
    Default: 20

  DBName:
    Type: String
    Default: appdb

  MasterUsername:
    Type: String
    Default: postgres

Resources:
  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: !Sub '${ProjectName} RDS subnet group (private)'
      SubnetIds:
        - Fn::ImportValue: !Sub '${ProjectName}-PrivateSubnet1Id'
        - Fn::ImportValue: !Sub '${ProjectName}-PrivateSubnet2Id'
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-db-subnet-group'

  DBParameterGroup:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Description: !Sub '${ProjectName} - logical replication enabled'
      Family: !Ref ParameterGroupFamily
      Parameters:
        rds.logical_replication: '1'
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-pg-params'

  DBInstance:
    Type: AWS::RDS::DBInstance
    DeletionPolicy: Delete
    UpdateReplacePolicy: Delete
    Properties:
      DBInstanceIdentifier: !Sub '${ProjectName}-pg'
      Engine: postgres
      EngineVersion: !Ref EngineVersion
      DBInstanceClass: !Ref DBInstanceClass
      AllocatedStorage: !Ref AllocatedStorage
      StorageType: gp3
      DBName: !Ref DBName
      MasterUsername: !Ref MasterUsername
      ManageMasterUserPassword: true   # stores the master password in Secrets Manager
      DBSubnetGroupName: !Ref DBSubnetGroup
      DBParameterGroupName: !Ref DBParameterGroup
      VPCSecurityGroups:
        - Fn::ImportValue: !Sub '${ProjectName}-RdsSecurityGroupId'
      PubliclyAccessible: false   # private; reachable only via the NLB + bastion
      MultiAZ: false
      BackupRetentionPeriod: 1
      DeletionProtection: false
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-pg'

Outputs:
  DBEndpointAddress:
    Description: >-
      RDS endpoint host. Use as the RDS_ENDPOINT env for the IP-sync Lambda (03 stack).
      NOTE: clients connect via the NLB DNS name, not this host. Its TLS cert CN is this
      endpoint, so connect with sslmode=require (verify-full against the NLB name fails).
    Value: !GetAtt DBInstance.Endpoint.Address
    Export:
      Name: !Sub '${ProjectName}-DBEndpointAddress'
  DBEndpointPort:
    Value: !GetAtt DBInstance.Endpoint.Port
    Export:
      Name: !Sub '${ProjectName}-DBEndpointPort'
  DBName:
    Value: !Ref DBName
  MasterUserSecretArn:
    Description: Secrets Manager ARN holding the master username/password.
    Value: !GetAtt DBInstance.MasterUserSecret.SecretArn
    Export:
      Name: !Sub '${ProjectName}-MasterUserSecretArn'

AWSTemplateFormatVersion: '2010-09-09'

Parameters:
  ProjectName:
    Type: String
    Default: openflow-pg-nlb

  SyncScheduleExpression:
    Type: String
    Default: rate(1 minute)

  LogRetentionDays:
    Type: Number
    Default: 14

Resources:
  Nlb:
    Type: AWS::ElasticLoadBalancingV2::LoadBalancer
    Properties:
      Name: !Sub '${ProjectName}-nlb'
      Type: network
      Scheme: internet-facing
      IpAddressType: ipv4
      SecurityGroups:
        - Fn::ImportValue: !Sub '${ProjectName}-NlbSecurityGroupId'
      Subnets:
        - Fn::ImportValue: !Sub '${ProjectName}-PublicSubnet1Id'
        - Fn::ImportValue: !Sub '${ProjectName}-PublicSubnet2Id'
      LoadBalancerAttributes:
        - Key: load_balancing.cross_zone.enabled
          Value: 'true'
        - Key: deletion_protection.enabled
          Value: 'false'
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-nlb'

  TargetGroup:
    Type: AWS::ElasticLoadBalancingV2::TargetGroup
    Properties:
      Name: !Sub '${ProjectName}-tg'
      TargetType: ip          # The private IP of RDS is set by the IP-sync Lambda function.
      Protocol: TCP
      Port: 5432
      VpcId:
        Fn::ImportValue: !Sub '${ProjectName}-VpcId'
      HealthCheckProtocol: TCP
      HealthCheckPort: traffic-port
      HealthCheckIntervalSeconds: 30
      HealthyThresholdCount: 3
      UnhealthyThresholdCount: 3
      TargetGroupAttributes:
        - Key: preserve_client_ip.enabled
          Value: 'false'
        - Key: deregistration_delay.timeout_seconds
          Value: '30'
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-tg'

  Listener:
    Type: AWS::ElasticLoadBalancingV2::Listener
    Properties:
      LoadBalancerArn: !Ref Nlb
      Protocol: TCP
      Port: 5432
      DefaultActions:
        - Type: forward
          TargetGroupArn: !Ref TargetGroup

  IpSyncLogGroup:
    Type: AWS::Logs::LogGroup
    Properties:
      LogGroupName: !Sub '/aws/lambda/${ProjectName}-nlb-ipsync'
      RetentionInDays: !Ref LogRetentionDays

  IpSyncRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal:
              Service: lambda.amazonaws.com
            Action: sts:AssumeRole
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole
      Policies:
        - PolicyName: nlb-target-sync
          PolicyDocument:
            Version: '2012-10-17'
            Statement:
              - Effect: Allow
                Action:
                  - elasticloadbalancing:RegisterTargets
                  - elasticloadbalancing:DeregisterTargets
                Resource: !Ref TargetGroup
              - Effect: Allow
                Action:
                  - elasticloadbalancing:DescribeTargetHealth
                  - elasticloadbalancing:DescribeTargetGroups
                Resource: '*'
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-ipsync-role'

  IpSyncFunction:
    Type: AWS::Lambda::Function
    DependsOn: IpSyncLogGroup
    Properties:
      FunctionName: !Sub '${ProjectName}-nlb-ipsync'
      Runtime: python3.12
      Handler: index.handler
      Role: !GetAtt IpSyncRole.Arn
      Timeout: 60
      MemorySize: 128
      ReservedConcurrentExecutions: 1
      VpcConfig:
        SubnetIds:
          - Fn::ImportValue: !Sub '${ProjectName}-PrivateSubnet1Id'
          - Fn::ImportValue: !Sub '${ProjectName}-PrivateSubnet2Id'
        SecurityGroupIds:
          - Fn::ImportValue: !Sub '${ProjectName}-LambdaSecurityGroupId'
      Environment:
        Variables:
          RDS_ENDPOINT:
            Fn::ImportValue: !Sub '${ProjectName}-DBEndpointAddress'
          TARGET_GROUP_ARN: !Ref TargetGroup
          PORT: '5432'
      Code:
        ZipFile: |
          import os
          import socket
          import boto3

          TG_ARN = os.environ["TARGET_GROUP_ARN"]
          HOST = os.environ["RDS_ENDPOINT"]
          PORT = int(os.environ.get("PORT", "5432"))

          elbv2 = boto3.client("elbv2")

          def resolve_ipv4(host):
              infos = socket.getaddrinfo(host, PORT, family=socket.AF_INET,
                                         type=socket.SOCK_STREAM)
              return sorted({info[4][0] for info in infos})

          def current_targets():
              resp = elbv2.describe_target_health(TargetGroupArn=TG_ARN)
              return sorted({d["Target"]["Id"]
                             for d in resp.get("TargetHealthDescriptions", [])})

          def handler(event, context):
              desired = resolve_ipv4(HOST)
              existing = current_targets()
              to_add = [ip for ip in desired if ip not in existing]
              to_remove = [ip for ip in existing if ip not in desired]

              if to_add:
                  try:
                      elbv2.register_targets(
                          TargetGroupArn=TG_ARN,
                          Targets=[{"Id": ip, "Port": PORT} for ip in to_add],
                      )
                      print("registered: %s" % to_add)
                  except Exception as e:
                      print("register failed for %s: %s" % (to_add, e))

              if to_remove and desired:
                  try:
                      elbv2.deregister_targets(
                          TargetGroupArn=TG_ARN,
                          Targets=[{"Id": ip, "Port": PORT} for ip in to_remove],
                      )
                      print("deregistered: %s" % to_remove)
                  except Exception as e:
                      print("deregister failed for %s: %s" % (to_remove, e))

              result = {"host": HOST, "desired": desired,
                        "added": to_add, "removed": to_remove}
              print(result)
              return result
      Tags:
        - Key: Name
          Value: !Sub '${ProjectName}-nlb-ipsync'

  SyncScheduleRule:
    Type: AWS::Events::Rule
    Properties:
      Name: !Sub '${ProjectName}-nlb-ipsync-schedule'
      ScheduleExpression: !Ref SyncScheduleExpression
      State: ENABLED
      Targets:
        - Id: ipsync
          Arn: !GetAtt IpSyncFunction.Arn

  SyncSchedulePermission:
    Type: AWS::Lambda::Permission
    Properties:
      FunctionName: !Ref IpSyncFunction
      Action: lambda:InvokeFunction
      Principal: events.amazonaws.com
      SourceArn: !GetAtt SyncScheduleRule.Arn

Outputs:
  NlbDnsName:
    Value: !GetAtt Nlb.DNSName
    Export:
      Name: !Sub '${ProjectName}-NlbDnsName'
  TargetGroupArn:
    Value: !Ref TargetGroup
    Export:
      Name: !Sub '${ProjectName}-TargetGroupArn'
  IpSyncFunctionName:
    Value: !Ref IpSyncFunction

CREATE TABLE IF NOT EXISTS public.customers (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT        NOT NULL,
    email       TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO public.customers (name, email) VALUES
    ('Alice Tanaka',  'alice@example.com'),
    ('Bob Suzuki',    'bob@example.com'),
    ('Carol Yamada',  'carol@example.com');

CREATE TABLE IF NOT EXISTS public.orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  BIGINT      NOT NULL REFERENCES public.customers(id),
    amount       NUMERIC(12,2) NOT NULL,
    status       TEXT        NOT NULL DEFAULT 'NEW',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO public.orders (customer_id, amount, status) VALUES
    (1, 1200.00, 'NEW'),
    (1,  450.50, 'PAID'),
    (2, 9800.00, 'NEW');

-- Row count check
SELECT 'customers' AS table, count(*) FROM public.customers
UNION ALL
SELECT 'orders' AS table, count(*) FROM public.orders;

-- Check logical replication
SHOW wal_level;
SHOW max_replication_slots;
SHOW max_wal_senders;

-- PUBLICATION
CREATE PUBLICATION snowflake_pub WITH (publish_via_partition_root = true);
ALTER PUBLICATION snowflake_pub ADD TABLE public.customers, public.orders;

-- user for openflow connector
CREATE ROLE openflow_repl WITH LOGIN PASSWORD '<CHANGE_ME_STRONG_PASSWORD>';
GRANT rds_replication TO openflow_repl;

-- Grant for Snapshot / CDC
GRANT CONNECT ON DATABASE public TO openflow_repl;
GRANT USAGE ON SCHEMA public TO openflow_repl;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO openflow_repl;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO openflow_repl;

-- Check
SELECT pubname, schemaname, tablename
FROM pg_publication_tables
WHERE pubname = 'snowflake_pub'
ORDER BY tablename;

USE ROLE ACCOUNTADMIN;

-- Create Openflow Admin Role
CREATE ROLE IF NOT EXISTS OPENFLOW_ADMIN;

-- Grant for Openflow
GRANT CREATE OPENFLOW DATA PLANE INTEGRATION ON ACCOUNT TO ROLE OPENFLOW_ADMIN;
GRANT CREATE OPENFLOW RUNTIME INTEGRATION   ON ACCOUNT TO ROLE OPENFLOW_ADMIN;
GRANT CREATE COMPUTE POOL                   ON ACCOUNT TO ROLE OPENFLOW_ADMIN;

-- Create a warehouse to be used for Openflow execution
CREATE WAREHOUSE IF NOT EXISTS OPENFLOW_WH
  WAREHOUSE_SIZE      = 'XSMALL'
  AUTO_SUSPEND        = 60
  AUTO_RESUME         = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- Grant to your working user
GRANT ROLE OPENFLOW_ADMIN TO USER <YOUR_USER>;

-- OAuth login to the runtime/connector uses the working user's DEFAULT_ROLE.
-- Even if the working user's role is changed to `OPENFLOW_ADMIN`,
-- if DEFAULT_ROLE is ACCOUNTADMIN / ORGADMIN / GLOBALORGADMIN / SECURITYADMIN,
-- access to Openflow will be denied
-- (the error `The role requested has been explicitly blocked ...` is returned)
-- Therefore, change DEFAULT_ROLE to the non-privileged OPENFLOW_ADMIN and set secondary roles to ALL
-- After the change, sign out once and sign back in before operating Openflow
ALTER USER <your_user> SET DEFAULT_ROLE = OPENFLOW_ADMIN;
ALTER USER <your_user> SET DEFAULT_SECONDARY_ROLES = ('ALL');

-- Role associated with the Openflow runtime
CREATE ROLE IF NOT EXISTS OPENFLOW_RUNTIME_ROLE_PGNLB;
-- Grant warehouse usage permissions
GRANT USAGE, OPERATE ON WAREHOUSE OPENFLOW_WH TO ROLE OPENFLOW_RUNTIME_ROLE_PGNLB;

-- Grant permission to Openflow admin role to assign this runtime role when creating the runtime
GRANT ROLE OPENFLOW_RUNTIME_ROLE_PGNLB TO ROLE OPENFLOW_ADMIN;

-- Network connection settings
CREATE DATABASE IF NOT EXISTS OPENFLOW_DB;
CREATE SCHEMA   IF NOT EXISTS OPENFLOW_DB.NETWORKING;

CREATE OR REPLACE NETWORK RULE OPENFLOW_DB.NETWORKING.NLB_PG_EGRESS
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('<NLB_DNS>:5432');

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION OPENFLOW_PG_NLB_EAI
  ALLOWED_NETWORK_RULES = (OPENFLOW_DB.NETWORKING.NLB_PG_EGRESS)
  ENABLED = TRUE;

GRANT USAGE ON INTEGRATION OPENFLOW_PG_NLB_EAI TO ROLE OPENFLOW_RUNTIME_ROLE_PGNLB;
GRANT USAGE ON INTEGRATION OPENFLOW_PG_NLB_EAI TO ROLE OPENFLOW_ADMIN;

-- Create the destination database
CREATE DATABASE IF NOT EXISTS PG_OPENFLOW_NLB_DEST;

GRANT USAGE         ON DATABASE PG_OPENFLOW_NLB_DEST TO ROLE OPENFLOW_RUNTIME_ROLE_PGNLB;
GRANT CREATE SCHEMA ON DATABASE PG_OPENFLOW_NLB_DEST TO ROLE OPENFLOW_RUNTIME_ROLE_PGNLB;
GRANT USAGE ON DATABASE PG_OPENFLOW_NLB_DEST TO ROLE OPENFLOW_ADMIN;