本文共 2411 字,大约阅读时间需要 8 分钟。
Row Security Policies
In addition to the SQL-standard privilege system available through grant, tables can have row security policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands. By default, tables do not have any policies, so that if a user has access privileges to a table according to the SQL privilege system, all rows within it are equally available for queries or updating.Below is a larger example of how this feature can be used in production environments. The table passwd emulates a Unix password file:
-- Simple passwd-file based exampleCREATE TABLE passwd ( user_name text UNIQUE NOT NULL, pwhash text, uid int PRIMARY KEY, gid int NOT NULL, real_name text NOT NULL, home_phone text, extra_info text, home_dir text NOT NULL, shell text NOT NULL);CREATE ROLE admin; -- AdministratorCREATE ROLE bob; -- Normal userCREATE ROLE alice; -- Normal user-- Populate the tableINSERT INTO passwd VALUES ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');INSERT INTO passwd VALUES ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');INSERT INTO passwd VALUES ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');-- Be sure to enable row level security on the tableALTER TABLE passwd ENABLE ROW LEVEL SECURITY;-- Create policies-- Administrator can see all rows and add any rowsCREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);-- Normal users can view all rowsCREATE POLICY all_view ON passwd FOR SELECT USING (true);-- Normal users can update their own records, but-- limit which shells a normal user is allowed to setCREATE POLICY user_mod ON passwd FOR UPDATE USING (current_user = user_name) WITH CHECK ( current_user = user_name AND shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh') );-- Allow admin all normal rightsGRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;-- Users only get select access on public columnsGRANT SELECT (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell) ON passwd TO public;-- Allow users to update certain columnsGRANT UPDATE (pwhash, real_name, home_phone, extra_info, shell) ON passwd TO public;
转载地址:http://weyvb.baihongyu.com/