Backing up and restoring a SQL/Protect database v17

Suggest edits

Backing up a database that's configured with SQL/Protect and then restoring the backup file to a new database requires considerations in addition to those normally associated with backup and restore procedures. These added considerations are mainly due to the use of object identification numbers (OIDs) in the SQL/Protect tables.

Note

This information applies if your backup and restore procedures result in re-creating database objects in the new database with new OIDs, such as when using the pg_dump backup program.

If you're backing up your EDB Postgres Advanced Server database server by using the operating system’s copy utility to create a binary image of the EDB Postgres Advanced Server data files (file system backup method), then this information doesn't apply.

Object identification numbers in SQL/Protect tables

SQL/Protect uses two tables, edb_sql_protect and edb_sql_protect_rel, to store information on database objects such as databases, roles, and relations. References to these database objects in these tables are done using the objects’ OIDs, not their text names. The OID is a numeric data type used by EDB Postgres Advanced Server to uniquely identify each database object.

When a database object is created, EDB Postgres Advanced Server assigns an OID to the object, which is then used when a reference to the object is needed in the database catalogs. If you create the same database object in two databases, such as a table with the same CREATE TABLE statement, each table is assigned a different OID in each database.

In a backup and restore operation that results in re-creating the backed-up database objects, the restored objects end up with different OIDs in the new database from what they were assigned in the original database. As a result, the OIDs referencing databases, roles, and relations stored in the edb_sql_protect and edb_sql_protect_rel tables are no longer valid when these tables are dumped to a backup file and then restored to a new database.

Two functions, export_sqlprotect and import_sqlprotect, are used specifically for backing up and restoring SQL/Protect tables to ensure the OIDs in the SQL/Protect tables reference the correct database objects after the tables are restored.

Backing up the database

Back up a database that was configured with SQL/Protect.

Create a backup file using pg_dump.
This example shows a plain-text backup file named /tmp/edb.dmp created from database edb using the pg_dump utility program:

$ cd /usr/edb/as17/bin
$ ./pg_dump -U enterprisedb -Fp -f /tmp/edb.dmp edb
Password:
$

Connect to the database as a superuser, and export the SQL/Protect data using the export_sqlprotect('sqlprotect_file') function. sqlprotect_file is the fully qualified path to a file where the SQL/Protect data is saved.
The enterprisedb operating system account (postgres if you installed EDB Postgres Advanced Server in PostgreSQL compatibility mode) must have read and write access to the directory specified in sqlprotect_file.

edb=# SELECT sqlprotect.export_sqlprotect('/tmp/sqlprotect.dmp');

Output

 export_sqlprotect
-------------------
(1 row)

The files /tmp/edb.dmp and /tmp/sqlprotect.dmp comprise your total database backup.

Restoring from the backup files

Restore the backup file to the new database.
This example uses the psql utility program to restore the plain-text backup file /tmp/edb.dmp to a newly created database named newdb:

$ /usr/edb/as17/bin/psql -d newdb -U enterprisedb -f /tmp/edb.dmp
Password for user enterprisedb:
SET
SET
SET
SET
SET
COMMENT
CREATE SCHEMA
    .
    .
    .

Connect to the new database as a superuser, and delete all rows from the edb_sql_protect_rel table.
This deletion removes any existing rows in the edb_sql_protect_rel table that were backed up from the original database. These rows don't contain the correct OIDs relative to the database where the backup file was restored.

$ /usr/edb/as17/bin/psql -d newdb -U enterprisedb
Password for user enterprisedb:
psql.bin (17.2.0, server 17.2.0)
Type "help" for help.

newdb=# DELETE FROM sqlprotect.edb_sql_protect_rel;
DELETE 2

Delete all rows from the edb_sql_protect table.
This deletion removes any existing rows in the edb_sql_protect table that were backed up from the original database. These rows don't contain the correct OIDs relative to the database where the backup file was restored.

newdb=# DELETE FROM sqlprotect.edb_sql_protect;
DELETE 1

Delete any of the database's statistics.
This deletion removes any existing statistics for the database to which you're restoring the backup. The following query displays any existing statistics:

newdb=# SELECT * FROM sqlprotect.edb_sql_protect_stats;

Output

 username  | superusers | relations | commands | tautology | dml
-----------+------------+-----------+----------+-----------+-----
(0 rows)

For each row that appears in the preceding query, use the drop_stats function, specifying the role name of the entry.

For example, if a row appeared with appuser in the username column, issue the following command to remove it:

newdb=# SELECT sqlprotect.drop_stats('appuser');

Output

 drop_stats
------------
(1 row)

Delete any of the database's offending queries.
This deletion removes any existing queries for the database to which you're restoring the backup. This query displays any existing queries:

edb=# SELECT * FROM sqlprotect.edb_sql_protect_queries;

Output

 username  | ip_address | port | machine_name | date_time | query
-----------+------------+------+--------------+-----------+-------
(0 rows)

For each row that appears in the preceding query, use the drop_queries function, specifying the role name of the entry. For example, if a row appeared with appuser in the username column, issue the following command to remove it:

edb=# SELECT sqlprotect.drop_queries('appuser');

Output

 drop_queries
--------------
(1 row)

Make sure the role names that were protected by SQL/Protect in the original database are in the database server where the new database resides.
If the original and new databases reside in the same database server, then you don't need to do anything if you didn't delete any of these roles from the database server.
Run the function import_sqlprotect('sqlprotect_file'), where sqlprotect_file is the fully qualified path to the file you created in Step 2 of Backing up the database.

newdb=# SELECT sqlprotect.import_sqlprotect('/tmp/sqlprotect.dmp');

Output

 import_sqlprotect
-------------------
(1 row)

Tables edb_sql_protect and edb_sql_protect_rel are populated with entries containing the OIDs of the database objects as assigned in the new database. The statistics view edb_sql_protect_stats also displays the statistics imported from the original database.

The SQL/Protect tables and statistics are properly restored for this database. Use the following queries on the EDB Postgres Advanced Server system catalogs to verify:

newdb=# SELECT datname, oid FROM pg_database;

Output

  datname  |  oid
-----------+-------
 template1 |     1
 template0 | 13909
 edb       | 13917
 newdb     | 16679
(4 rows)

newdb=# SELECT rolname, oid FROM pg_roles;

Output

   rolname    |  oid
--------------+-------
 enterprisedb |    10
 appuser      | 16671
 newuser      | 16678
(3 rows)

newdb=# SELECT relname, oid FROM pg_class WHERE relname IN
('dept','emp','appuser_tab');

Output

   relname   |  oid
-------------+-------
 appuser_tab | 16803
 dept        | 16809
 emp         | 16812
(3 rows)

newdb=# SELECT * FROM sqlprotect.edb_sql_protect;

Output

 dbid  | roleid | protect_relations | allow_utility_cmds | allow_tautology |
 allow_empty_dml
-------+--------+-------------------+--------------------+-----------------+--
---------------
 16679 |  16671 | t                 | t                  | f               | f
(1 row)

newdb=# SELECT * FROM sqlprotect.edb_sql_protect_rel;

Output

 dbid  | roleid | relid
-------+--------+-------
 16679 |  16671 | 16809
 16679 |  16671 | 16803
(2 rows)

newdb=# SELECT * FROM sqlprotect.edb_sql_protect_stats;

Output

 username | superusers | relations | commands | tautology | dml
----------+------------+-----------+----------+-----------+-----
 appuser  |          0 |         5 |        2 |         1 |   0
(1 row)

newedb=# \x
Expanded display is on.
nwedb=# SELECT * FROM sqlprotect.edb_sql_protect_queries;

Output

-[ RECORD 1 ]+---------------------------------------------
 username     | appuser
 ip_address   |
 port         |
 machine_name |
 date_time    | 20-JUN-14 13:21:00 -04:00
 query        | CREATE TABLE appuser_tab_2 (f1 INTEGER);
-[ RECORD 2 ]+---------------------------------------------
 username     | appuser
 ip_address   |
 port         |
 machine_name |
 date_time    | 20-JUN-14 13:22:00 -04:00
 query        | INSERT INTO appuser_tab_2 VALUES (2);
-[ RECORD 3 ]+---------------------------------------------
 username     | appuser
 ip_address   | 192.168.2.6
 port         | 50098
 machine_name |
 date_time    | 20-JUN-14 13:39:00 -04:00
 query        | CREATE TABLE appuser_tab_3 (f1 INTEGER);
-[ RECORD 4 ]+---------------------------------------------
 username     | appuser
 ip_address   | 192.168.2.6
 port         | 50098
 machine_name |
 date_time    | 20-JUN-14 13:39:00 -04:00
 query        | INSERT INTO appuser_tab_2 VALUES (1);
-[ RECORD 5 ]+---------------------------------------------
 username     | appuser
 ip_address   | 192.168.2.6
 port         | 50098
 machine_name |
 date_time    | 20-JUN-14 13:39:00 -04:00
 query        | SELECT * FROM appuser_tab_2 WHERE 'x' = 'x';

Note the following about the columns in tables edb_sql_protect and edb_sql_protect_rel:

dbid. Matches the value in the oid column from pg_database for newdb.
roleid. Matches the value in the oid column from pg_roles for appuser.
Also, in table edb_sql_protect_rel, the values in the relid column match the values in the oid column of pg_class for relations dept and appuser_tab.

Verify that the SQL/Protect configuration parameters are set as desired in the postgresql.conf file for the database server running the new database. Restart the database server or reload the configuration file as appropriate.

You can now monitor the database using SQL/Protect.

← Prev

Common maintenance operations

↑ Up

Protecting against SQL injection attacks

Generating SSL certificates

Backing up and restoring a SQL/Protect database v17

Note

Object identification numbers in SQL/Protect tables

Backing up the database

Restoring from the backup files

← Prev

↑ Up

Next →