<commit_before>---
author: <NAME>
lastmod: 2014-10-03
date: 2014-10-03 14:20:36+00:00
layout: post
type: post
slug: postgres-9-5-feature-highlight-row-level-security
title: 'Postgres 9.5 feature highlight - Row-Level Security and Policies'
categories:
- PostgreSQL-2
tags:
- postgres
- postgresql
- open source
- database
- development
- 9.5
- new
- feature
- row
- level
- tuple
- security
- check
- user
- policy

---

[Row-level security]
(http://www.postgresql.org/docs/devel/static/ddl-rowsecurity.html) is
a new feature of PostgreSQL 9.5 that has been introduced by this commit:

    commit: 491c029dbc4206779cf659aa0ff986af7831d2ff
    author: <NAME> <<EMAIL>>
    date: Fri, 19 Sep 2014 11:18:35 -0400
    Row-Level Security Policies (RLS)

    Building on the updatable security-barrier views work, add the
    ability to define policies on tables to limit the set of rows
    which are returned from a query and which are allowed to be added
    to a table.  Expressions defined by the policy for filtering are
    added to the security barrier quals of the query, while expressions
    defined to check records being added to a table are added to the
    with-check options of the query.

Behind this jargon is a feature that could be defined in short words as
a complementary permission manager of [GRANT]
(http://www.postgresql.org/docs/devel/static/sql-grant.html) and
[REVOKE](http://www.postgresql.org/docs/devel/static/sql-revoke.html)
that allows controlling at row level which tuples can be retrieved
for a read query or manipulated using INSERT, UPDATE or DELETE.
This row control mechanism is controlled using a new query called
[CREATE POLICY]
(http://www.postgresql.org/docs/devel/static/sql-createpolicy.html)
(of course its flavor [ALTER POLICY]
(http://www.postgresql.org/docs/devel/static/sql-alterpolicy.html)
to update an existing policy and [DROP POLICY]
(http://www.postgresql.org/docs/devel/static/sql-droppolicy.html)
to remove a policy exist as well). By default, tables have no
restrictions in terms of how rows can be added and manipulated.
However they can be made able to accept level restriction policies
using [ALTER TABLE]
(http://www.postgresql.org/docs/devel/static/sql-altertable.html)
and ENABLE ROW LEVEL SECURITY. Now, let's imagine the following table
where a list of employees and their respective salaries can be read
(salary is an integer as this is entirely fictive situation and refers
to no real situation, quoique...):

    =# CREATE TABLE employee_data (id int,
           employee text,
           salary int,
           phone_number text);
    CREATE TABLE
    =# CREATE ROLE ceo;<commit_msg>Fix some links on RLS article<commit_after>---
author: <NAME>
lastmod: 2014-10-03
date: 2014-10-03 14:20:36+00:00
layout: post
type: post
slug: postgres-9-5-feature-highlight-row-level-security
title: 'Postgres 9.5 feature highlight - Row-Level Security and Policies'
categories:
- PostgreSQL-2
tags:
- postgres
- postgresql
- open source
- database
- development
- 9.5
- new
- feature
- row
- level
- tuple
- security
- check
- user
- policy

---

[Row-level security](http://www.postgresql.org/docs/devel/static/ddl-rowsecurity.html) is
a new feature of PostgreSQL 9.5 that has been introduced by this commit:

    commit: 491c029dbc4206779cf659aa0ff986af7831d2ff
    author: <NAME> <<EMAIL>>
    date: Fri, 19 Sep 2014 11:18:35 -0400
    Row-Level Security Policies (RLS)

    Building on the updatable security-barrier views work, add the
    ability to define policies on tables to limit the set of rows
    which are returned from a query and which are allowed to be added
    to a table.  Expressions defined by the policy for filtering are
    added to the security barrier quals of the query, while expressions
    defined to check records being added to a table are added to the
    with-check options of the query.

Behind this jargon is a feature that could be defined in short words as
a complementary permission manager of [GRANT](http://www.postgresql.org/docs/devel/static/sql-grant.html) and
[REVOKE](http://www.postgresql.org/docs/devel/static/sql-revoke.html)
that allows controlling at row level which tuples can be retrieved
for a read query or manipulated using INSERT, UPDATE or DELETE.
This row control mechanism is controlled using a new query called
[CREATE POLICY](http://www.postgresql.org/docs/devel/static/sql-createpolicy.html)
(of course its flavor [ALTER POLICY](http://www.postgresql.org/docs/devel/static/sql-alterpolicy.html)
to update an existing policy and [DROP POLICY](http://www.postgresql.org/docs/devel/static/sql-droppolicy.html)
to remove a policy exist as well). By default, tables have no
restrictions in terms of how rows can be added and manipulated.
However they can be made able to accept level restriction policies
using [ALTER TABLE](http://www.postgresql.org/docs/devel/static/sql-altertable.html)
and ENABLE ROW LEVEL SECURITY. Now, let's imagine the following table
where a list of employees and their respective salaries can be read
(salary is an integer as this is entirely fictive situation and refers
to no real situation, quoique...):

    =# CREATE TABLE employee_data (id int,
           employee text,
           salary int,
           phone_number text);
    CREATE TABLE
    =# CREATE ROLE ceo;
{'fraction_non_alphanumeric': 0.08135593220338982, 'fraction_numerical': 0.02862523540489642, 'mean_word_length': 3.7760791366906474, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 14, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6274750', 'n_tokens_mistral': 1733, 'n_tokens_neox': 1571, 'n_words': 634}