Common maintenance operations v17

You must be connected as a superuser to perform these operations. Include the sqlprotect schema in your search path.

Adding a role to the protected roles list

Add a role to the protected roles list. Run protect_role('rolename'), as shown in this 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.

To delete the statistics for a role that was removed, use the drop_stats function.

To delete the offending queries for a role that was removed, use the drop_queries function.

This example shows the unprotect_role function:

edb=# SELECT unprotect_role('newuser');

 unprotect_role
----------------
(1 row)

Alternatively, you can remove the role 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 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 you want to enable or disable protection of a role.

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're making the change.
• roleid. OID of the role for which you're changing the Boolean settings

For example, to allow a given role to issue utility commands, update the allow_utility_cmds column:

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 learns that a given relation is accessible for a given role, you can later remove that relation from the role’s protected relations list.

Delete the 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 isn't 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.

This example removes the public.emp relation from the protected relations list of the role appuser:

edb=# SELECT unprotect_rel('appuser', 'public', 'emp');

 unprotect_rel
---------------
(1 row)

This query shows there's 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 now issues a warning or completely blocks access (depending on the setting of edb_sql_protect.level) when the role attempts to use that relation.

Deleting statistics

You can delete statistics from the view edb_sql_protect_stats using either of the following functions:

drop_stats('rolename')

drop_stats(roleoid)

The form 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.

This example shows 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)

This example uses 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 the view edb_sql_protect_queries using either of the 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.

This example shows 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)

This example uses 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 want 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 want to turn on SQL/Protect monitoring, modify the postgresql.conf file, setting the edb_sql_protect.enabled parameter to on. Save the file, and then reload the server configuration to apply the settings.