Implementing Row and Column Level Security in Klustron

Note:

Unless specifically mentioned, any version number mentioned in this document can be replaced by any released version number. For a list of all released versions, please refer to: http://doc.klustron.com/zh/Release_notes.html.

Content of this article:

Klustron is a secure database cluster with a wide range of security features at various levels. At the top level, the database cluster can be protected against unauthorized users through host-based authentication, different authentication methods (like LDAP, PAM), restricting listening addresses, and many other security measures available in PostgreSQL.

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

In this article, we will discuss security at a more granular level. While users might have access to a table, we may not wish to allow them to view specific columns or specific rows.

01 Implementing Column-Level Security Policies

As the name suggests, when this security policy is applied, it is designed to allow users to only view specific columns or sets of columns. It renders all other columns private by preventing access to them. As a result, these columns remain invisible and unusable to users during selection or sorting operations.

There are multiple methods to set up column-level security policies, which we will explore in sequence.

1.1 Using Views

Scenario: Within the database, there's an 'employee' table that contains basic employee details as well as information related to their salaries. The aim is to provide querying users with a subset of this employee information, specifically excluding details like employee salaries and bank account numbers.

Implementation Steps:

Set up 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;

Log into 'testdb' using 'readusr'. Initially, this user has access to the 'employee' table.

Revoke 'readusr's access to the 'employee' table and instead grant permissions to view only certain columns 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

Upon accessing the 'employee' table again as 'readusr', it's noted that the user no longer has access. The user can now only view the employee ID, employee name, and address via the view, with salary and bank account details being inaccessible.

1.2 Column Permissions

Another viable option for safeguarding column security is to grant users access permissions solely to specific columns. In the aforementioned scenario, the goal is to prevent the 'readusr' user from accessing the 'salary' and 'account_number' columns of the 'employee' table. Instead of creating a view, we can directly provide access to all columns except for 'salary' and 'account_number'.

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;

After logging in as 'readusr' and accessing the 'employee' table:

It's evident that the access to 'salary' and 'account_number' columns for 'readusr' can be effectively restricted.

Note:

Users shouldn't possess GRANT access permissions for the entire table. It's essential to first revoke the SELECT permission for the table and grant column access only for the columns you want the user to access. If a user already has SELECT permission for the entire table, then granting column-specific access will be ineffective.

02 Implementing Row-Level Security Policies

Row-level security is a crucial safety feature in Klustron. This feature empowers database administrators to outline policies on tables, providing granular control over how each user views and interacts with the data. Think of row-level policies as an added filter; this filter is applied prior to any query conditions or selections when a user attempts to execute an operation on a table. It can either reduce the data available or deny access, depending on the specific policy.

Row-level security policies can be crafted for distinct statements, like SELECT or DML (INSERT/UPDATE/DELETE), or they can encompass ALL. Moreover, these policies can be instituted for specific roles or multiple roles.

For our example, we'll create a user named 'Jack' and grant him permissions to query the 'employee' table.

create user jack with password 'kunlun';

\c testdb kunlun_test

grant select on employee to jack;

Initially, Jack has full access to the 'employee' table.

To impose row-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));

First, using the 'kunlun_test' user (who, in this scenario, also owns the 'employee' table), connect to 'testdb' and establish the policy.

The policy name 'emp_rls_policy' is user-defined.

'employee' is the name of the table.

Here, 'ALL' represents all actions. Alternatively, we can specify certain actions like select/insert/update/delete that we wish to restrict.

'PUBLIC' in this context means all users. However, we can specify certain users for whom the policy is applicable.

Using (ename = current_user) — this segment, termed an expression, acts as a filter, returning a boolean value. Here, we're comparing 'ename' with the user currently connected to the database.

Activating the row-level security:

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

ALTER TABLE employee ENABLE ROW LEVEL SECURITY;

Upon Jack logging into 'testdb' and querying the 'employee' table, with the activated row policy, he can only see records in the 'employee' table corresponding to his details.

Suppose Jack undergoes a departmental change within the company, moving to HR. Now, he's authorized to view all records within the 'employee' table.

Klustron employs the BYPASSRLS and NOBYPASSRLS permissions, assignable to users. By default, NOBYPASSRLS is specified. Both the table owner and super-users have BYPASSRLS permissions, allowing them to circumvent row-level security policies. Now, Jack needs to be granted BYPASSRLS permissions.

psql -h 192.168.40.152 -p 47001 postgres

alter user jack bypassrls;

Once Jack logs into the database again, he has unhindered access to all data within the 'employee' table.

To revoke the row-level security policy, execute the following command:

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

drop policy emp_rls_policy ON employee;

Upon Jack logging in again, he finds himself unable to access data within the 'employee' table. The row-level security policy is still in effect on the 'employee' table. To disable it, execute the subsequent command.

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

alter table employee disable row level security;

After another login, Jack regains full access to all records within the 'employee' table.

03 Combining Row and Column Level Security Policies

Let's suppose you want a user to only view rows that pertain to them and also restrict them from seeing their salary and bank account details. We can achieve this using a combined approach of row 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;

Once these combined policies are in place, the user 'jack' can only query his employee ID, name, and address.

04 Implementing Row-Level Security Policies in Real-world Applications

Implementing row-level security often means creating corresponding database accounts for each company employee. In real-world applications, this can be challenging to put into practice. Typically, only 1-2 accounts connect to the database via the application server. In such scenarios, the following method can be utilized to maintain row-level security.

Let's assume that applications only log into the database as 'appuser' to perform relevant operations, and the user 'jack' does not exist within 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;

When logging into 'testdb' using 'appuser', we can leverage the session variable 'rls.ename' to implement the row-level security policy.

By explicitly setting the session variable 'rls.name' to the name of the logged-in user, the previous row-level security strategy is thereby emulated.

【End】