Home Explore Blog CI



postgresql

35th chunk of `doc/src/sgml/ddl.sgml`
ec297edcc25fc596c0679d34b5ffe274cd9a70625fb81cae0000000100000fa9
 normal user is allowed to set
CREATE 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 rights
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Users only get select access on public columns
GRANT SELECT
  (user_name, 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=&gt; set role admin;
SET
postgres=&gt; table passwd;
 user_name | 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=&gt; set role alice;
SET
postgres=&gt; table passwd;
ERROR:  permission denied for table passwd
postgres=&gt; select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | 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=&gt; update passwd set user_name = 'joe';
ERROR:  permission denied for table passwd
-- Alice is allowed to change her own real_name, but no others
postgres=&gt; update passwd set real_name = 'Alice Doe';
UPDATE 1
postgres=&gt; update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
postgres=&gt; update passwd set shell = '/bin/xx';
ERROR:  new row violates WITH CHECK OPTION for "passwd"
postgres=&gt; delete from passwd;
ERROR:  permission denied for table passwd
postgres=&gt; insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for table passwd
-- Alice can change her own password; RLS silently prevents updating other rows
postgres=&gt; update passwd set pwhash = 'abc';
UPDATE 1
</programlisting>

  <para>
   All of the policies constructed thus far have been permissive policies,
   meaning that when multiple policies are applied they are combined using
   the <quote>OR</quote> Boolean operator.  While permissive policies can be constructed
   to only allow access to rows in the intended cases, it can be simpler to
   combine permissive policies with restrictive policies (which the records
   must pass and which are combined using the <quote>AND</quote> Boolean operator).
   Building on the example above, we add a restrictive policy to require
   the administrator to be connected over a local Unix socket to access the
   records of the <literal>passwd</literal> table:
  </para>

<programlisting>
CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
    USING (pg_catalog.inet_client_addr() IS NULL);
</programlisting>

  <para>
   We can then see that an administrator connecting over a network will not
   see any records, due to the restrictive policy:
  </para>

<programlisting>
=&gt; SELECT current_user;
 current_user
--------------
 admin
(1 row)

=&gt; select inet_client_addr();
 inet_client_addr
------------------

Title: Testing Row-Level Security, Restrictive Policies
Summary
This section demonstrates testing row-level security (RLS) by setting roles and performing various operations on the 'passwd' table, like viewing rows, updating fields, and attempting unauthorized actions. It shows how policies restrict users from accessing or modifying data outside their permissions. Additionally, it introduces restrictive policies, combined with permissive policies to enhance security. It presents an example where an administrator is restricted to accessing the 'passwd' table only when connected via a local Unix socket.