Exporting documents from PasswordState

I have been using PasswordState for couple of years, and I have to say that I like it.

But there is one feature missing. Since you might want to take offline copy of everything that you have in your password management solution to some offsite place - in a form that does not require you to set-up your servers in case of disaster - you might need to copy out more than just your passwords.

Exporting passwords is easy, built-in feature. But exporting documents that you might have (like certificate files etc) is not that easy.

You have to use API-interface to download, and read file names from database. A bit tricky, and if you need to do that regularly, you need to have at least a (PowerShell) script for you to do that.

So here is what I did:

 
#SQL Functionality from:
#https://cmatskas.com/execute-sql-query-with-powershell/


Param(
 [string]$apikey,
 [string]$exportpath 
 )
 
if(-not$apikey) { Throw "Mandatory paramter Apikey not specified" }
if(-not$exportpath) { Throw "Mandatory paramter exportpath not specified" }
if(!(test-path $exportpath)){Throw "Exportpath " +$exportpath+ " does not exist"}

$ItemDocPath = $exportpath + "\PWItemDocuments"
$ListDocPath = $exportpath + "\PWListDocuments"


if(!(test-path $ItemDocPath)){New-Item -ItemType directory $ItemDocPath}
if(!(test-path $ListDocPath)){New-Item -ItemType directory $ListDocPath}



#ENVIRONMENT VARIABLES, MODIFY TO MATCH YOUR ENVIRONMENT
$Server = "yoursqlinstance"
$Database = "yourdatabase"
$PasswordStateSiteUrl = "https://yourpasswordstateurl/"
#ENVIRONMENT VARIABLES, MODIFY TO MATCH YOUR ENVIRONMENT

#DO NOT CHANGE THESE
$QueryListDocuments = $("SELECT * FROM ["+$Database+"].[dbo].[PasswordListDocuments]")
$QueryItemDocuments = $("SELECT * FROM ["+$Database+"].[dbo].[PasswordDocuments]")
$QueryPasswordLists = $("SELECT [PasswordListID],[PasswordList],[Description] FROM ["+$Database+"].[dbo].[PasswordLists]")
$QueryPasswords = $("SELECT [PasswordID],[Title],[PasswordListID] FROM ["+$Database+"].[dbo].[Passwords]")
#DO NOT CHANGE THESE

function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {
    $Datatable = New-Object System.Data.DataTable
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    $Datatable.Load($Reader)
    $Connection.Close()
    
    return $Datatable
}

$PasswordLists = New-Object System.Data.DataTable
$PasswordLists = ExecuteSqlQuery $Server $Database $QueryPasswordLists

$Passwords = New-Object System.Data.DataTable
$Passwords = ExecuteSqlQuery $Server $Database $QueryPasswords

$ListDocuments = New-Object System.Data.DataTable
$ListDocuments = ExecuteSqlQuery $Server $Database $QueryListDocuments

$ItemDocuments = New-Object System.Data.DataTable
$ItemDocuments = ExecuteSqlQuery $Server $Database $QueryItemDocuments



foreach ($document in $ListDocuments) {
 $command = 'curl ' + $PasswordStateSiteUrl+'api/document/passwordlist/"'+$document.DocumentID+'"?apikey='+$apikey+' -OutFile "'+$ListDocPath+'\'+$document.DocumentID+'_'+$document.DocumentName+'"'
 invoke-expression $command

 $temp = $passwordlists|Where-Object {$_.PasswordListID -eq $document.PasswordListID}
 $pwlistname = $temp.PasswordList
 
 [array]$PWListDocuments += New-Object -TypeName PSObject -Property @{
   "OriginalFileName" = $document.DocumentName
   "DocumentID" = $document.DocumentID
   "Description" = $document.DocumentDescription
   "PasswordList" = $pwlistname
   "Modified" = $document.Modified
   "ModifiedBy" = $document.ModifiedBy
  }
 
 
 }

 
foreach ($document in $ItemDocuments) {
 $command = 'curl ' + $PasswordStateSiteUrl+'api/document/password/"'+$document.DocumentID+'"?apikey='+$apikey+' -OutFile "'+$ItemDocPath+'\'+$document.DocumentID+'_'+$document.DocumentName+'"'
 invoke-expression $command
 
 $temp = $Passwords|Where-Object {$_.PasswordID -eq $document.PasswordID}
 $PasswordTitle = $temp.Title
 $PasswordListID = $temp.PasswordListID
 
 $temp = $passwordlists|Where-Object {$_.PasswordListID -eq $PasswordListID}
 $pwlistname = $temp.PasswordList
 
 
 [array]$PWDocuments += New-Object -TypeName PSObject -Property @{
   "OriginalFileName" = $document.DocumentName
   "DocumentID" = $document.DocumentID
   "Description" = $document.DocumentDescription
   "PasswordItem" = $PasswordTitle
   "PasswordList" = $pwlistname
   "Modified" = $document.Modified
   "ModifiedBy" = $document.ModifiedBy
   
   
   
   
  }
 
 }

 
if ($PWListDocuments){
 $outputfile = $exportpath+'\PWListDocuments.csv'
 $PWListDocuments|Export-CSV -useCulture -NoType -Encoding UTF8 $outputfile
 }

 if ($PWDocuments)
 {
  $outputfile = $exportpath+'\PWItemDocuments.csv'
  $PWDocuments|Export-CSV -useCulture -NoType -Encoding UTF8 $outputfile
 }

So, this script does following things when you run it:

• Needs two parameters:
• apikey: you need to have a system wide API-key in your PasswordState
• exportpath: root path, where documents etc are exported
• this path needs to exist
• It creates two documents under exportpath, 'PWItemDocuments' and 'PWListDocuments'.
• Does four queries to your PasswordState database to read information that we need
• Exports all documents that are attached in password lists to 'PWListDocumnts' folder (with ID-number as a prefix)
• Exports all documents that are attached in password items to 'PWItemDocuments' folder (with ID-number as a prefix)
• Creates two .csv files to exportpath, with following info:
• PWListDocuments.csv
• Original filename
• Document ID in PasswordState
• Description of document
• Password list name where document is attached to
• Modified date
• Modifier
• PWItemDocuments.csv
• Original filename
• Document ID in PasswordState
• Description of document
• Password item name where document is attached to
• Password list name where that item is
• Modified date
• Modifier

Reason that I'm adding document ID as a prefix, is that you might have duplicate filenames in your attachments, and this way we can get all of those exported.