I needed to audit a personal Seafile cloud storage instance. Unfortunately, the web interface doesn’t provide a convenient way to export lists of users, repositories, etc. all at once. Therefore, I decided to extract all necessary information directly from the databases, as the structure is simple and there aren’t too many tables. While the built-in API could have been used, in this case I found the direct database approach simpler.

SQL Scripts:

lusers.sql

Gets a list of local system users.

use ccnet-db;
select 'EmailUser' AS 'table',e.id,e.email,e.is_staff,e.is_active,FROM_UNIXTIME(ctime / 1000000) as ctime,l.last_login from EmailUser e LEFT JOIN `seahub-db`.base_userlastlogin l ON e.email=l.username;

The most interesting field here is is_staff – if the value is not 0, the user is an administrator.

adusers.sql

Gets a list of LDAP-connected users (e.g., from Active Directory).

use ccnet-db;
select 'LDAPUsers' AS 'table',e.id,e.email,e.is_staff,e.is_active,l.last_login from LDAPUsers e LEFT JOIN `seahub-db`.base_userlastlogin l ON e.email=l.username;

This query is essentially similar to the previous one.

repos.sql

Provides information about all repositories (regular, public, and virtual), including access permissions.

USE seafile-db;
SELECT
  r.repo_id,
  COALESCE(i.name, origin_info.name) AS repo_name,
  virt.path AS virtual_path,
  o.owner_id AS owner_id,
  s.size AS repo_size_MB,
  f.file_count,
  CASE WHEN virt.origin_repo IS NOT NULL THEN 'true' ELSE 'false' END AS is_virtual,
  CASE WHEN pub.permission IS NOT NULL THEN 'true' ELSE 'false' END AS is_public,
  sr.to_email AS shared_with_user,
  sr.permission AS user_permission,
  cg.group_name AS shared_with_group,
  rg.permission AS group_permission
FROM
  Repo r
LEFT JOIN RepoOwner o ON r.repo_id = o.repo_id
LEFT JOIN RepoSize s ON r.repo_id = s.repo_id
LEFT JOIN RepoFileCount f ON r.repo_id = f.repo_id
LEFT JOIN RepoInfo i ON r.repo_id = i.repo_id
LEFT JOIN VirtualRepo virt ON r.repo_id = virt.repo_id
LEFT JOIN RepoInfo origin_info ON virt.origin_repo = origin_info.repo_id

-- Users access
LEFT JOIN SharedRepo sr ON r.repo_id = sr.repo_id

-- Groups access
LEFT JOIN RepoGroup rg ON r.repo_id = rg.repo_id
LEFT JOIN `ccnet-db`.Group cg ON rg.group_id = cg.group_id

-- Public repos
LEFT JOIN InnerPubRepo pub ON r.repo_id = pub.repo_id

ORDER BY repo_name;

This is probably the most useful query for auditing purposes.

groups.sql

Information about groups and their members.

USE ccnet-db;
SELECT g.user_name,grp.group_name,grp.creator_name
FROM `GroupUser` g
LEFT JOIN `Group` grp ON g.group_id = grp.group_id
ORDER BY grp.group_name;

fileshares.sql

Information about published file/folder access links (useful for preventing data leaks).

use seahub-db;
select 
s.username,
s.repo_id,
COALESCE(i.name, origin_info.name) AS repo_name,
s.path,
s.ctime,
s.view_cnt,
s_type,
CASE WHEN s.password IS NOT NULL THEN 'true' ELSE 'false' END AS password,
s.expire_date,
s.permission
from share_fileshare s 
LEFT JOIN `seafile-db`.RepoInfo i ON s.repo_id = i.repo_id
LEFT JOIN `seafile-db`.VirtualRepo virt ON s.repo_id = virt.repo_id
LEFT JOIN `seafile-db`.RepoInfo origin_info ON virt.origin_repo = origin_info.repo_id;

uploadshares.sql

Similar to the previous query, but lists file upload links

use seahub-db;
select 
s.username,
s.repo_id,
COALESCE(i.name, origin_info.name) AS repo_name,
s.path,
s.ctime,
s.view_cnt,
CASE WHEN s.password IS NOT NULL THEN 'true' ELSE 'false' END AS password,
s.expire_date
from share_uploadlinkshare s 
LEFT JOIN `seafile-db`.RepoInfo i ON s.repo_id = i.repo_id
LEFT JOIN `seafile-db`.VirtualRepo virt ON s.repo_id = virt.repo_id
LEFT JOIN `seafile-db`.RepoInfo origin_info ON virt.origin_repo = origin_info.repo_id;

Ideally, such links (both from this and the previous example) should be avoided, or at least password-protected.

devices.sql

Lists devices connected to the system (should be checked and cleaned up periodically).

use seahub-db;
select user,platform,device_name,platform_version,client_version,last_accessed,last_login_ip,created_at from api2_tokenv2 order by last_accessed;

Execution Methods

You can run these scripts manually with commands like:

mysql -u root -p < devices.sql > devices.csv

But it’s easier to use this small bash script:

#!/bin/bash
mkdir -p result
read -s -p "Enter MySQL password: " MYSQL_PWD
echo

for sql_file in *.sql; do
    base_name="${sql_file%.sql}"
    output_file="${base_name}.txt"

    echo "Executing $sql_file -> $output_file"
    mysql -u root -p"$MYSQL_PWD" < "$sql_file" > ./"result/$output_file" 2>&1
done

rm -rf audit-result.tar.gz 
tar -czvf audit-result.tar.gz ./result

After execution, you’ll get a set of CSV files packed into an archive that can be transferred from the server using scp and processed with convenient tools.

PS: This was tested on a rather old version of Seafile. Some things might not work in current versions or might require updates. Please comment if you encounter any issues.

PPS: The scripts are available on GitHub.