We often get interesting questions from Customers before starting migrations from Oracle to PostgreSQL or SQL Server to PostgreSQL. One of such questions is regarding the Oracle or SQL Server alternatives for security beyond the object level. Oracle has Label security whereas SQL Server has row level security. In this article, we shall discuss about row level and column lever security in Oracle vs PostgreSQL and understand how it works in PostgreSQL.
Before proceeding with this article, please remember that the failures and delays in Database Migrations can be avoided by having detailed Assessments done by experts. Contact MigOps about your database migration assessment today.
Oracle Label Security - Row level
Through Oracle label security (OLS), users can achieve row level level security using different data access levels. Security authorization for users is based on the OLS labels. Oracle Label Security also supports us to define table’s access requirements when there exists no natural data such as an employee ID or a manager column that helps us define policies. This is done by defining labels aka data access levels.
Column level security in Oracle
Oracle VPD or Virtual Private database allows us to enable Column level security in Oracle. This can be implemented using VPD policies. Starting from Oracle 12c, Oracle has introduced Real Application Security (RAS) which allows users to define both Row level as well as Column level security policies by appending a WHERE dynamically. This dynamic WHERE condition to restrict access is transparent to Users. The PL/SQL Package : DBMS_RLS can be used to implement Oracle VPD. For more details please see : Oracle VPD documentation.
Similar to Oracle, PostgreSQL supports both Row level and Column level security. Let us see how it works in detail.
Contact MigOps today for migrating from Oracle to PostgreSQL
Row Level Security or RLS in PostgreSQL
When a user is granted a select or insert/update/delete access to a table, the respective grant is granted to all the records of that table according to the SQL privilege system. However, if the business or security requirements are incorporated to allow a user to access only certain rows of a table that the user is allowed to access, then, a row level security is required.
Enabling Row Level Security at Table level
To implement RLS, the foremost step is enabling the RLS on the respective table. It can be done using the following command.
ALTER TABLE <tablename> ENABLE ROW LEVEL SECURITY;Row Security Policies
Upon enabling RLS at table level, a policy must be attached. The syntax of the policy creation is as follows :
CREATE POLICY <policy_name> ON <table> [FOR <commands>][TO <role>]
USING (boolean_clause)
[WITH CHECK (boolean_clause)];A few facts to note about policies while enabling Row Level Security.
- When row level security is enabled on a table, all normal access to the table for selecting rows or modifying rows must be allowed through a row security policy.
- Row security policies can be specific to commands, or to roles, or to both.
- A policy can be specified to apply to ALL commands, or to SELECT, INSERT, UPDATE, or DELETE.
- Multiple roles can be assigned to a given policy, and normal role membership and inheritance rules apply.
- Policies are selective, only applying to rows that match a predefined SQL expression.
- USING statements are used to check existing table rows for the policy expression.
- WITH CHECK statements are used to check new rows.
- You can define multiple policies for a single table, but each policy must have a unique name within that table.
By passing Row level security
Following are the scenarios where a row level security may be bypassed.
- A Superuser or a ROLE with BYPASSRLS attribute
- Table owners who haven’t opted for the RLS forcefully
- Primary Key and Unique key constraints including foreign key references bypass RLS always.
Demonstration of Row Level Security at the Table level
Step 1 : Let us create a Table named : users and 3 roles : admin, bob and eve.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INTEGER,
city VARCHAR(255),
email VARCHAR(255) NOT NULL,
phone VARCHAR(255),
salary INTEGER NOT NULL
);
CREATE ROLE admin; -- Administrator
CREATE ROLE bob; -- Normal user
CREATE ROLE eve; -- Normal user
Step 2 : Insert a few records to this table and enable RLS at Table level.
INSERT INTO users (id, name, age, city, email, phone, salary)
VALUES (1, 'admin', 25, 'New York', 'admin@example.com', '555-555-55', 50000);
INSERT INTO users (id, name, age, city, email, phone, salary)
VALUES (2, 'bob', 32, 'Chicago', 'bob@example.com', '555-555-56', 60000);
INSERT INTO users (id, name, age, city, email, phone, salary)
VALUES (3, 'eve', 28, 'Houston', 'eve@example.com', '555-555-57', 55000);
--Enable row-level security on the table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;Step 3 : Create necessary policies to implement RLS.
In our example, we shall consider the following requirements -
- Admin can view and modify any row
- Any other roles should only view the records they are allowed to view.
To achieve the above requirements, we shall start by creating a policy that allows the role : admin to have no limitations at row level as long as the necessary privileges for SELECT or others exist. For other roles, we shall have a policy defined for both SELECT and UPDATE as seen in the following example.
-- Admin User
CREATE POLICY admin_all ON users TO admin USING (true) WITH CHECK (true);
--Normal User can view only their own records
CREATE POLICY user_view ON users FOR SELECT USING (current_user = name);
--Normal User can update only their own records
CREATE POLICY user_mod ON users FOR UPDATE USING (current_user = name)
-- Provide admin with all grants
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO admin;
-- Provide public role with SELECT and UPDATE grants
GRANT SELECT, UPDATE ON users TO public;Through the above policies and privileges, we are allowing any role other than admin to have appropriate privilege on the records that match the condition (current_user = name). Let us test and ensure that the system is behaving as expected.
-- admin can view all rows and fields
postgres=> set role admin;
SET
postgres=> table users;
id | name | age | city | email | phone | salary
----+-------+-----+-----------+-------------------+------------+--------
1 | admin | 25 | New York | admin@example.com | 555-555-55 | 50000
3 | eve | 28 | Houston | eve@example.com | 555-555-57 | 55000
2 | bob | 32 | Chicago | bob@example.com | 555-555-56 | 60000
(3 rows)
-- Test what bob is able to do
postgres=> set role bob;
SET
-- Bob can only view his own records
postgres=> table users;
id | name | age | city | email | phone | salary
----+------+-----+----------+-----------------+------------+--------
2 | bob | 32 | Chicago | bob@example.com | 555-555-56 | 60000
(1 row)
-- Bob can change her own record; RLS silently prevents updating other rows
postgres=> update users set city = 'hyderabad';
UPDATE 1
postgres=> update users set city = 'hyderabad' where name = 'admin';
UPDATE 0From the above tests, we can notice that the role : admin can see all the records whereas the role : bob can only see the record that satisfies the condition (current_user = name). In this context, current_user is bob and the value stored in name should also be bob. In addition to that, we could only notice that the UPDATE statement would prevent the role : bob from updating any other records other than the ones that satisfies the condition : (current_user = name)
Column Level Security in PostgreSQL
As the name implies, at this level of security, we only want to enable the user to view a specific column or a collection of columns. There are two ways to implement column level security.
- Enforcing access to selected columns of a Table with a direct access to the Table.
- Using VIEWS to limit access to selected columns of a Table upon eliminating direct access to the Table.
Column-level Access
In PostgreSQL, securing columns from users or providing access to limited columns can be implemented by leveraging the GRANT command.
For demonstration purpose, we shall create a table and insert some records to it. We can then attempt to enable column level security and test the same.
-- Create employees table
postgres=# CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INTEGER,
city VARCHAR(255),
email VARCHAR(255) NOT NULL,
phone VARCHAR(255),
salary INTEGER NOT NULL
);
CREATE TABLE
postgres=# CREATE ROLE admin ;
postgres=#
-- Insert few records into the users table
INSERT INTO employees (id, name, age, city, email, phone, salary)
VALUES (1, 'admin', 25, 'New York', 'admin@example.com', '555-555-55', 50000);
INSERT INTO employees (id, name, age, city, email, phone, salary)
VALUES (2, 'steve', 32, 'Chicago', 'bob@example.com', '555-555-56', 60000);
INSERT INTO employees (id, name, age, city, email, phone, salary)
VALUES (3, 'ben', 28, 'Houston', 'eve@example.com', '555-555-57', 55000);As the table is created and few records are inserted, we shall now grant access on selected columns to the respective role.
In the following section, you could notice that the SELECT privilege to role : ben is issued for only 5 of the 7 columns of the table : employees. Whereas, the role : admin is allowed to SELECT all the columns of the employees table. Through this approach, we are implementing column level security by limiting access to only selected columns.
-- Grant SELECT on all the columns to admin
postgres=# GRANT SELECT ON employees TO admin;
GRANT
--Grant SELECT on the columns you want the user(ben) to access which makes limiting the access from remaining columns
postgres=# GRANT SELECT (id, name, age, city, phone) ON employees TO ben ;
GRANTWe can now test the the column level security implementation for the role : ben. As seen in the following section, when we attempt to select all the columns from the employees table by setting the role to ben, we see a “permission denied” error message.
postgres=# SET ROLE admin;
SET
postgres=> TABLE employees ;
id | name | age | city | email | phone | salary
----+-------+-----+----------+-------------------+------------+--------
1 | admin | 25 | New York | admin@example.com | 555-555-55 | 50000
2 | steve | 32 | Chicago | bob@example.com | 555-555-56 | 60000
3 | ben | 28 | Houston | eve@example.com | 555-555-57 | 55000
(3 rows)
postgres=> SET ROLE ben;
SET
postgres=> TABLE employees ;
ERROR: permission denied for table employees
postgres=> SELECT id, name, age, city, phone FROM employees ;
id | name | age | city | phone
----+-------+-----+----------+------------
1 | admin | 25 | New York | 555-555-55
2 | steve | 32 | Chicago | 555-555-56
3 | ben | 28 | Houston | 555-555-57
(3 rows)In this scenario, it's crucial to keep in mind that the user shouldn't have a direct SELECT privilege granted to the entire table without column restriction. Removing SELECT access from the table and giving the user access to only the columns you want them to see is important.
Leveraging Views for Column level security
Creating a view that only contains the columns you want the user to see and granted the access to the view instead of the table is another approach to implement column-level security.
For demonstration, let us create a VIEW on selected columns of the employees table and only grant access to the view but not to the table. In the following example we are revoking SELECT privilege on employees table from the admin role. We are rather granting the SELECT privilege on the VIEW that is created out of selected columns of the table.
postgres=> SET ROLE <superuser> ;
SET
postgres=# REVOKE SELECT ON employees FROM admin;
REVOKE
postgres=# CREATE VIEW emp_info AS SELECT id, name, age, city FROM employees;
CREATE VIEW
postgres=# GRANT SELECT on emp_info TO admin;
GRANT
postgres=# SET ROLE admin ;
SET
postgres=> SELECT * FROM employees ;
ERROR: permission denied for table employees
postgres=> SELECT * FROM emp_info ;
id | name | age | city
----+-------+-----+----------
1 | admin | 25 | New York
2 | steve | 32 | Chicago
3 | ben | 28 | Houston
(3 rows)As we see in the above logs, admin can access the emp_info view to examine employee data, but unable to access the table's columns like email and salary.
Conclusion
Row-level security and Column-level security are useful solutions for enforcing data security in PostgreSQL. With RLS, you can easily restrict access to sensitive data based on the user's role or other attributes, and you can implement more fine-grained access controls using dynamic policies. Whether you are working with a small database or a large enterprise system, RLS and CLS can help you ensure that your data is protected and secure. If you are looking to know about more such features available to secure your PostgreSQL environment, Contact Us today.
