diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 111 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_policy.sgml | 2 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_policy.sgml | 6 |
3 files changed, 114 insertions, 5 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 98e897aeb6c..f9dc151a0cc 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1615,7 +1615,7 @@ REVOKE ALL ON accounts FROM PUBLIC; and foreign key references, will bypass row security to ensure that data integrity is maintained. Care must be taken when developing schemas and row level policies to avoid a "covert channel" leak of - information through these referntial integrity checks. + information through these referential integrity checks. </para> <para> @@ -1674,6 +1674,115 @@ CREATE POLICY user_policy ON users normal privileges system. </para> + <para> + Below is a larger example of how this feature can be used in + production environments, based on a unix password file. + </para> + +<programlisting> +-- Simple passwd-file based example +CREATE TABLE passwd ( + username 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; -- Administrator +CREATE ROLE bob; -- Normal user +CREATE ROLE alice; -- Normal user + +-- Populate the table +INSERT 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 table +ALTER TABLE passwd ENABLE ROW LEVEL SECURITY; + +-- Create policies +-- Administrator can see all rows and add any rows +CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true); +-- Normal users can view all rows +CREATE 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 set +CREATE POLICY user_mod ON passwd FOR UPDATE + USING (current_user = username) + WITH CHECK ( + current_user = username AND + shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh') + ); + +-- Allow admin all normal rights +GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin; +-- Users only get select access on public columns +GRANT SELECT + (username, uid, gid, real_name, home_phone, extra_info, home_dir, shell) + ON passwd TO public; +-- Allow users to update certain columns +GRANT UPDATE + (pwhash, real_name, home_phone, extra_info, shell) + ON passwd TO public; +</programlisting> + + <para> + As with any security settings, it's important to test and ensure that + the system is behaving as expected. Using the example above, this + demonstrates that the permission system is working properly. + </para> + +<programlisting> +-- admin can view all rows and fields +postgres=> set role admin; +SET +postgres=> table passwd; + username | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell +----------+--------+-----+-----+-----------+--------------+------------+-------------+----------- + admin | xxx | 0 | 0 | Admin | 111-222-3333 | | /root | /bin/dash + bob | xxx | 1 | 1 | Bob | 123-456-7890 | | /home/bob | /bin/zsh + alice | xxx | 2 | 1 | Alice | 098-765-4321 | | /home/alice | /bin/zsh +(3 rows) + +-- Test what Alice is able to do +postgres=> set role alice; +SET +postgres=> table passwd; +ERROR: permission denied for relation passwd +postgres=> select username,real_name,home_phone,extra_info,home_dir,shell from passwd; + username | real_name | home_phone | extra_info | home_dir | shell +----------+-----------+--------------+------------+-------------+----------- + admin | Admin | 111-222-3333 | | /root | /bin/dash + bob | Bob | 123-456-7890 | | /home/bob | /bin/zsh + alice | Alice | 098-765-4321 | | /home/alice | /bin/zsh +(3 rows) + +postgres=> update passwd set username = 'joe'; +ERROR: permission denied for relation passwd +-- Allowed to change her own real_name, but no others +postgres=> update passwd set real_name = 'Alice Doe'; +UPDATE 1 +postgres=> update passwd set real_name = 'John Doe' where username = 'admin'; +UPDATE 0 +postgres=> update passwd set shell = '/bin/xx'; +ERROR: new row violates WITH CHECK OPTION for "passwd" +postgres=> delete from passwd; +ERROR: permission denied for relation passwd +postgres=> insert into passwd (username) values ('xxx'); +ERROR: permission denied for relation passwd +-- Alice can change her own password +postgres=> update passwd set pwhash = 'abc'; +UPDATE 1 +</programlisting> + </sect1> <sect1 id="ddl-schemas"> diff --git a/doc/src/sgml/ref/alter_policy.sgml b/doc/src/sgml/ref/alter_policy.sgml index 37615fcab5d..ab717f31c51 100644 --- a/doc/src/sgml/ref/alter_policy.sgml +++ b/doc/src/sgml/ref/alter_policy.sgml @@ -94,7 +94,7 @@ ALTER POLICY <replaceable class="parameter">name</replaceable> ON <replaceable c security-barrier qualification to queries which use the table automatically. If multiple policies are being applied for a given table then they are all combined and added using OR. The USING - expression applies to records which are being retrived from the table. + expression applies to records which are being retrieved from the table. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 3c5bdc69cdc..eff062c114f 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -81,7 +81,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable referenced table. These issues can be addressed by carefully crafting policies which prevent users from being able to insert, delete, or update records at all which might possibly indicate a value they are not otherwise - able to see, or by using generated values (eg: surrogate keys) instead. + able to see, or by using generated values (e.g.: surrogate keys) instead. </para> <para> @@ -218,7 +218,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable records from the relation which pass the <literal>SELECT</literal> policy will be returned, even if other records exist in the relation. The <literal>SELECT</literal> policy only accepts the USING expression - as it only ever applies in cases where records are being retrived from + as it only ever applies in cases where records are being retrieved from the relation. </para> </listitem> @@ -272,7 +272,7 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable for the <literal>DELETE</literal>, and rows which are not visible through the <literal>SELECT</literal> policy may be deleted if they pass the <literal>DELETE</literal> USING policy. The - <literal>DELETE</literal> policy only accept the USING expression as + <literal>DELETE</literal> policy only accepts the USING expression as it only ever applies in cases where records are being extracted from the relation for deletion. </para> |