PostgreSQL has been a widely used database system across the globe and is highly secured. PostgreSQL has come over with the two types of securities, e.g. column-level and row-level. Our main topic is Row-level Security in PostgreSQL. Row-level security is said to be a simple and much-needed tool in PostgreSQL security. It has been used to control user access to certain tables and records based on some policies. By applying row-level security, we will restrict users to only view or manipulate the table records containing the data regarding them instead of making changes to other user’s records.
You need to open the SQL Shell for PostgreSQL 13 from the start bar of Windows 10. After opening it, you will get the black screen of the SQL shell. Add the server name, database name, port number, user name, and password when asked one by one. The SQL Shell will be ready for your further use.
The database user “Postgres” is already a superuser of your system. If you are not logged in from a superuser, you have to log in from it. The method to log in from a superuser account is by using the stated command below in the shell having “\c” sign with the name of a database to be used, e.g. Postgres, along with the name of a superuser, e.g. Postgres. It may require the password for an account if not already logged in.
Create Table:
You need to create a new table within the superuser and database “Postgres”. So, we have used the CREATE TABLE query to create a table “test” with some columns as shown.
After creating a table “test”, we have inserted three records in it for 3 different users, e.g. aqsa, raza, and rimsha, via the “INSERT INTO” instruction in the shell.
The table and its records can be seen on the SQL Shell screen using the SELECT query.
Create Users:
We have been working in SQL Shell on the table test with the superuser “Postgres”, but we have to create some other users as mentioned in the table, e.g. aqsa, raza, and rimsha. So, we have used the CREATE USER command to do so while assigning the password. After that, we have granted SELECT privileges on all these users after creation.
When we have used the newly created users to fetch the records of a table “test”, the output shows that a user can easily access all the rows from a table instead of a row having its name. The below output shows the output for accessing table test with a user “Aqsa”.
The beneath output demonstrates the output for accessing table test with a user “Raza”.
The below output is for a table test with a user “rimsha”.
Create Policy:
The purpose of Row-level security is to restrict users only to fetch the records having the information regarding themselves. We want row-level security for users not to fetch the records of other users. Let’s start by login in from the Superuser “Postgres” in the SQL Shell.
After login, we have used the CREATE POLICY instruction shown below to create a policy named “new” on the table “test”. We have used the “ALL” keyword here representing all privileges, e.g. insert, update, modify, etc. You can make it particular by adding an insert, select, update, or any keyword. The PUBLIC role has been indicating all the roles. You can specify the user or role here as well. We have been using the “USING” expression here. This will compare the currently logged-in user name with the table “test” in the column “Name”.
Enable Row Level Security:
Only creating the Policy and been applied to roles and tables is not enough to get a change. You must have to enable Row-level security on the table “test” that has a policy set just before. So, we have used the superuser “Postgres” to enable row-level security on a table “test” with the ALTER TABLE command shown in the screenshot attached.
As we have currently logged in from the superuser “Postgres”, the command “SELECT” along with the keyword “current_user” is showing the user name in the output. Upon accessing the table with select command while logged in from the superuser, it shows all the records of a table “test”. This means the policy and row-level security does not affect superuser.
Now, we will be logging in from the new roles created a while ago. We have logged in from the user “aqsa” and checked the currently logged-in user. It returns “aqsa” as a current user. Upon fetching the table “test” records by a SELECT command, it returns the rows only belonged to the user name “aqsa” matched to a column “Name” in the table. All the other rows have been secured and cannot be viewed by a user “aqsa”.
Let’s log in from the other user, “Raza” from the terminal and check the current user. It returned “Raza” as a current user. The output for the SELECT command shows only the record for a user “Raza” from the table “test”.
The row-level security has worked the same on the user “rimsha” as per the output image below.
ByPass Row-Level Security:
The bypassing permissions can be used to overrule the row-level security by some superusers and other privileged users. The user having privileges of Bypass row-level security can overrule the row-level security for any table and access other users’ records as well. So, we have logged in from the superuser account within the terminal first.
After that, we have altered a user’s rights “Raza” by an ALTER USER command applied on it. We have assigned user “Raza”, the privileges of bypassing the row-level security by “bypassrls” mentioned in the ALTER USER query as shown.
Log in from the user “Raza” from the shell. You can see that the user “Raza” can now surpass the row-level security policy and can easily see and modify the records of all other users from the table “test” via the SELECT query.
Drop Policy:
Let’s log in from the superuser once again to drop a policy “new” that has been applied on the table “test”.
The DROP POLICY command has been used in the shell to drop a policy named “new” from the table “test”.
After dropping a policy, we have logged in from one of the users to check if it still works or not. We have found that dripping a policy couldn’t change the user “aqsa” or others to fetch the records of a table “test”. This is because we haven’t disabled the Row-level security on the table yet.
Disable Row-Level Security:
To disable the row-level security on a table “test”, log in as a superuser and use the query shown in the snap below.
After login from the other user, you will be able to view and modify the records easily.
Conclusion:
This tutorial contains a brief demonstration of Row-level security used to restrict users upon access to data for security purposes. Row-level security has been achieved by creating users, policies and then enabling security. The article also contains the implementation regarding dropping a policy and disabling Row-level security. Hence, this article is a bonus package for our users to do everything from enabling to disabling the row-level security within one take.
