In recent MR releases there have been a number of changes to improve the security of XG Firewall and to make it more difficult for attackers to get hold of sensitive information in the event that something does go wrong.
One of the changes was to remove the ability to export and import user accounts using csv files. There were a number of security concerns about this feature, in particular the added risk of exposure of password material that it encourages. Many customers now manage users by connecting their firewall to an external directory service, such as Active Directory, which overall creates a much more secure and manageable environment.
But some customers have established processes that rely on importing of lists of users from time to time. What can they do?
SFOS has an XML API that provides a way to automatically manage most objects and features that can be controlled via the Webadmin user interface, and User objects are no exception.
Using the XML API is a great alternative to the old CSV import feature for bulk adding of on-device user accounts.
But how? Well, here's an example. The attached Python program can read basic user data from a csv file and post it directly to your firewall's XML API. It basically replicates the old CSV import functionality.
*NOTE* If Python is not your thing, please check out the reply below in this thread where I added a Powershell version of this script. This can be run directly in Powershell on Windows without installing any other software.
If you have a process where, from time to time, you create a csv file with new user information in it, you can now use this script instead to import it directly instead of navigating through the WebAdmin UI.
PLEASE NOTE: The attached script does not currently work on SFOS installations where the Default Configuration Language is not English. I am working on an update and will post it here soon.
Here's how to do it. Not all steps will be required for everyone - if you already use Python, or if you use a Mac that has Python built-in, you should be pretty much set to go.
C:\Users\John> pip install requests python-certifi-win32
C:\Users\John> pip install requests python-certifi-win32 --cert mycacert.pem
This script expects you to provide a csv file, with fields separated by commas. Users with regional preferences that create csv files with semicolons or other characters may find that it works, or may need to adjust the script accordingly.
The file must have the following columns - Name, Username, Password, Email Address, Group:
Example file:
Name,Username,Password,Email Address,GroupNigel Brown,nbrown,Pa5s!w0rd19,nigel.brown@example.com,Open GroupGina Lopez,glopez,e1Azjr8q9^21,gina.lopez@example.com,Open Group
Name,Username,Password,Email Address,Group
Nigel Brown,nbrown,Pa5s!w0rd19,nigel.brown@example.com,Open Group
Gina Lopez,glopez,e1Azjr8q9^21,gina.lopez@example.com,Open Group
Download this zip file and extract it to a directory on your computer. The rest of these instructions assume you saved it in 'Downloads', and that the csv file containing the users you want to add is in 'Documents' and called users.csv
users.csv
Run the following command, substituting your firewall's hostname or IP address, admin username and password. If you're using an account with multi-factor authentication (OTP) then don't forget to append the authentication code to the end of the password:
C:\Users\John> python Downloads\UserImport.py -f myfirewall.example.com -i Documents\users.csv -u admin -p A1B2c3d4!!E5 -a
If you see exception messages related to certificate trust issues, try running the same command again, but add '-n' as an additional command-line qualifier. The argument '-n' tells Python to ignore any certificate validation errors when connecting to the firewall - python does not automatically pick up any additional root CAs from your Windows installation:
C:\Users\John> python Downloads\UserImport.py -f myfirewall.example.com -i Documents\users.csv -u admin -p A1B2c3d4!!E5 -a -n
As long as the details of all the users are correct, the import should proceed successfully. If it does not, check the message responses for clues as to what went wrong.
If you don't want to download the code as a zip file, you can copy and paste it from here:
# UserImport # # Takes basic User definitions from a CSV file and posts # them to an XG Firewall using the XML API import xml.etree.ElementTree as ET import argparse import requests import sys import configparser import csv serial = 0 UserAPIAddResults = { "200": (True, "User registered successfully"), "500": (False, "User couldn't be registered"), "502": (False, "A user with the same name already exists"), "503": (False, "A user with the same L2TP/PPTP IP already exists"), "510": (False, "Invalid password - doesn't meet complexity requirements") } UserAPIUpdateResults = { "200": (True, "User registered successfully"), "500": (False, "User couldn't be updated"), "502": (False, "A user with the same name already exists"), "503": (False, "A user with the same L2TP/PPTP IP already exists"), "510": (False, "Invalid password - doesn't meet complexity requirements"), "541": (False, "There must be at least one Administrator"), "542": (False, "There must be at least one user with Administrator profile") } def eprint(*args, **kwargs): # Print to stderr instead of stdout print(*args, file=sys.stderr, **kwargs) def getArguments(): parser = argparse.ArgumentParser( description='Grab a list of video IDs from a YouTube playlist') parser.add_argument( '-f', '--firewall', help=("To call the API directly, specify a firewall hostname or IP. " "Without this, an XML API document will be output to stdout."), default=argparse.SUPPRESS) parser.add_argument( '-i', '--input', metavar="FILENAME", help=("Name of CSV file containing these columns:" " Name, Username, Password, Email Address, Group"), default=argparse.SUPPRESS) parser.add_argument( '-u', '--fwuser', metavar="ADMIN-USERNAME", help='Admin username for the XG firewall', default='admin') parser.add_argument( '-p', '--fwpassword', metavar="ADMIN-PASSWORD", help='Password for the XG user - defaults to "admin"', default='password') parser.add_argument( '-a', '--add', help=('Call API in "Add" mode - use first time only (otherwise' ' Update will be used)'), action='store_true') parser.add_argument( '-n', '--insecure', help="Don't validate the Firewall's HTTPS certificate", action='store_false') parser.add_argument( '-1', '--oneshot', help=("Create a single enormous XMLAPI transaction instead " " of multiple smaller ones. Only used when outputting " "to stdout "), action='store_true') parser.add_argument( '-x', '--useimpex', help=("Create an Entities.xml-style file for inclusion in an " " Import tarball"), action='store_true') return parser.parse_args() def readConfig(): # Read in the temp config file (if it exists) global config global workingPath config = configparser.ConfigParser() config.read(workingPath) def getserial(): # Returns an incrementing serial number. Used to reference individual # XMLAPI transactions. global serial serial = serial + 1 return str(serial) def xgAPIStartUser(username, name, password, email, group="Open Group", usertype="User", surfquota="Unlimited Internet Access", accesstime="Allowed all the time", datatransferpolicy="", qospolicy="", sslvpnpolicy="", clientlesspolicy="", status="Active", l2tp="Disable", pptp="Disable", cisco="Disable", quarantinedigest="Disable", macbinding="Disable", loginrestriction="UserGroupNode", accessschedule="All The Time", loginrestrictionapp="", isencryptcert="Disable", simultaneouslogins="Enable"): # Returns a complete XML User record with some sane defaults which can # be modified later, if you want. userblock = ET.Element('User', transactionid=getserial()) ET.SubElement(userblock, 'Username').text = username ET.SubElement(userblock, 'Name').text = name ET.SubElement(userblock, 'Password').text = password ET.SubElement(ET.SubElement(userblock, 'EmailList'), 'EmailID').text = email ET.SubElement(userblock, 'Group').text = group ET.SubElement(userblock, 'SurfingQuotaPolicy').text = surfquota ET.SubElement(userblock, 'AccessTimePolicy').text = accesstime ET.SubElement(userblock, 'DataTransferPolicy').text = datatransferpolicy ET.SubElement(userblock, 'QoSPolicy').text = qospolicy ET.SubElement(userblock, 'SSLVPNPolicy').text = sslvpnpolicy ET.SubElement(userblock, 'ClientlessPolicy').text = clientlesspolicy ET.SubElement(userblock, 'Status').text = status ET.SubElement(userblock, 'L2TP').text = l2tp ET.SubElement(userblock, 'PPTP').text = pptp ET.SubElement(userblock, 'CISCO').text = cisco ET.SubElement(userblock, 'QuarantineDigest').text = quarantinedigest ET.SubElement(userblock, 'MACBinding').text = macbinding ET.SubElement(userblock, 'LoginRestriction').text = loginrestriction ET.SubElement( userblock, 'ScheduleForApplianceAccess').text = accessschedule ET.SubElement( userblock, 'LoginRestrictionForAppliance').text = loginrestrictionapp ET.SubElement(userblock, 'IsEncryptCert').text = isencryptcert ET.SubElement( userblock, 'SimultaneousLoginsGlobal').text = simultaneouslogins return userblock def xgAPILogin(fwuser, fwpassword): # Returns a root Login element for an XMLAPI call requestroot = ET.Element('Request') login = ET.SubElement(requestroot, 'Login') ET.SubElement(login, 'Username').text = fwuser # ET.SubElement(login, 'Password', passwordform = 'encrypt').text = fwpassword ET.SubElement(login, 'Password').text = fwpassword return requestroot def xgImpExBegin(): return ET.Element('Configuration', APIVersion="1702.1", IPS_CAT_VER="1") def xgAPIPost(requestroot): # Posts an XMLAPI document to the firewall specified in command line -f arg # If there is no -f arg, it prints the document to stdout # Parameter 'requestroot' provides the root element of the XML document postdata = { 'reqxml': ET.tostring(requestroot, 'unicode') } result = 0 try: callurl = ('https://' + stuff.firewall + ':4444/webconsole/APIController') eprint("Sending XMLAPI request to %s" % callurl) r = requests.post(callurl, data=postdata, verify=stuff.insecure) # eprint(r) # eprint(r.text) result = 1 except AttributeError: print(ET.tostring(requestroot, 'unicode')) result = 0 return (result, r) # Fun starts here # Check command line stuff = getArguments() try: eprint("Reading from file: %s" % stuff.input) except AttributeError: eprint("No filename provided. Run this command with --help for more info.") sys.exit() # If there's a firewall address set, make sure we're in oneshot mode try: eprint("Config will be posted to " + stuff.firewall) stuff.oneshot = True except AttributeError: eprint("No firewall set") if stuff.add: method = 'add' else: method = 'update' # Start building the XMLAPI Request if stuff.useimpex: fwRequestroot = xgImpExBegin() fwRequestSet = fwRequestroot else: fwRequestroot = xgAPILogin(stuff.fwuser, stuff.fwpassword) fwRequestSet = ET.SubElement(fwRequestroot, 'Set', operation=method) usernames = [] # Open the csv for reading with open(stuff.input, encoding="utf-8-sig") as input_csv: csv_parser = csv.DictReader(input_csv, delimiter=',') count = 0 for row in csv_parser: # eprint(row) fwRequestSet.append(xgAPIStartUser(row["Username"], row["Name"], row["Password"], row["Email Address"], group=row["Group"])) usernames.append(row["Username"]) count = count + 1 eprint("Read %d users from file %s\n" % (count, stuff.input)) if stuff.oneshot or stuff.useimpex: result, r = xgAPIPost(fwRequestroot) if result == 1: resultcontent = ET.fromstring(r.text) for child in resultcontent: if child.tag == "Login": status = child.find('status').text if status == '200': eprint("API login successful") else: eprint("Login status: %s" % child.find('status').text) if child.tag == "User": Status = child.find('Status') eprint("Line %s (%s), Status %s" % ( child.attrib["transactionid"], usernames[int( child.attrib["transactionid"]) - 1], Status.attrib["code"])) try: if stuff.add: eprint(" %s" % UserAPIAddResults[Status.attrib["code"]][1]) else: eprint(" %s" % UserAPIUpdateResults[Status.attrib["code"]][1]) except KeyError: eprint("Message: %s" % Status.text) continue if child.tag == "Status": eprint("Error code %s: %s" % (child.attrib['code'], child.text)) # Take the xml output from this program and send it to your firewall with curl, # for example: # $ curl -k https://<firewall ip>:4444/webconsole/APIController -F "reqxml=<foo.xml" # To create an API Import file, use '-x', write the XML output to a file # 'Entities.xml' and create a tarball with the following (group/owner options # to make it anonymous): # $ tar --group=a:1000 --owner=a:1000 --numeric-owner -cvf ../API-O365.tar Entities.xml
The stated claim that the CSV import removal was because of security concerns does not hold water. In fact, by having the API and using the script, security is actually decreased as clear text passwords…
The stated claim that the CSV import removal was because of security concerns does not hold water. In fact, by having the API and using the script, security is actually decreased as clear text passwords need to be stored in the script and the CSV. How does that increase security??
All Sophos did was removed a feature without user input. We have many customers that use CSV for remote locations that do not have Radius or AD.
This is nothing more than a very difficult, convoluted, and insecure work around to a very poor decision to remove the CSV import feature.
Now I have customers looking to migrate to Fortinet Fortigate. Brilliant Sophos!
My advice - re-enable the CSV feature and trust your users to implement security measures. We are professionals, not neophytes.
Nick
Certified Architect & Engineer
Just to be clear, there is no need to store the admin password in the script or in the csv - you just need to provide it in the command line when you run the program. It does not store the admin password anywhere.
We'll certainly take your feedback into account. I appreciate it's annoying to have to change a process. Removing the old CSV handling feature reduces the surface area of potential weakness for all customers, not only those who make use of the feature.
I am curious on the need to import users via CSV. Can you give me some insights, why you want to import the users in the first place? Because in my experience, most of those use scenarios can be resolved in a different way, and maybe we can give you some insights, how to handle this request differently, without a CSV import.
__________________________________________________________________________________________________________________
Hi Rich -- thanks for the reply. I was referring to the user password, not the admin password. I do not think there is a no way to avoid the user password because without it, the user will not be able to login.
Hello Lucar Toni,
probably because importing users into XG is only possible through the captive portal, right?
If there is another way to import users to XG, please tell me how? I'm really happy to learn it.
Yes, importing groups from MS AD works reliably and I really can't understand why it is not possible to import users in the same way. This is ONLY another query to MS AD.
But I guess I really don't understand at all ....
Regards
alda
Hi Lucar Toni -Any remote installation without AD, radius etc. where the users are constantly changing. Think transport where these devices are installed. crews constantly change, bandwidth is thin, and app and url control is a must. The Sophos XG being Linux at the core, perhaps there is a way to do this via a script as there is in Linux. Even if so, we would be finding a solution that is as insecure or more insecure than the CSV import was *purported" to be.
Hi Alda -- not all installations us MS AD - kindly see my reply to Rich. Thank.
XG will create the user with every authentication. So basically all the time, you authenticate with "something" against the AD.
Most likely this is for customers enough, if they know, if the user is going to authenticate, they get all the needed configuration settings etc.
There are edge cases, where no authentication is in place upfront. For example quarantine digest (You are using XG as a Email gateway only and want to get the digest).
But most customers have some sort of authentication in place (Captive Portal, Kerberos/NTLM, STAS, SATC, Sync-sec User ID etc.). Those mechanism will automatically create all users, which are used.
To fetch those users upfront would need more work as you think, as the user can be fetched but need always a password. If you fetch a user, this user is also created on the XG. It would need a password to be used. So we would need also to fetch the password, which is not that easy to be handled. XG expect to have a way to authenticate those user. So you would have to limited those users against AD only. There is plenty of work to do for a fetching mechanism to work and this would only benefit customers, which have a rare use case (like explained above). Others will likely have the user created by the daily usage.
Hi LuCar Toni -- I respectfully submit that we are getting far afield here. My initial post was specifically for the non-AD ("Captive Portal, Kerberos/NTLM, STAS, SATC, Sync-sec User ID etc") use case. This use case exists and it is not an "edge case" scenario. The Firewall has the ability to authenticate users "locally" which is what several of our customers need for their specific use scenario.
I still maintain that the elimination of the CSV import was a mistake and now puts Sophos XG at a competitive disadvantage to other firewalls that recognize that not all installations can avail themselves of external authentication methods.
Certified Sophos Architect & Engineer
Hello LuCar Toni,
nice justification, but I think you definitely did not answer my question how can I bulk import users from MS AD without a captive portal or csv file!Why could XG Firewall only import user groups but cannot create user fingerprints from MS AD from these user groups to the local user database? Why could not you implement this feature when UTM v9 has been supporting this feature (if I remember correctly) for at least 10 years?
Is it really such a problem?