KlustronDB Sets Row-Level and Column-Level Security Policies

Note:

Unless otherwise specified, the version numbers in the text can be replaced with the version numbers of any released version. All released versions are detailed in Release_notes.

Overview

KlustronDB is a secure database cluster with a wide range of security features at various levels. At the top level, host-based authentication, different authentication methods (LDAP, PAM), restriction of listening addresses, and more security methods provided in PostgreSQL can be used to protect the database cluster from unauthorized users.

When an authorized user obtains access to the database, further security can be implemented at the object level by allowing or denying access to specific objects. This can be done using various role-based authentication measures as well as the GRANT and REVOKE commands.

In this article, we will discuss security at a finer-grained level. Although users have access to tables, we do not want to allow them to view specific columns or specific rows.

01 Set Column-Level Security Policy

As the name suggests, after implementing this security policy, we hope to allow users to view only specific columns or sets of columns, making all other columns private by blocking access to them, so that users cannot see or use these columns when selecting or sorting.

There are multiple ways to implement column-level security policies, which we will explore one by one.

1.1 View Mode

Specific example: There is an employee table in the database, which contains basic employee details and salary-related information. The business wants to provide some employee information to query users but does not want to display employees' salary and bank account information.

Prepare users, tables, and data

psql -h 192.168.40.152 -p 47001 postgres

create user kunlun_test with password 'kunlun';

create database testdb;

grant all privileges on database testdb to kunlun_test;

create user readusr with password 'kunlun';

\c testdb kunlun_test

create table employee ( empno int, ename text, address text, salary int, account_number text );

insert into employee values (1, 'Tony', '2 down str', 10000, '612345578' );

insert into employee values (2, 'James', '132 south avn', 5000, '412345579' );

insert into employee values (3, 'Jack', 'Down st 17th', 6000, '542124566' );

grant select on employee to readusr;

\c testdb readusr

select * from employee;

Use readusr to log in to testdb; initially, there is permission to access the employee table.

Revoke readusr's permission to access the employee table, and grant them access to certain fields through a view.

revoke select on employee from readusr ;

create view emp_info as select empno, ename, address from employee;

grant select on emp_info to readusr;

\q

Accessing employee again through readusr, it was found that there is no longer permission to access. Can only access employee ID, employee name, and address through the view, but cannot access salary and bank account any longer.

1.2 Field Permissions

Another good option for protecting column security is to grant users access only to specific columns. In the example above, we do not want the readusr user to access the salary and account_number columns of the employee table. We can provide access to all columns except salary and account_number, instead of creating a view.

psql -h 192.168.40.152 -p 47001 -U kunlun_test -d testdb

drop view emp_info;

grant select (empno, ename, address) on employee to readusr;

Then log in through readusr and access the employee table

It was discovered that the same can restrict readusr from accessing the salary and account_number fields.

Note:

Users should not have GRANT access to tables. SELECT access to the table must first be revoked, and column access should be provided only for the columns the user is intended to access. If the user already has SELECT access to the entire table, column access to specific columns will not work.

02 Set Row-Level Security Policy

Row-level security is an important security feature of Klustron. This feature allows database administrators to define policies on tables so that they can control each user's viewing and operation of data. Row-level policies can be understood as an additional filter; when a user tries to perform operations on a table, this filter is applied before any query conditions or filters, and data is either reduced or access is denied based on specific policies.

You can create row-level security policies for specific statements, such as SELECT or DML (INSERT/UPDATE/DELETE), or use ALL. Row-level security policies can also be created for a specific role or multiple roles.

Create a user named Jack and grant him permission to query the employee table.

create user jack with password 'kunlun';

\c testdb kunlun_test

grant select on employee to jack;

At the beginning, Jack can access the employee table.

Then create a row-level security policy

psql -h 192.168.40.152 -p 47001 -U kunlun_test -d testdb

create policy emp_rls_policy on employee FOR ALL TO PUBLIC USING (upper(ename)=upper(current_user));

First, connect to testdb through the user kunlun_test, who is also the owner of the employee table in this example, and then create the policy.

The strategy name emp_rls_policy is a user-defined name.

employee is the name of the table.

Here, ALL represents all operations, or we can also specify select/insert/update/delete for any operations we want to restrict.

Here, PUBLIC represents all users. We can also provide specific usernames to which the policy applies.

Using (ename = current_user): This part is called an expression. It is a filter condition that returns a Boolean value, and we compare ename with the user currently connected to the database.

Activate row-level security policy

psql -h 192.168.40.152 -p 47001 -U kunlun_test -d testdb

ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

Then log in to testdb as the user jack and query the employee table.

After row-level security was enabled, Jack could only query the records in the employee table that corresponded to himself.

Suppose Jack transferred positions within the company and moved to the HR department. At this time, he has permission to view all records in the employee table.

KlustronDB has BYPASSRLS and NOBYPASSRLS privileges, which can be assigned to a user; by default, NOBYPASSRLS is assigned. Table owners and superusers have BYPASSRLS privileges, so they can bypass row-level security policies. Now it is necessary to grant jack the BYPASSRLS privilege.

psql -h 192.168.40.152 -p 47001 postgres

alter user jack bypassrls;

When Jack logs into the database again, he can see all the data in the employee table.

You can delete the corresponding row-level security policy using the following statement

psql -h 192.168.40.152 -p 47001 -U kunlun_test -d testdb

drop policy emp_rls_policy ON employee;

User Jack logged in again and found that he could no longer access the data in the employee table. In fact, at this time, the row-level security policy is still in effect on the employee table, and the following statement needs to be executed to disable the row-level security policy on the employee table.

psql -h 192.168.40.152 -p 47001 -U kunlun_test -d testdb

alter table employee disable row level security;

After Jack logs in again, he can see all the records in the employee table.

03 Using Row-Level and Column-Level Security Policies Together

If users are only allowed to see the rows corresponding to themselves, and their access to their own salary and bank account is also restricted, we can use the following strategy that combines row-level and column-level security.

psql -h 192.168.40.152 -p 47001 -U kunlun_test -d testdb

create policy emp_rls_policy on employee FOR ALL TO PUBLIC USING (upper(ename)=upper(current_user));

ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

revoke SELECT on employee from jack;

grant select (empno, ename, address) on employee to jack;

After implementing row-level security policies, the user Jack can only query his own employee number, name, and address.

04 Implementation of Row-Level Security Policies in Real-World Applications

In order to implement row-level security, it is necessary to create a corresponding database account for each employee in the company, which is often difficult to achieve in practical applications. Generally, there are only 1-2 accounts that connect to the database through the application server. In this case, row-level security can be implemented using the following method.

Assume that the application only performs relevant business operations on the database through the appuser login, and there is no user named Jack in the database.

psql -h 192.168.40.152 -p 47001 postgres

drop user jack;

create user appuser with password 'kunlun';

\c testdb kunlun_test;

grant select on employee to appuser;

CREATE POLICY emp_rls_policy ON employee FOR all TO public USING (upper(ename)=upper(current_setting('rls.ename')));

ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

By setting the session variable rls.name to the name of the logged-in user through display settings, the previous row-level security policy is implemented.