Unauthorized access must be prevented when working with databases. In PostgreSQL, you can restrict what a user can access by implementing security definer functions. These user-defined functions allow you, as the database administrator, to grant specific access or modification privileges to particular database users while restricting their access to other data.
For instance, if you have a table containing customer details and someone else needs specific data from that table, you can use security definer functions to manage what data the user can access. If that’s what you hope to achieve, this post will guide you on what steps to take.
How to Implement Security Define Functions in PostgreSQL
When well utilized, security definer functions help restrict access to different types of data in a table. Sometimes, you may want to give a given user or role access to data they didn’t define. It could be the required data is essential in some operations, but since you don’t want the given user to access all the data in the particular table, you can restrict it. Security definer functions provide the room and means to restrict certain privileges.
We will give an example of how to implement it. In our example, we will create a table, insert values, create the security definer function, then test it to confirm it works as expected.
Step 1: Create a Table
Let’s begin by accessing PostgreSQL. Open your terminal and log in to Postgres using the psql shell.
Once logged in, create a table. In this example, we will create a ‘customers’ table with a few columns.
Next, let’s quickly add data to our table using the ‘Insert’ command.
We can check all the inserted data with the select query.
Step 2: Create a Security Definer Function
The user defining the security definer function in this case is “postgres.” Ensure you are connected as that user using the \c command.
Next, we need to create the security definer function. Since we want the user executing the security definer function to access only a specific section of our data, we define a return type and specify what data to be queried.
For this case, we only want the user to query the client_id, name, and order_id, excluding the email and the age of the customer. We’ve named our function customer_details with its parameter as customer_id INT and its return type as TABLE.
Note that we’ve defined the language as plpsql and specified which columns to return when the user calls the function. You will get an output that the security definer function has been created.
Check your database for existing users or roles to specify which to grant the privileges. You can list all the roles with the below command.
Here, our target user is “demo1.”
Let’s grant the user execute privileges to execute the select command on our table using the security definer function we created. Execute the grant command by specifying the security definer function and the target user who should execute it.
Step 3: Test It
It’s time we put the created security definer function to the test. First, let’s first access the database using the target user. Switch the user and database as shown.
Once connected to the database, try running the select query on our customer’s table.
Note how we get a permission denied error, and that’s because we restricted access when we created the security definer function. To access the data in the table, rerun the select query but use the definer function and specify the argument, which is the client_id of the row you want to extract.
You can change the argument to retrieve different data matching the specified argument, as in the image below.
Conclusion
Using security definer functions in PostgreSQL helps you restrict what data a user can access. We’ve explained it in detail and given an example of its implementation. Hopefully, you now understood security definer functions and how to implement them.
