When you create a PostgreSQL database that contains tables with different rows, it’s your work as the administrator to define and restrict what rows do different users can access or modify. When numerous users are accessing a database, applying the row level security ensures that you restrict what activities a user can perform when accessing a table.
For instance, you can utilize the row level security to restrict the SELECT query such that a user will only access the rows pertaining to them. That way, you enhance the privacy and database security. So, what does row level security mean, and how can you apply it in PostgreSQL? Read on to find out!
Understanding the Row Level Security in PostgreSQL
Row Level Security (RLS) refers to a feature in PostgreSQL that allows the administrators to create policies that allow them to control what rows does a given database user can access. That way, depending on the created policy, some actions get rejected or have narrowed the output to minimize the user access or modification of unauthorized data.
RLS is ideal in a multi-user environment as it ensures that the users are restricted to their data only. That way, you eliminate the cases of data tampering such as unauthorized access or modification. You can create and define the policies for database actions such as SELECT or UPDATE.
Row Level Security in PostgreSQL
Having defined what RLS means in PostgreSQL, let’s dig in on the examples of how its implementation works. To better understand what Row Level Security means and how it works, let’s create a table and a user, then proceed to see how to create an RLS and see it in action.
Open the PSQL shell on your terminal as follows:
Once you open the shell, connect to the “postgres” database using the “postgres” superuser. For that, run the following command:
Next, let’s quickly create a table. We name our table as “students ” which contains a few columns.
Insert different rows in your table to see how the row level security works.
Suppose we execute a SELECT query. It uses the current user which is the default user named “Postgres”. Take a look!
The superuser has all the access and modification rights on a database and the tables. You can see from the previous output that after executing the select query, we listed all the contents in our table as the superuser.
We can also create another user aside from the default “postgres” user who accesses our table based on the policy that we define.
To create a new user, use the following syntax:
We are targeting to use the “postgres” database for this case. You can add any database of your choice. As for the user, we named our user as “demo1” and added the –pwprompt option to invoke the password option for the newly created user.
Go ahead and set a password for the user.
After creating the new user, you must restart PostgreSQL using the following command:
Next, login to PostgreSQL using the new user. We can initiate the psql shell with the credentials of the user that you created using the following syntax:
You must specify which database you want to connect to. In our case, we want to connect to the “postgres” database and try to retrieve the contents of the table that we created earlier.
Let’s try running the select query on our table and see what happens. The following image confirms that we get a permission denied error, and that’s because we didn’t grant the new user with the select permission.
So, open another psql shell as the postgres superuser. You can use the current_user attribute to see the currently logged-in database user.
Next, grant the user with the SELECT privileges as follows:
Once we grant that user the select permission, return to the previous shell and re-execute the same select query that we tried earlier. Note how we managed to view all the table contents this time.
To change this state, such that a user can only access the table entries pertaining to them, we must alter the table and enable the row level security. Use the following syntax:
You must also create a restriction policy for the table and define what criteria to use to implement the row level security. Here, we created a policy that uses the name column to only allow the current user to access the entries pertaining to them. Create the policy as follows:
Go back to the shell where the target user is logged in. To test the row level security, try rerunning the earlier select query. Notice how our “demo1” user only managed to access the rows where the name column matches their username after enabling the row level security feature.
That’s how you enable and implement the row level security feature in PostgreSQL.
Conclusion
Row level security is a handy feature that helps to implement the access control in a Postgres database. You can enable it and set the policy to restrict what contents a user can access in a database. We discussed the row level security in PostgreSQL and demonstrated the steps to enable and implement it.
