Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

CREATE ROW LEVEL SECURITY POLICY

Updated on 2024-05-07 GMT+08:00

Description

Creates a row-level security policy for a table.

The policy takes effect only after row-level security is enabled (by running ALTER TABLE ... ENABLE ROW LEVEL SECURITY). Otherwise, this statement does not take effect.

Currently, row-level security affects the read (SELECT, UPDATE, and DELETE) of data tables and does not affect the write (INSERT and MERGE INTO) of data tables. The table owner or system administrators can create an expression in the USING clause. When the client reads the data table, the database server combines the expressions that meet the condition and applies it to the execution plan in the statement rewriting phase of a query. For each tuple in a data table, if the expression returns TRUE, the tuple is visible to the current user; if the expression returns FALSE or NULL, the tuple is invisible to the current user.

A row-level security policy name is specific to a table. A data table cannot have row-level security policies with the same name. Different data tables can have the same row-level security policy.

Row-level security policies can be applied to specified operations (SELECT, UPDATE, DELETE, and ALL). ALL indicates that SELECT, UPDATE, and DELETE will be affected. For a new row-level security policy, the default value ALL will be used if you do not specify the operations that will be affected.

Row-level security policies can be applied to a specified user (role) or to all users (PUBLIC). For a new row-level security policy, the default value PUBLIC will be used if you do not specify the user that will be affected.

Precautions

  • Row-level security policies can be defined for row-store tables, row-store partitioned tables, unlogged tables, and hash tables.
  • Row-level security policies cannot be defined for foreign tables and local temporary tables.
  • Row-level security policies cannot be defined for views.
  • A maximum of 100 row-level security policies can be defined for a table.
  • System administrators are not affected by row-level security policies and can view all data in a table.
  • Tables queried by using SQL statements, views, functions, and stored procedures are affected by row-level security policies.
  • The data type of a table column to which a row-level security policy has been added cannot be changed.

Syntax

CREATE [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name
     [ AS { PERMISSIVE | RESTRICTIVE } ]
     [ FOR { ALL | SELECT | UPDATE | DELETE } ]
     [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
     USING ( using_expression )

Parameters

  • policy_name

    Specifies the name of a row-level security policy to be created. The names of row-level security policies for a table must be unique.

  • table_name

    Specifies the name of a table to which a row-level security policy is applied.

  • PERMISSIVE | RESTRICTIVE

    PERMISSIVE enables the permissive policy for row-level security. The conditions of the permissive policy are joined through the OR expression.

    RESTRICTIVE enables the restrictive policy for row-level security. The conditions of the restrictive policy are joined through the AND expression. The join methods are as follows:

    (using_expression_permissive_1 OR using_expression_permissive_2 ...) AND (using_expression_restrictive_1 AND using_expression_restrictive_2 ...)

    The default value is PERMISSIVE.

  • command

    Specifies the SQL operations affected by a row-level security policy, including ALL, SELECT, UPDATE, and DELETE. If this parameter is not specified, the default value ALL will be used, covering SELECT, UPDATE, and DELETE.

    If command is set to SELECT, only tuple data that meets the condition (the return value of using_expression is TRUE) can be queried. The operations that are affected include SELECT, SELECT FOR UPDATE/SHARE, UPDATE ... RETURNING, and DELETE ... RETURNING.

    If command is set to UPDATE, only tuple data that meets the condition (the return value of using_expression is TRUE) can be updated. The operations that are affected include UPDATE, UPDATE ... RETURNING, and SELECT ... FOR UPDATE/SHARE.

    If command is set to DELETE, only tuple data that meets the condition (the return value of using_expression is TRUE) can be deleted. The operations that are affected include DELETE and DELETE ... RETURNING.

    The following table describes the relationship between row-level security policies and SQL statements.

    Table 1 Relationship between row-level security policies and SQL statements.

    Command

    SELECT/ALL Policy

    UPDATE/ALL Policy

    DELETE/ALL Policy

    SELECT

    Existing row

    No

    No

    SELECT FOR UPDATE/SHARE

    Existing row

    Existing row

    No

    UPDATE

    No

    Existing row

    No

    UPDATE RETURNING

    Existing row

    Existing row

    No

    DELETE

    No

    No

    Existing row

    DELETE RETURNING

    Existing row

    No

    Existing row

  • role_name

    Specifies database users affected by a row-level security policy.

    CURRENT_USER indicates the username in the current operating environment. SESSION_USER indicates the session username. If this parameter is not specified, the default value PUBLIC is used, indicating that all database users are affected. You can specify multiple affected database users.

    NOTICE:

    System administrators are not affected by row access control.

  • using_expression

    Specifies an expression defined for a row-level security policy (return type: Boolean).

    The expression cannot contain aggregate functions or window functions. In the statement rewriting phase of a query, if row-level security for a data table is enabled, the expressions that meet the specified conditions will be added to the plan tree. The expression is calculated for each tuple in the data table. For SELECT, UPDATE, and DELETE, row data is visible to the current user only when the return value of the expression is TRUE. If the expression returns FALSE, the tuple is invisible to the current user. In this case, the user cannot view the tuple through the SELECT statement, update the tuple through the UPDATE statement, or delete the tuple through the DELETE statement.

Examples

-- Create user alice.
gaussdb=# CREATE USER alice PASSWORD '********';

-- Create user bob.
gaussdb=# CREATE USER bob PASSWORD '********';

-- Create data table all_data.
gaussdb=# CREATE TABLE public.all_data(id int, role varchar(100), data varchar(100));

-- Insert data into the data table.
gaussdb=# INSERT INTO all_data VALUES(1, 'alice', 'alice data');
gaussdb=# INSERT INTO all_data VALUES(2, 'bob', 'bob data');
gaussdb=# INSERT INTO all_data VALUES(3, 'peter', 'peter data');

-- Grant the read permission on the all_data table to users alice and bob.
gaussdb=# GRANT SELECT ON all_data TO alice, bob;

-- Enable the row-level security policy.
gaussdb=# ALTER TABLE all_data ENABLE ROW LEVEL SECURITY;

-- Create a row-level security policy to specify that the current user can view only their own data.
gaussdb=# CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER);

-- View information about the all_data table.
gaussdb=# \d+ all_data
                               Table "public.all_data"
 Column |          Type          | Modifiers | Storage  | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
 id     | integer                |           | plain    |              |
 role   | character varying(100) |           | extended |              |
 data   | character varying(100) |           | extended |              |
Row Level Security Policies:
    POLICY "all_data_rls" FOR ALL
      TO public
      USING (((role)::name = "current_user"()))
Has OIDs: no
Options: orientation=row, compression=no, enable_rowsecurity=true

-- Run SELECT.
gaussdb=# SELECT * FROM all_data;
 id | role  |    data
----+-------+------------
  1 | alice | alice data
  2 | bob   | bob data
  3 | peter | peter data
(3 rows)

gaussdb=# EXPLAIN(COSTS OFF) SELECT * FROM all_data;
      QUERY PLAN
----------------------
 Seq Scan on all_data
(1 row)

-- Switch to user alice and run SELECT.
gaussdb=# SELECT * FROM all_data;
 id | role  |    data
----+-------+------------
  1 | alice | alice data
(1 row)

gaussdb=# EXPLAIN(COSTS OFF) SELECT * FROM all_data;
 QUERY PLAN
----------------------------------------------------------------
 Seq Scan on all_data
   Filter: ((role)::name = 'alice'::name)
 Notice: This query is influenced by row level security feature
(3 rows)

-- Delete a row-level security policy.
gaussdb=# DROP ROW LEVEL SECURITY POLICY all_data_rls ON all_data;

-- Delete the all_data table.
gaussdb=# DROP TABLE public.all_data;

-- Delete users alice and bob.
gaussdb=# DROP USER alice, bob;

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback