3.1.4 Backing Up and Restoring a SQL/Protect Database

Table of Contents Previous Next


3 Security : 3.1 Protecting Against SQL Injection Attacks : 3.1.4 Backing Up and Restoring a SQL/Protect Database

Note: This section is applicable if your backup and restore procedures result in the re-creation of database objects in the new database with new OIDs such as is the case when using the pg_dump backup program.
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, and not the objects’ text names. The OID is a numeric data type used by Advanced Server to uniquely identify each database object.
The following sections describe two functions, export_sqlprotect and import_sqlprotect, that are used specifically for backing up and restoring SQL/Protect tables in order to ensure the OIDs in the SQL/Protect tables reference the correct database objects after the SQL/Protect tables are restored.
Step 1: Create a backup file using pg_dump.
The following example shows a plain-text backup file named /tmp/edb.dmp created from database edb using the pg_dump utility program:
Step 2: Connect to the database as a superuser and export the SQL/Protect data using the export_sqlprotect('sqlprotect_file') function where sqlprotect_file is the fully qualified path to a file where the SQL/Protect data is to be saved.
The enterprisedb operating system account (postgres if you installed Advanced Server in PostgreSQL compatibility mode) must have read and write access to the directory specified in sqlprotect_file.
The files /tmp/edb.dmp and /tmp/sqlprotect.dmp comprise your total database backup.
Step 1: Restore the backup file to the new database.
The following example uses the psql utility program to restore the plain-text backup file /tmp/edb.dmp to a newly created database named newdb:
Step 2: Connect to the new database as a superuser and delete all rows from the edb_sql_protect_rel table.
This step removes any existing rows in the edb_sql_protect_rel table that were backed up from the original database. These rows do not contain the correct OIDs relative to the database where the backup file has been restored.
Step 3: Delete all rows from the edb_sql_protect table.
This step removes any existing rows in the edb_sql_protect table that were backed up from the original database. These rows do not contain the correct OIDs relative to the database where the backup file has been restored.
Step 4: Delete any statistics that may exist for the database.
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:
Step 5: Delete any offending queries that may exist for the database.
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:
Step 6: Make sure the role names that were protected by SQL/Protect in the original database exist in the database server where the new database resides.
Step 7: Run the function import_sqlprotect('sqlprotect_file') where sqlprotect_file is the fully qualified path to the file you created in Step 2 of Section 3.1.4.2.
Tables edb_sql_protect and edb_sql_protect_rel are now populated with entries containing the OIDs of the database objects as assigned in the new database. The statistics view edb_sql_protect_stats also now displays the statistics imported from the original database.
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 note that 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.
Step 8: 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.

3 Security : 3.1 Protecting Against SQL Injection Attacks : 3.1.4 Backing Up and Restoring a SQL/Protect Database

Table of Contents Previous Next