postgres 行安全策略

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;