Creating a Python3 Proxy Between PostgreSQL and the Dynamics 365 Web UI

Question

After several days of some progress, I agree that I lack the knowledge or skill level to collect all these things and complete this project. Thus, I urge and thank everyone who can help me with this.

Technologies

• CentOS 7.5
• Python 3.6.0
• Django 1.10.5
• PostreSQL 9.2
• Microsoft CRM Dynamics 365, which has the latest customer data, should use the web API: https://msdn.microsoft.com/en-us/library/gg334767.aspx

Question

• CRM has the most recent client data in it and wants to bring it to PostgreSQL for use for numerous things.
• Want to use www_fdw, since it is the only external data wrapper I've seen for PostgreSQL than it can use the web API: https://github.com/cyga/www_fdw/wiki
• Dynamics API uses OAuth2 and www_fdwdoes not support any type of authentication natively
• A conversation with a developer www_fdwwho recommended making a proxy server to handle OAuth2 authentication using Microsoft
• PostgreSQL www_fdwwill talk with a proxy server, which, in turn, will send authentication to Microsoft, culminating in the possibility of using the Web API as a foreign table so that it is processed like any other table.

Three parts and what has been tried so far

= www_fdw + - + OAuth2

• www_fdw: : https://github.com/cyga/www_fdw/wiki/Examples

  DROP EXTENSION IF EXISTS www_fdw CASCADE;
  CREATE EXTENSION www_fdw;
  CREATE SERVER crm FOREIGN DATA WRAPPER www_fdw OPTIONS
      (uri 'http://localhost:12345');  -- proxy server
  CREATE USER MAPPING FOR current_user SERVER crm;
  
  -- for testing trying to get 'name' out of the CRM 'accounts' table and
     naming the foreign table the same as the table in CRM
  CREATE FOREIGN TABLE accounts (
      name varchar(255)
  ) SERVER crm;
  

• crmproxytest.py -, , : http://effbot.org/librarybook/simplehttpserver.htm

  import socketserver
  import http.server
  import urllib
  
  PORT = 12345
  
  class Proxy(http.server.SimpleHTTPRequestHandler):
      def do_GET(self):
          self.copyfile(urllib.urlopen(self.path), self.wfile)
  
  httpd = socketserver.ForkingTCPServer(('', PORT), Proxy)
  print ("serving at port", PORT)
  httpd.serve_forever()
  

  , , serving at port 12345, nmap -sT -O localhost, , , nmap, . .

  SELECT * FROM accounts PostgreSQL Can't get a response from server: Failed to connect to ::1: Permission denied.

• OAuth2. crm.py Microsoft, : http://alexanderdevelopment.net/post/2016/11/27/dynamics-365-and-python-integration-using-the-web-api/

  , Azure Active Directory, client_id, client_secret, OAuth 2.0 Token URI OAuth 2.0 Authorization URI. authorizationendpoint, , , , tokenendpoint, -API.

  , , API- Dynamics :

  import requests  
  import json
  
  #set these values to retrieve the oauth token
  crmorg = 'https://ORG.crm.dynamics.com' #base url for crm org  
  clientid = '00000000-0000-0000-0000-000000000000' #application client id  
  client_secret = 'SUPERSECRET'
  username = '[e-mail]' #username  
  userpassword = 'qwerty' #password
  authorizationendpoint =  'https://login.windows.net/ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZZZZZ/oauth2/authorize'
  tokenendpoint = 'https://login.windows.net/ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZZZZZ/oauth2/token' #oauth token endpoint
  
  #set these values to query your crm data
  crmwebapi = 'https://ORG.api.crm.dynamics.com/api/data/v8.2' #full path to web api endpoint  
  crmwebapiquery = '/accounts?$select=name&$orderby=name' #web api query (include leading /)
  
  #build the authorization token request
  tokenpost = {  
      'client_id':clientid,
      'client_secret': client_secret,
      'resource':crmorg,
      'oauthUrl': authorizationendpoint,
      'username':username,
      'password':userpassword,
      'grant_type':'password'
      }
  
  #make the token request
  tokenres = requests.post(tokenendpoint, data=tokenpost)
  
  #check the value of tokenres
  print(tokenres)
  
  #set accesstoken variable to empty string
  accesstoken = ''
  
  #extract the access token
  try:  
      accesstoken = tokenres.json()['access_token']
  except(KeyError):  
      #handle any missing key errors
      print('Could not get access token')
  
  # check point for debugging
  # print(accesstoken)
  
  #if we have an accesstoken
  if(accesstoken!=''):  
      #prepare the crm request headers
      crmrequestheaders = {
          'Authorization': 'Bearer ' + accesstoken,
          'OData-MaxVersion': '4.0',
          'OData-Version': '4.0',
          'Accept': 'application/json',
          'Content-Type': 'application/json; charset=utf-8',
          'Prefer': 'odata.maxpagesize=500',
          'Prefer': 'odata.include-annotations=OData.Community.Display.V1.FormattedValue'
          }
  
      #make the crm request
      crmres = requests.get(crmwebapi+crmwebapiquery, headers=crmrequestheaders)
  
      try:
          #get the response json
          crmresults = crmres.json()
  
          #loop through it
          for x in crmresults['value']:
              # print (x['fullname'] + ' - ' + x['contactid'])
              print (x['name'])
      except KeyError:
          #handle any missing key errors
          print('Could not parse CRM results')
  

  , OAuth2. crmwebapi crmwebapiquery , PostgreSQL, FDW , SQL- -API.

, , . , , , - , , . crm.py crmtest.py, , , , .

!

: , www_ftw , www_fdw.

Answer 1

FDW 1 .

Python script 2 . bash, 3 import(1) http.server, socketserver urllib. script, PORT. ( ), curl http://localhost:12345 , Postgres:

curl: (7) Failed to connect to localhost port 12345: Connection refused

#!/usr/bin/env python3 script . , curl http://localhost:12345/etc/passwd .

, 3 (OAuth), , , , .