Data Security and AI - Sharing Your PostgreSQL Database With Ollama AI

You probably saw the story about your public ChatGPT queries being indexed by Google and other search engines. Yikes! 

You may want AI with your data but do not wish your prompts to end up in a search engine, especially if you are working with sensitive data.  

Previously, we covered using some of DBeaver's Artificial Intelligence Assistant features with Ollama. Ollama is popular because it runs on your system, keeping your data from travelling over networks to an AI and back. What happens only on your system stays on your system, to paraphrase the Las Vegas motto. Paranoia in defense of your data is a good thing.

Secure By Default


We will start with an example of preference settings for DBeaver Enterprise Edition for Ollama. DBeaver has other security options I want to bring to your attention when working with an AI. Ollama differs from most other AIs because you do not have to obtain a key to work with it.


AI Configuration - DBeaver Enterprise Edition


The ability to tailor what you send to the AI should be your next step. You can also configure what to share with the AI. The default is to share only the metadata—tables, column names, and Views—but not the data. Optionally, sharing information about keys and object descriptions can improve the AI's job, at the expense of extra token consumption.

Send database structure options in this example, including foreign, unique, and primary keys.


Note that you can send table data to the AI to help it develop a better solution. This is the only option, and you can make the choice. If you do not want to share, you do not have to. The query will also run only on the local machine. 

Setting The Scope


The AI Assistant can direct the scope of the data to be used.


You can define the scope of database objects that the AI should consider for generating SQL queries and providing assistance. This scope enables the AI to focus on relevant parts of the database, thereby improving accuracy and efficiency, particularly in large schemas. This is particularly handy when you have two similar schemas or databases, and only one of them is intended for the work. 

Memory

Ollama will not impress you with its performance until you have 32GB of memory. My original attempts with 16GB were slow and disappointing. Remember that you are running Ollama and DBeaver at a minimum and maybe a local instance, all of which use memory. I suggest you not attempt using Ollama until you have at least 32GB. 

Example

We ask Ollama to create a table on a local PostgreSQL instance

We ask Ollama to create a contact list table

Ollama responds with the DDL for the requested table

Now we request some test data

The table and data from Ollama


Conclusion


DBeaver allows you to use an AI to work without worrying that your prompts will end up on a search engine.







Comments

Post a Comment

Popular posts from this blog

Incremental Backups in PostgreSQL 17

 The old adage that a DBA or SRE is only as good as their last backup is true.  PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use. This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies. Step 1 - Enable WALL Summerization The incremental backups require Write Ahead Log Summarization. It is likely not on your instance by default. But it is easy to enable. demo=# alter system set summerize_wal = 'on'; ALTER SYSTEM demo=# select pg_reload_conf(); pg_reload_conf  ----------------  t (1 row) Step 2 - Full Backup The examples below are much simpler than you will run into in real life.  The scenario is that we make a full backup on Sunday and do incrementals the rest of the week. Wednesday through Saturday are omitted below.  stoker@ThinkPa...
Read more

Can Artificial Intelligence Created Better Tables Than You?

 Artificial Intelligence is one of those conundrums where the ability to have some tasks handled for you contrasts with a mix of ego and pride that it may be better than you at those tasks.  I recently wrote a blog using another database about an AI-generated SQL that was quite sophisticated . But what about asking an AI to create a table with a specification like a DBA/SRE/Analyst might receive? I used Grok and entered the following prompt: Write the SQL to create a table on a PostgreSQL version 17 server to record data on customers. The data will include a first name, a last name, an address, a birthdate, and a unique UUID primary key. Partition the data by the year of birth. And produce some test data of at least ten records. I am sure many of us have started large projects given less instruction.  Notice: I did not denote the format for the address (US Post Office's format, or UK, or other). Nor did I set a name length. I wanted to see what assumptions were made by t...
Read more

Saving The Output From psql

  Occasionally, you will need to capture data when working with a database. You can cut-n-paste or use a shell command like script . In typical PostgreSQL fashion, psql  has two ways to save your output. The First Way You must invoke psql with the --log-file-<filename> or -L <filename> option. The entire session will be recorded. stoker@ThinkPad:~$ psql --log-file=/tmp/psqllog demo Password for user stoker: psql (17.4 (Ubuntu 17.4-1.pgdg24.04+2)) Type "help" for help. demo=# \d ledger                    Table "public.ledger"      Column     |  Type   | Collation | Nullable | Default ----------------+---------+-----------+----------+---------  id             | integer |           |          |  tx_id          | integer |        ...
Read more