<title>Row Security Policies</title>

<indexterm zone="ddl-rowsecurity">

<primary>row security</primary>

<primary>row-level security</primary>

</indexterm>

<indexterm zone="ddl-rowsecurity">

<primary>policy</primary>

</indexterm>

<para>

In addition to the <xref linkend="ddl-priv"> system available through

<xref linkend="sql-grant">, tables can have row security policies

which limit the rows returned for normal queries and rows which can

be added through data modification commands. By default, tables do

not have any policies and all rows are visible and able to be added,

subject to the regular <xref linkend="ddl-priv"> system. This is

also known as Row Level Security.

In addition to the SQL-standard <link linkend="ddl-priv">privilege

system</link> available through <xref linkend="sql-grant">,

tables can have <firstterm>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.

This feature is also known as <firstterm>Row-Level Security</>.

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 querying or updating.

</para>

<para>

When row security is enabled on a table with

<xref linkend="sql-altertable">, all normal access to the table

(excluding the owner) for selecting rows or adding rows must be through

a policy. If no policy exists for the table, a default-deny policy is

used and no rows are visible or can be added. Privileges which operate

at the whole table level such as <literal>TRUNCATE</>, and

<literal>REFERENCES</> are not subject to row security.

When row security is enabled on a table (with

<link linkend="sql-altertable">ALTER TABLE ... ENABLE ROW LEVEL

SECURITY</>), all normal access to the table for selecting rows or

modifying rows must be allowed by a row security policy. (However, the

table's owner is typically not subject to row security policies.) If no

policy exists for the table, a default-deny policy is used, meaning that

no rows are visible or can be modified. Operations that apply to the

whole table, such as <command>TRUNCATE</> and <literal>REFERENCES</>,

are not subject to row security.

</para>

<para>

Row security policies can be specific to commands, or to roles, or to

both. The commands available are <literal>ALL</literal>,

<literal>SELECT</>, <literal>INSERT</>, <literal>UPDATE</>, and

<literal>DELETE</>. Multiple roles can be assigned to a given policy and

normal role membership and inheritance rules apply. Table owners,

superusers, and roles with the <literal>BYPASSRLS</> attribute bypass the

row security system when querying a table. Applications that expect to

bypass all row security through those mechanisms should

set <xref linkend="guc-row-security"> to <literal>off</>.

both. A policy can be specified to apply to <literal>ALL</literal>

commands, or to <literal>SELECT</>, <literal>INSERT</>, <literal>UPDATE</>,

or <literal>DELETE</>. Multiple roles can be assigned to a given

policy, and normal role membership and inheritance rules apply.

</para>

<para>

To specify which rows are visible and what rows can be added to the

table with row level security, an expression is required which returns

a Boolean result. This expression will be evaluated for each row prior

to other conditionals or functions which are part of the query. The

one exception to this rule are <literal>leakproof</literal> functions,

which are guaranteed to not leak information. Two expressions may be

specified to provide independent control over the rows which are

visible and the rows which are allowed to be added. The expression

is run as part of the query and with the privileges of the user

running the query, however, security definer functions can be used in

the expression.

To specify which rows are visible or modifiable according to a policy,

an expression is required that returns a Boolean result. This

expression will be evaluated for each row prior to any conditions or

functions coming from the user's query. (The only exceptions to this

rule are <literal>leakproof</literal> functions, which are guaranteed to

not leak information; the optimizer may choose to apply such functions

ahead of the row-security check.) Rows for which the expression does

not return <literal>true</> will not be processed. Separate expressions

may be specified to provide independent control over the rows which are

visible and the rows which are allowed to be modified. Policy

expressions are run as part of the query and with the privileges of the

user running the query, although security-definer functions can be used

to access data not available to the calling user.

</para>

<para>

Superusers and roles with the <literal>BYPASSRLS</> attribute always

bypass the row security system when accessing a table. Table owners

normally bypass row security as well, though a table owner can choose to

be subject to row security with <link linkend="sql-altertable">ALTER

TABLE ... FORCE ROW LEVEL SECURITY</>. Even in a table with that option

selected, the table owner will bypass row security if the

<xref linkend="guc-row-security"> configuration parameter is set

to <literal>off</>; this setting is typically used for purposes such as

backup and restore.

</para>

<para>

Enabling and disabling row security, as well as adding policies to a

table, is always the privilege of the owner only.

table, is always the privilege of thetableowner only.

</para>

<para>

<para>

When multiple policies apply to a given query, they are combined using

<literal>OR</literal>, similar to how a given role has the privileges

of all roles which they are a member of.

<literal>OR</literal>, so that a row is accessible if any policy allows

it. This is similar to the rule that a given role has the privileges

of all roles that they are a member of.

</para>

<para>

Referential integrity checks, such as unique or primary key constraints

and foreign key references,will bypass row security to ensure that

and foreign key references,always bypass row security to ensure that

data integrity is maintained. Care must be taken when developing

schemas and row level policies to avoida "covert channel" leak of

information throughthese referential integrity checks.

schemas and row level policies to avoid<quote>covert channel</> leaks of

information throughsuch referential integrity checks.

</para>

<para>

To enable row security for a table,

the <command>ALTER TABLE</command> is used. For example, to enable

row level security for the table accounts, use:

As a simple example, here is how to create a policy on

the <literal>account</> relation to allow only members of

the <literal>managers</> role to access rows, and only rows of their

accounts:

</para>

<programlisting>

-- Create the table first

CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

</programlisting>

<para>

To create a policy on the account relation to allow the managers role

to view the rows of their accounts, the <command>CREATE POLICY</command>

command can be used:

</para>

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

<programlisting>

CREATE POLICY account_managers ON accounts TO managers

USING (manager = current_user);

</programlisting>

<para>

If no role is specified, or the special<quote>user</quote> name

If no role is specified, or the special user name

<literal>PUBLIC</literal> is used, then the policy applies to all

users on the system. To allow all users toview their own row in

auser table, a simple policy can be used:

users on the system. To allow all users toaccess their own row in

a<literal>users</> table, a simple policy can be used:

</para>

<programlisting>

</programlisting>

<para>

To use a different policy for rowswhich are being added to the

table from those rowswhich are visible, the WITH CHECK clause

can be used. This would allow all users to view all rows in the

users table, but only modify their own:

To use a different policy for rowsthat are being added to the table

compared to those rowsthat are visible, the<literal>WITH CHECK</>

clausecan be used. Thispolicywould allow all users to view all rows

in the <literal>users</> table, but only modify their own:

</para>

<programlisting>

</programlisting>

<para>

Row security can be disabled with the <command>ALTER TABLE</command>

also.Note that disablingrow security does not removethe

policies which aredefined on the table, they are simply ignored

and all rowsare visible andable to be added, subject to the

normal privilegessystem.

Row security canalsobe disabled with the <command>ALTER TABLE</>

command.Disablingrow security does not removeany policies that are

defined on the table; they are simply ignored. Then all rows in the

tableare visible andmodifiable, subject to the standard SQL 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.

Below is a larger example of how this feature can be used in production

environments. The table <literal>passwd</> emulates a Unix password

file:

</para>

<programlisting>

postgres=&gt; set role admin;

SET

postgres=&gt; table passwd;

username | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell

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

postgres=&gt; table passwd;

ERROR: permission denied for relation passwd

postgres=&gt; select username,real_name,home_phone,extra_info,home_dir,shell from passwd;

username | real_name | home_phone | extra_info | home_dir | shell

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

postgres=&gt; update passwd set username = 'joe';

ERROR: permission denied for relation passwd

--Allowed to change her own real_name, but no others

--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 username = 'admin';

ERROR: permission denied for relation passwd

postgres=&gt; insert into passwd (username) values ('xxx');

ERROR: permission denied for relation passwd

-- Alice can change her own password

-- Alice can change her own password; RLS silently prevents updating other rows

postgres=&gt; update passwd set pwhash = 'abc';

UPDATE 1

</programlisting>

<para>

For additional details see <xref linkend="sql-createpolicy">

and <xref linkend="sql-altertable">.

</para>

</sect1>

<sect1 id="ddl-schemas">

<refnamediv>

<refname>ALTER POLICY</refname>

<refpurpose>change the definition of a policy</refpurpose>

<refpurpose>change the definition of arow level securitypolicy</refpurpose>

</refnamediv>

<refsynopsisdiv>

<synopsis>

ALTER POLICY <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable>

ALTER POLICY <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable>

[ RENAME TO <replaceable class="PARAMETER">new_name</replaceable> ]

[ TO { <replaceable class="parameter">role_name</replaceable> | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]

[ USING ( <replaceable class="parameter">using_expression</replaceable> ) ]

[ WITH CHECK ( <replaceable class="parameter">check_expression</replaceable> ) ]

<title>Description</title>

<para>

<command>ALTER POLICY</command> changes the<replaceable class="parameter">

definition</replaceable> of an existing policy.

<command>ALTER POLICY</command> changes thedefinition of an existing

row-level security policy.

</para>

<para>

To use <command>ALTER POLICY</command>, you must own the table that

the policy applies to.

</para>

<para>

In the second form of <command>ALTER POLICY</command>, the role list,

<replaceable class="parameter">using_expression</replaceable>, and

<replaceable class="parameter">check_expression</replaceable> are replaced

independently if specified. When one of those clauses is omitted, the

corresponding part of the policy is unchanged.

</para>

</refsect1>

<refsect1>

<term><replaceable class="parameter">role_name</replaceable></term>

<listitem>

<para>

The role to which the policy applies. Multiple roles can be specified at one time.

To apply the policy to all roles, use <literal>PUBLIC</literal>, which is also

the default.

The role(s) to which the policy applies. Multiple roles can be

specified at one time.To apply the policy to all roles,

use <literal>PUBLIC</literal>.

</para>

</listitem>

</varlistentry>