Introduction to OmniDB

Why running it today is a risk

• No security fixes. OmniDB is a web application that stores database credentials and can run any SQL as those users. Its Python dependencies (Django, CherryPy and others) have had years of security releases that an old installation does not include.
• A known default login. A fresh server creates the user admin with the password admin. An instance reachable from a network with that account unchanged gives away every database it can connect to.
• No support for current PostgreSQL. It was built for PostgreSQL 13. Basic querying of newer servers may work, but the object tree, DDL generation and monitoring views depend on system catalogs that have changed since, and nobody is fixing what breaks.
• Installation is getting harder. The packages were built for operating systems and Python versions from 2020.
• It never left beta. The 3.0 line changed the internals substantially and was not followed by a stable release.

If you still have OmniDB running

Until you migrate, reduce the exposure:

1. Do not expose it to the internet. Bind it to localhost and reach it through an SSH tunnel or VPN. The listening address and port are set with -H and -p (the default port is 8000):

   omnidb-server -H 127.0.0.1 -p 8000
   

   ssh -L 8000:127.0.0.1:8000 you@omnidb-host
   

   Then browse to http://127.0.0.1:8000 on your own machine.

2. Replace the default admin account with a named superuser, then list what exists:

   omnidb-server --createsuperuser alice 'use-a-long-passphrase'
   omnidb-server --dropuser admin
   omnidb-server --listusers
   omnidb-server --listconnections alice
   

   --listconnections takes an OmniDB username and prints that user's saved connections. Run it for each user and keep the output as the inventory of what to recreate in the replacement tool.

3. Connect with least privilege. Give OmniDB database roles that can do only what its users need. Do not store superuser credentials in it.

4. Export what you want to keep. Saved snippets and custom monitoring queries live in OmniDB's own metadata database in its home directory. Copy the SQL out by hand; there is no export format other tools read.

Maintained alternatives

How to choose:

• You liked OmniDB and want the same feel: Audax Data Manager.
• PostgreSQL only, and you need the debugger or server administration: pgAdmin 4.
• Many database types from one desktop tool: DBeaver.
• A browser-based tool hosted for a team: CloudBeaver, or pgAdmin 4 in server mode.

Whichever you pick, the security advice above still applies to any web-hosted database tool: keep it off the public internet, put it behind single sign-on or a VPN, and give it least-privilege database accounts.

Moving off OmniDB

There is no automatic migration, but the job is small:

1. List each user's saved connections with omnidb-server --listconnections <username> and recreate them in the new tool. Take the opportunity to rotate the passwords, since they were stored in an unmaintained application.
2. Copy any saved snippets and custom monitoring queries across as plain SQL.
3. If you installed OmniDB's debugger plugin on a PostgreSQL server, remove omnidb_plugin from shared_preload_libraries and drop the schema or extension it created during the next maintenance window. Unmaintained server-side code is a bigger liability than an unmaintained client.
4. Stop the OmniDB service and delete its home directory, which contains the stored connection details.

Further reading

• OmniDB repository on GitHub (source and final release notes)
• Audax Data Manager repository
• pgAdmin 4 documentation
• DBeaver documentation

How do I insert a row only if it doesn't exist, without race conditions?

Select-then-insert breaks under concurrency. Use a unique constraint plus one atomic statement: ON CONFLICT, ON DUPLICATE KEY UPDATE, or UPDLOCK/SERIALIZABLE in SQL Server.

#concurrency#schema-design#transactions