CREATE BLOCK RULE

Function

CREATE BLOCK RULE creates a query filtering rule, including the query filtering rule name, bound client name, client IP address, user, and matching mode. During routine database operations, executing abnormal SQL statements may use too many resources, affecting database performance and stability. You can create query filtering rules to intercept such SQL statements.

Currently, you can specify the regular expression of an SQL statement for interception. For example, intercepting the SELECT statement of a table of a user. You can also specify the value of unique_sql_id or sql_hash of an SQL statement for interception.

This document provides basic SQL syntax. You can also configure the query filtering rules on the console. For details, see "Using DWS Query Filters to Intercept Slow SQL Statements" in the User Guide.

Precautions

• This syntax is supported only by clusters of version 9.1.0.100 or later.
• Only a user with the database owner permission or the gs_role_block role permission can run CREATE BLOCK RULE. A system administrator has this permission by default.
• Common users cannot run CREATE BLOCK RULE. To run it, they must be authorized by the system administrator. The authorization syntax is as follows:

  1	GRANT gs_role_block TO user;

  You are advised to narrow down the application scope when creating a query filtering rule to improve performance.

Syntax

1
2
3
4
5
6

CREATE BLOCK RULE [ IF NOT EXISTS ] block_name
    [ [ TO user_name@'host' ] | [ TO user_name ] | [ TO 'host' ] ] |
    [ FOR UPDATE | SELECT | INSERT | DELETE | MERGE ] |
    FILTER BY
    { SQL ( 'text' ) | TEMPLATE ( template_parameter = value ) }
    [ WITH ( { with_parameter = value }, [, ... ] ) ];

Parameter Description

1

CREATE BLOCK RULE query_block TO user1@'192.168.0.59' FOR SELECT  FILTER BY SQL('test.test');     

1

CREATE BLOCK RULE query_block TO user1 FOR SELECT  FILTER BY SQL('test.test');  

1

CREATE BLOCK RULE query_block TO '192.168.0.59' FOR SELECT  FILTER BY SQL('test.test');  

1

EXPLAIN VERBOSE SELECT * FROM test.test;

Example: Intercept SQL Statements Based on Keywords

1
2

CREATE SCHEMA test;
CREATE TABLE test.src AS SELECT 1;

1
2

DROP TABLE IF EXISTS test.test;
CREATE TABLE test.test(a int, b numeric(15,2)) WITH(orientation=column);

1

INSERT INTO test.test SELECT generate_series(1,20000000) % 1000,generate_series(1,20000000) FROM test.src;

Create user1 and grant the user the permission to access table test.test.

1
2
3

CREATE USER user1 PASSWORD "password";
GRANT USAGE ON SCHEMA test TO user1;
GRANT SELECT ON TABLE test.test TO user1;

Connect to the database as user1 and query table data. The query result shows that user1 can query data in table test.test without creating a filtering rule

1

SELECT sum(b) FROM test.test GROUP BY a;

Connect to the database as a system administrator and create a query filtering rule (query_block) to intercept the SQL statements queried by user1. The filtering method is SQL. If an SQL statement contains the keyword test (table name), the SQL statement is filtered.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE BLOCK RULE query_block 
TO user1@'192.168.0.59'        --The rule applies to user1 and the client IP address is 192.168.0.59.
FOR SELECT                      --Filter SELECT-related operations.
FILTER BY 
SQL('test.test')               --The filtering mode is SQL. Any statements that contain keyword test (table name) are intercepted.
WITH                            --The statements that meet the following conditions are intercepted.
(                             
application_name='gsql',       --The client name is gsql.
table_num='1',                 --The number of base table scans is 1.
estimate_row='10',             --The number of rows in the base table to be scanned is 10.
is_warning='off''               --An error is reported.
)
;

Query the created filtering rule by using the pg_get_blockruledef view. The command output indicates that the rule has taken effect.

1

SELECT * FROM pg_get_blockruledef('query_block');

Switch back to the user, connect to the database, and query the following SQL statement again. If information similar to "the statement is intercepted: ERROR: hit block rule query_block xxx" is displayed, the statement is intercepted.

1

SELECT sum(b) FROM test.test GROUP BY a;

Example: Intercept SQL Statements Based on the unique_sql_id or sql_hash

Continue with the preceding example. Switch back to the system administrator, connect to the database, and run the following command to query unique_sql_id and sql_hash:

1

EXPLAIN VERBOSE  SELECT sum(b) FROM test.test GROUP BY a;

Record unique_sql_id and sql_hash in the command output.

Delete the original filtering rule and create one based on unique_sql_id.

1
2

DROP BLOCK RULE query_block;
CREATE BLOCK RULE query_block FILTER BY TEMPLATE(unique_sql_id='3324222175');

Switch to user1 again. An error is reported and the statement is intercepted.

1

SELECT sum(b) FROM test.test GROUP BY a;

Similarly, execute the statement as the system administrator. The statement is intercepted. It can be concluded that the unique_sql_id is used to filter and intercept SQL statements regardless of users. The SQL statement executed by all users is intercepted, and the interception effect is better.

1

SELECT sum(b) FROM test.test GROUP BY a;

Delete the filtering rule and create one based on sql_hash.

1
2

DROP BLOCK RULE query_block;
CREATE BLOCK RULE query_block FILTER BY TEMPLATE(sql_hash='sql_170d0c75c472043b0cd35e05903c382f');

Switch to user1 again. An error is reported and the statement is intercepted.

Helpful Links

ALTER BLOCK RULE, DROP BLOCK RULE

This document provides basic SQL syntax. You can also configure the query filtering rules on the console. For details, see "Using DWS Query Filters to Intercept Slow SQL Statements" in the User Guide.