How Database Encryption Works
You create an encrypted database by using the -encrypt flag on the createdb command. At that time, you are prompted to enter an initial passphrase that will be used to lock and unlock the encrypted database. Createdb leaves an encrypted database unlocked until the DISABLE PASSPHRASE statement is issued.
When an encrypted database is created, a database encryption key is derived from the randomly generated main key of the database. The database encryption key is kept in shared memory only and is used to encrypt and decrypt the data at rest. Only the main key is persisted and protected by encrypting it with an AES key, called “protection key”, derived from the specified PASSPHRASE. See the diagram in
Encryption Key Management that shows how the different keys and the passphrase are related.
The database is unlocked using the ENABLE PASSPHRASE statement in a session connected to iidbdb, where the statement specifies the correct passphrase and the database to be unlocked. The protection key derived from the passphrase is used to decrypt the main key and the database encryption key is derived from the main key. Again, the database encryption key is only kept in shared memory for the encryption and decryption of database data. The statement DISABLE PASSPHRASE or a server shutdown clears the database encryption key from memory and the database is effectively locked. To access the encrypted database again, the statement ENABLE PASSPHRASE must be issued.
Using Database Encryption
Disable Access to an Encrypted Database
To lock an encrypted database, disable the passphrase with the DISABLE PASSPHRASE statement. The passphrase must always be enclosed in single quotes. If the passphrase contains a single quote, escape the single quote by repeating it. Example with the passphrase containing blanks:
DISABLE PASSPHRASE 'my secret passphrase';
An attempt to access the database in a new database session results in an error message.
Note: The DISABLE PASSPHRASE statement does not take effect for already running database sessions. However, it takes effect for new database sessions that are started after the statement has been run.
Note: To lock an encrypted database in an emergency situation, the database owner can run the DISABLE PASSPHRASE statement without specifying the passphrase. The database owner must have the SECURITY privilege to perform this task.
Enable Access to an Encrypted Database
To enable access to a locked encrypted database, e.g. after server startup, unlock the database with the ENABLE PASSPHRASE statement. This must be done in a session that is connected to the iidbdb database. The ENABLE PASSPHRASE statement must specify the correct passphrase and the database that is to be unlocked. The passphrase must always be enclosed in single quotes. If the passphrase contains a single quote, escape the single quote by repeating it. Example with the passphrase containing blanks:
ENABLE PASSPHRASE 'my secret passphrase' ON DATABASE mydb;
Creating and Locking an Encrypted Database
1. Execute the following command at the command prompt:
createdb mydb -encrypt
When prompted, enter an initial passphrase. The passphrase can contain blanks, but trailing blanks are ignored.
Note: Createdb leaves the encrypted database unlocked.
2. Connect to the database:
sql mydb
3. Lock the database:
DISABLE PASSPHRASE 'my passphrase';
The database is locked for new sessions.
Database encryption is transparent to the application
The following example creates a table in an unlocked encrypted database, inserts rows into the table, and then selects them:
CREATE TABLE socsec1
(fname CHAR(10),
lname CHAR(20),
socsec CHAR(11)
INSERT INTO socsec1 VALUES ('John', 'Smith', '012-33-4567');
INSERT INTO socsec1 VALUES ('Lois', 'Lane', '010-40-1234');
INSERT INTO socsec1 VALUES ('Charlie', 'Brown', '012-44-9876');
SELECT * FROM socsec1;
The following results are returned:
+----------+--------------------+-----------+
|fname |lname |socsec |
+----------+--------------------+-----------+
|John |Smith |012-33-4567|
|Lois |Lane |010-40-1234|
|Charlie |Brown |012-44-9876|
+----------+--------------------+-----------+
(3 rows)
As shown by the plain text values in all columns, the encryption is transparent to the application (in this case, the SQL terminal monitor).
Last modified date: 01/27/2026