PostgreSQL9.5 new features-row-level security and application-level solutions

PostgreSQL9.5 new features-row-level security and application-level solutions

PostgreSQL 9.5 introduces the row-level security policy feature (RLS), which provides fine-grained control beyond the traditional authorization security system in the data security system. Correspondingly, Oracle provided a similar VPD (Virtual Private Database) technology long ago, which has matured in the Oracle10g era. SQL Server 2016 also provides similar row-level security features. PostgreSQL also provides this new feature in this major version. This article will introduce this technology in detail and then propose corresponding application-level solutions.

PostgreSQL 9.5 released and downloaded, bringing new features such as UPSERT

1. Row-level security policies 1. Overview of Row-level security policies

In earlier versions of data security technology, the GRANT/REVOKE command is used. These two commands provide object-level security restrictions for tables, it also provides column-level security restrictions. However, in many scenarios, different users are often expected to access the same table to view different data, that is, row-level security requirements. This feature is supported in 9.5. In this version, in addition to normal SQL queries and data updates, additional row-level security policies can be attached to restrict query return and data operation results. By default, the table has no security policy restrictions.

All operations on data, including data query and data update, are restricted by policies. If no security policy is configured, all data query and update operations are prohibited, but the command for operating the entire table, suchTRUNCATEAnd REFERENCES are not affected.

Row-level security policies can be added to commands, roles, or both. The command can be ALL,SELECT,INSERT,UPDATEAnd DELETE. The same policy can also be assigned to multiple roles. However, the table owner, superuser (postgres), and role with the BYPASSRLS attribute are not restricted by security. If the application wants to ignore the restrictions of the row-level security mechanismRow_securitySet to off.

How does one control data query and update when a row-level security table is enabled? This is implemented by an expression that returns a Boolean value. This expression takes precedence over the query conditions and functions in the query. The only exception to this rule is the leakproof function. Two expressions are required to control data query and update independently.

Only the owner has the permission to enable/disable row-level security and add a policy to the table.

The create policy, alter policy, and drop policy commands are used to CREATE, modify, and delete policies respectively. alter table can be used to enable or disable row-level security.

Each policy has a name, and each table can define multiple policies. Because the policy is for tables, the names of multiple policies in the table must be unique, however, different tables can have a policy with the same name. When a table has multiple policies, the relationship between these policies is OR.

1. 2. related examplesEnable row-level security

To enable row-Level TABLE security, run the alter table command as follows:

CREATE TABLE user (id text, name text, email text,manager text);ALTER TABLE user ENABLE ROW LEVEL SECURITY;

Alter table can also be used to disable row-level security, but does not delete the corresponding policy after it is disabled.

Create Policy

If you want only managers to see the corresponding users:

CREATE POLICY user_manager ON user TO managers USING (manager = current_user);

If no role is specified, the default value is PUBLIC, that is, for all roles in the system, if you want each user in the system to only view their own data, you only need to create the following simple policy:

CREATE POLICY user_policy ON users USING (id = current_user);

If you want to restrict data update operations, you can add the with check statement. The following policy allows everyone to view all the data, but only modify their own data:

CREATE POLICY user_policy ON user USING (true) WITH CHECK (id = current_user);

For more details about creating a POLICY, refer to the create policy command manual.

2. Application-level solutions

From the above, we can see that PostgreSQL's Row-Level Security is for users with different permissions who log on to the database. This is no problem for database designers because they work on the database layer. In actual scenarios, we work at the application layer. We hope to implement different permission control for the same SQL statement in the application, which involves three problems:

1. The application logs on to the database through a shared account.
2. Application rules may be complex.
3. There is also a possibility that many dynamic parameters are required.

The following is an application-level solution for the above three problems.

2. Level-3 Account System

In reality, for the convenience of application development, the common practice is to first create a login user role with the Super User (postgres), and then use the newly created Login User role to log on, create and log on to a database with the same name as the user name, and then the application will use this login user to connect to the database.

Because row-Level Security is ineffective for table owners and superusers, the original development model is no longer applicable, and the application needs to log on through a separate account, thus forming a three-level account system:

1. Super User (ipvs): as the administrator of the database system, it has all permissions of the entire database system;
2. Database owner: This account acts as the database administrator and has all permissions for the entire database;
3. Application owner: by default, this account only has the permission to log on to the database. Related authorization is required for other operations;

The application connects to the database through the Application owner account. For example, to add, delete, modify, and query all tables in the public mode of a database, perform the following authorization:

Assume that the logon user is umo-public:

GRANT ALL ON ALL TABLES IN SCHEMA public TO u1_public;

The same applies to permission authorization for other objects.

2. Policy Functions

Both the USING expression and with check expression require that the return value of the expression be a Boolean value, but there are no restrictions on the expression itself. Therefore, policy functions can be written in some complicated scenarios, such:

ALTER POLICY user_policy ON user USING(p());

This is legal, as long as the return value of the p function is boolean.

This p function can clearly write complex logic internally, but this p function cannot pass parameters for the moment, and the function can only return boolean values, but it also has restrictions on the space used by the function, it is not as flexible as the WHERE clause of a string returned by Oracle's policy function, because it cannot be used in some dynamic scenarios.

2. 3. Dynamic Parameters

The last problem is dynamic parameters, that is, specific policy expressions or policy functions depend on specific and transaction-level parameters of the application operator, such as the user id, the id of the organization to which the user belongs, or even some data that the user selects or inputs on the interface. This problem is implemented in Oracle through the context object of the database, but not in PostgreSQL. So what should we do?

The power of PostgreSQL is here! Here we will introduce two concepts: customization options and configuration setting functions in system management functions.

1. Custom options:

Any database, including many other complex software, has many configuration parameters, as is PostgreSQL. In PostgreSQL, many built-in parameters are defined in postgresql. conf.

We know that PostgreSQL supports extensions and these extensions may also require some parameters. How can we define these parameters in PostgreSQL? This feature is provided through custom options.

A custom option consists of two parts: the extension, and the property name, such as rls. userid. Because custom options may need to be set before the extension is loaded, PostgreSQL allows these variables to exist in the form of placeholders until the extension module is loaded, after the extension module is loaded, the actual meanings of these variables are given.

With this understanding, we can use this feature to transmit dynamic parameters.

Note that before PostgreSQL, the extension of this customization option must be in ipvs. the conf file is defined, for example, custom_variable_classes = rls, and this restriction is removed in version 9.2, which provides greater convenience.

2. configuration setting function:

After PostgreSQL supports dynamic parameters and defines the rules of dynamic parameters, you need to know how to assign transaction-level values to these parameters, then we need to use the configuration to set the function.

PostgreSQL provides three methods for parameter settings: SET, alter system, and set_config, the two functions are exactly what we need. Let's look at the definitions of these two functions:

Name	Return Value	Description
Current_setting (setting_name)	Text	Get the set current value
Set_config (setting_name, new_value, is_local)	Text	Set parameters and return New Values

Here, we need to pay special attention to the third parameter is_local of the set_config function. If this parameter is true, this parameter is valid only for the current transaction. If it is false, it is valid for the current session. The SET command also corresponds to the LOCAL/SESSION parameter.

After understanding these two features, we have a corresponding application layer solution, which requires two steps:

1. Define and pass parameters:

   After the transaction is started, you can perform corresponding SQL operations before, for example, calling the following SQL:

   SELECT set_config('rls.userid', 'xiaoming', true); 

2. Obtain parameters in a policy expression or policy function:

   Assume that for the preceding user table, we want users logged on to the application to only query/update their own data. The corresponding policy is as follows:

   CREATE POLICY user_policy ON user USING (id =current_setting('rls.userid')) WITH CHECK (id=current_setting('rls.userid'));

------------------------------------ Lili split line ------------------------------------

Install the PostgreSQL 9.3.5 database in Ubuntu Server 14.04

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: