Common Maintenance Operations v10
The following describes how to perform other common operations.
You must be connected as a superuser to perform these operations and have included the sqlprotect schema in your search path.
Adding a Role to the Protected Roles List
To add a role to the protected roles list run protect_role('rolename') as shown in the following example:
edb=# SELECT protect_role('newuser');
protect_role
--------------
(1 row)Removing a Role From the Protected Roles List
To remove a role from the protected roles list, use either of the following functions:
unprotect_role('rolename')
unprotect_role(roleoid)The variation of the function using the OID is useful if you remove the role using the DROP ROLE or DROP USER SQL statement before removing the role from the protected roles list. If a query on a SQL/Protect relation returns a value such as unknown (OID=16458) for the user name, use the unprotect_role(roleoid) form of the function to remove the entry for the deleted role from the protected roles list.
Removing a role using these functions also removes the role’s protected relations list.
The statistics for a role that has been removed are not deleted until you use the drop_stats function.
The offending queries for a role that has been removed are not deleted until you use the drop_queries function.
The following is an example of the unprotect_role function:
edb=# SELECT unprotect_role('newuser');
unprotect_role
----------------
(1 row)Alternatively, the role could be removed by giving its OID of 16693:
edb=# SELECT unprotect_role(16693); unprotect_role ---------------- (1 row)
Setting the Types of Protection for a Role
You can change whether or not a role is protected from a certain type of SQL injection attack.
Change the Boolean value for the column in edb_sql_protect corresponding to the type of SQL injection attack for which protection of a role is to be disabled or enabled.
Be sure to qualify the following columns in your WHERE clause of the statement that updates edb_sql_protect:
- dbid. OID of the database for which you are making the change
- roleid. OID of the role for which you are changing the Boolean settings
For example, to allow a given role to issue utility commands, update the allow_utility_cmds column as follows:
UPDATE edb_sql_protect SET allow_utility_cmds = TRUE WHERE dbid = 13917 AND roleid = 16671;
You can verify the change was made by querying edb_sql_protect or list_protected_users. In the following query note that column allow_utility_cmds now contains t:
edb=# SELECT dbid, roleid, allow_utility_cmds FROM edb_sql_protect; dbid | roleid | allow_utility_cmds --------+--------+-------------------- 13917 | 16671 | t (1 row)
The updated rules take effect on new sessions started by the role since the change was made.
Removing a Relation From the Protected Relations List
If SQL/Protect has learned that a given relation is accessible for a given role, you can subsequently remove that relation from the role’s protected relations list.
Delete its entry from the edb_sql_protect_rel table using any of the following functions:
unprotect_rel('rolename', 'relname')
unprotect_rel('rolename', 'schema', 'relname')
unprotect_rel(roleoid, reloid)If the relation given by relname is not in your current search path, specify the relation’s schema using the second function format.
The third function format allows you to specify the OIDs of the role and relation, respectively, instead of their text names.
The following example illustrates the removal of the public.emp relation from the protected relations list of the role appuser:
edb=# SELECT unprotect_rel('appuser', 'public', 'emp');
unprotect_rel
---------------
(1 row)The following query shows there is no longer an entry for the emp relation:
edb=# SELECT * FROM list_protected_rels; Database | Protected User | Schema | Name | Type | Owner ----------+----------------+--------+-------------+-------+-------------- edb | appuser | public | dept | Table | enterprisedb edb | appuser | public | appuser_tab | Table | appuser (2 rows)
SQL/Protect will now issue a warning or completely block access (depending upon the setting of edb_sql_protect.level) whenever the role attempts to utilize that relation.
Deleting Statistics
You can delete statistics from view edb_sql_protect_stats using either of the two following functions:
drop_stats('rolename')
drop_stats(roleoid)The variation of the function using the OID is useful if you remove the role using the DROP ROLE or DROP USER SQL statement before deleting the role’s statistics using drop_stats('rolename'). If a query on edb_sql_protect_stats returns a value such as unknown (OID=16458) for the user name, use the drop_stats(roleoid) form of the function to remove the deleted role’s statistics from edb_sql_protect_stats.
The following is an example of the drop_stats function:
edb=# SELECT drop_stats('appuser');
drop_stats
------------
(1 row)
edb=# SELECT * FROM edb_sql_protect_stats;
username | superusers | relations | commands | tautology | dml
-----------+------------+-----------+----------+-----------+-----
(0 rows)The following is an example of using the drop_stats(roleoid) form of the function when a role is dropped before deleting its statistics:
edb=# SELECT * FROM edb_sql_protect_stats;
username | superusers | relations | commands | tautology | dml
---------------------+------------+-----------+----------+-----------+-----
unknown (OID=16693) | 0 | 5 | 3 | 1 | 0
appuser | 0 | 5 | 2 | 1 | 0
(2 rows)
edb=# SELECT drop_stats(16693);
drop_stats
------------
(1 row)
edb=# SELECT * FROM edb_sql_protect_stats;
username | superusers | relations | commands | tautology | dml
----------+------------+-----------+----------+-----------+-----
appuser | 0 | 5 | 2 | 1 | 0
(1 row)Deleting Offending Queries
You can delete offending queries from view edb_sql_protect_queries using either of the two following functions:
drop_queries('rolename')
drop_queries(roleoid)The variation of the function using the OID is useful if you remove the role using the DROP ROLE or DROP USER SQL statement before deleting the role’s offending queries using drop_queries('rolename'). If a query on edb_sql_protect_queries returns a value such as unknown (OID=16454) for the user name, use the drop_queries(roleoid) form of the function to remove the deleted role’s offending queries from edb_sql_protect_queries.
The following is an example of the drop_queries function:
edb=# SELECT drop_queries('appuser');
drop_queries
--------------
5
(1 row)
edb=# SELECT * FROM edb_sql_protect_queries;
username | ip_address | port | machine_name | date_time | query
-----------+------------+------+--------------+-----------+-------
(0 rows)The following is an example of using the drop_queries(roleoid) form of the function when a role is dropped before deleting its queries:
edb=# SELECT username, query FROM edb_sql_protect_queries;
username | query
---------------------+----------------------------------------------
unknown (OID=16454) | CREATE TABLE appuser_tab_2 (f1 INTEGER);
unknown (OID=16454) | INSERT INTO appuser_tab_2 VALUES (2);
unknown (OID=16454) | CREATE TABLE appuser_tab_3 (f1 INTEGER);
unknown (OID=16454) | INSERT INTO appuser_tab_2 VALUES (1);
unknown (OID=16454) | SELECT * FROM appuser_tab_2 WHERE 'x' = 'x';
(5 rows)
edb=# SELECT drop_queries(16454);
drop_queries
--------------
5
(1 row)
edb=# SELECT * FROM edb_sql_protect_queries;
username | ip_address | port | machine_name | date_time | query
----------+------------+------+--------------+-----------+-------
(0 rows)Disabling and Enabling Monitoring
If you wish to turn off SQL/Protect monitoring, modify the postgresql.conf file, setting the edb_sql_protect.enabled parameter to off. After saving the file, reload the server configuration to apply the settings.
If you wish to turn on SQL/Protect monitoring, modify the postgresql.conf file, setting the edb_sql_protect.enabled parameter to on. After saving the file, reload the server configuration to apply the settings.