openapi: 3.0.3
info:
  title: SQL Database Authorizer API
  version: 1.0.0
  description: This Authorizer dynamically modifies SQL queries according to PlainID's policy-based authorization calculations. This process enhances data integrity and security by guaranteeing that only authorized queries are executed, safeguarding sensitive information and ensuring compliance with Access Control Policies.
  contact:
    name: PlainID
servers:
  - url: https://authz-sql-pdp-modifier.local.platform.test
tags:
  - name: SQL Database Authorizer
    description: API for modifying SQL queries based on authorization policies

paths:
  /resql:
    post:
      summary: Modify SQL query based on authorization policies
      description: This endpoint modifies SQL queries to enforce authorization policies, ensuring data integrity and compliance.
      operationId: resqlRequest
      tags:
        - SQL Database Authorizer
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              required:
                - sql
                - clientID
              properties:
                sql:
                  type: string
                  description: SQL string to enforce.
                entityId:
                  type: string
                  description: The ID of the entity.
                entityTypeId:
                  type: string
                  description: Your entity type ID (Identity Template).
                user:
                  type: string
                  description: User identification string.
                clientID:
                  type: string
                  description: Your client ID for the PlainID Scope.
                clientSecret:
                  type: string
                  description: Your client secret the PlainID Scope.
                jwtToken:
                  type: string
                  description: JWT token for authentication (optional).
                flags:
                  type: object
                  description: JSON object with modification flags.
                  properties:
                    emptyRLSTreatAsDenied:
                      type: boolean
                      description: Treat empty row-level security as denied.
                    emptyCLSTreatAsPermitted:
                      type: boolean
                      description: Treat empty column-level security as permitted.
                    ignoreRuntimeCLSResponse:
                      type: boolean
                      description: Ignore runtime column-level security response.
                    expandStarColumn:
                      type: boolean
                      description: Expand star (*) columns into specific fields.
                    oppositeColumnFilteringBehavior:
                      type: boolean
                      description: Use opposite column filtering behavior.
                    policiesJoinOperation:
                      type: string
                      enum: [OR, AND]
                      description: Join operation for policies.
                    runtimeCLSAsMasked:
                      type: boolean
                      description: Treat runtime column-level security as masked.
                    columnsResourceType:
                      type: string
                      description: Resource type for columns.
                entityAttributes:
                  type: object
                  additionalProperties:
                    type: array
                    items:
                      type: object
                  description: Additional entity attributes.
                contextData:
                  type: object
                  additionalProperties:
                    type: array
                    items:
                      type: object
                  description: Context data for the request.
                environment:
                  type: object
                  additionalProperties:
                    type: array
                    items:
                      type: object
                  description: Environment data for the request.
                
            examples:
              simpleQuery:
                value:
                  sql: "SELECT e1.first_name,e1.last_name,e1.department_id from employees as e1 where e1.first_name = 'bardak'"
                  user: "Jordan"
                  clientID: "PBMZCUX4GCQ6BQH1CFQ7"
                  clientSecret: "B0JCgbzMbNIls3NjKy4P12CTv0jvL2JAzbF5T8dg"
                  entityAttributes:
                    DemoGroup:
                      - {}
                  flags:
                    emptyRLSTreatAsDenied: false
                    emptyCLSTreatAsPermitted: false
                    ignoreRuntimeCLSResponse: false
                    expandStarColumn: true
                    oppositeColumnFilteringBehavior: false
                    policiesJoinOperation: "OR"
                    runtimeCLSAsMasked: true
                    columnsResourceType: ""
              complexQuery:
                value:
                  sql: "WITH ranked_salaries AS( SELECT employee_id, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS salary_rank FROM salaries) SELECT e.id, e.first_name, e.last_name, rs.amount FROM employees e, ranked_salaries rs WHERE e.id = rs.employee_id AND rs.salary_rank <= 5 ORDER BY rs.amount DESC;"
                  entityTypeId: "Main"
                  clientID: "PBMZCUX4GCQ6BQH1CFQ7"
                  clientSecret: "B0JCgbzMbNIls3NjKy4P12CTv0jvL2JAzbF5T8dg"
                  flags:
                    emptyRLSTreatAsDenied: false
                    emptyCLSTreatAsPermitted: false
                    ignoreRuntimeCLSResponse: false
                    expandStarColumn: true
                    oppositeColumnFilteringBehavior: true
                    policiesJoinOperation: "OR"
                    runtimeCLSAsMasked: true
                    columnsResourceType: ""
      responses:
        '200':
          description: Successful response
          content:
            application/json:
              schema:
                type: object
                properties:
                  sql:
                    type: string
                    description: Final SQL string after modifications.
                  wasModified:
                    type: boolean
                    description: Boolean indicating if the original SQL query was modified.
                  error:
                    type: string
                    description: Error messages that were generated during SQL query enforcement. Field is empty if no errors occurred.
              examples:
                simpleQueryResponse:
                  value:
                    sql: "SELECT e1.first_name, e1.last_name, NULL AS department_id FROM employees e1 WHERE e1.first_name = 'bardak' AND e1.location = 'US'"
                    wasModified: true
                    error: ""
                complexQueryResponse:
                  value:
                    sql: "WITH ranked_salaries AS (SELECT employee_id, amount, row_number() OVER (ORDER BY amount DESC) AS salary_rank FROM salaries WHERE amount >= 500) SELECT e.id, '****' AS first_name, ice(e.last_name) AS last_name, rs.amount FROM employees e, ranked_salaries rs WHERE (e.id = rs.employee_id AND rs.salary_rank <= 5) AND e.location = 'US' ORDER BY rs.amount DESC"
                    wasModified: true
                    error: ""
                failedQueryResponse:
                  value:
                    sql: ""
                    wasModified: false
                    error: "Error processing query: Error runtime HTTP status: 424, msg: Read timed out, agentName: internal_asset"