Postgres Security Definer

Within this advanced and automated era of computers and technology, everybody wants to be secured along with their personal information. Same like that, databases are used to store information regarding a lot of things including personal information about people. Due to a lot of information, we also need to take care of our database security to not let anyone make use of it wrongly. Within the PostgreSQL database, we have to use many built-in databases to fetch some records from the tables and database. Just like many of these functions, one is the Security Definer function. As the title suggests that it must be something related to defining the security of the PostgreSQL database. As this concept is unique, complex, and new to most of the PostgreSQL users, we have decided to cover it within a whole new article. So, let’s have a little knowledge about the security definer property of methods now.

Security Definer vs Security Invoker

The security definer term referred to some exceptional functions used to perform unique and privileged tasks concerning the security of the database in PostgreSQL. These tasks are so official and privileged that not every user can perform them or use them if it is safe and sound. This can be used as a property in the PostgreSQL query while performing some transactions from the PostgreSQL database. When a function has been called with the privileges of the user who has been calling this function, it is said to be the Security Invoker property of functions. On the other hand, when a function has been called with the rights of its real owner (who created this function), it is said to be the Security Definer property of the function.

Who can use it?

Most of the time, the security definer function can be executed with the rights of its owner i.e., the owner of the security definer function will be the only one making use of it on the execution part. On the other hand, normal built-in or user-defined functions can be executed by any user i.e., public, if they are not specified as Security Definers. As the PostgreSQL database always agrees to the use of system databases and tables by the local and remote PostgreSQL employers, this use may influence the Security Definer function execution and also harms the security of a database. There is a possibility that an outside user may get an opportunity to manage or control the whole PostgreSQL database and its tables, add corrupted data within the database using the same rights as the security definer function owner has. This way, that outsider user can execute queries and codes which he/she shouldn’t be. While using this property within the PostgreSQL query, we have been letting users perform the functions carried out with the rights of the user who has created it and will not be using the rights of the user who has been calling it. Thus, we need to be more cautious than before, while using the database after the use of Security Definer. Let’s take a look at some examples now.

Example 01

Let’s take a look at a simple example to generate a function with security definer property. We will try to make our function as secure as possible yet use the security definer function in it. So, we have started with the login in the PostgreSQL database pgAdmin GUI tool. See the “Functions” tab within your specific database. Right-click on it, hover over the “Create” option, and select “Function”.

The screen named Create-Function will be opened. Add the name of a function as “harmless”, select its owner and schema. As this function is going to be a security definer, it would be only executed by the rights of user “aqsayasin”.

From the Definition section, add the return type, language, and add the data type of the argument to be given in the function.

Within the code area, write some simple code. As you can see, we are not using any table here so it seems to be unharmful when it comes to its execution.

In the Options area, turn on “Security of definer” to enable security definer property.

Within the SQL section, you will get your whole query for creating a new function with the Security Definer property. The owner of this function will be the user “aqsayasin”.

After running the SELECT instruction to call the harmless function passing 15 as value to it, we have got the value 25 as output. Although it looks all safe and sound, using public schema will let the outside attachers/users create new objects and have access to this function.

To get the information regarding the newly created security definer function “Harmless”, we need to use the SELECT instruction shown below in the query area of pgAdmin 4. The column “proname” is showing the name of a function i.e., harmless, “nspname” is showing the name of a schema i.e., public, and the “usname” column belongs to the owner for this function i.e., “aqsayasin”. This is how we get the information regarding the security definer function.

Example 02

The security definer function can also be used to create a procedure by a particular user. Let’s create a new procedure “sec_def” using security definer property within it. This procedure is used to get values from the “Ftest” table with the “public” schema. You have to know that the “Ftest” table has been saved in the “aqsayasin” database and its owner is user “aqsayasin”.

Now, you have to log in from the PostgreSQL shell terminal to add some simple commands. So, we have logged in from the “aqsayasin” database first. After this, we have tried to connect the “aqsayasin” database with the user “outsider” using the “\c” command along with the database name and user to be connected. The user “outsider” user is connected to the database “aqsayasin”.

Now, it’s time to call the “sec_def()” procedure using the built-in call() function in the terminal shell. It will throw an exception showing that the “public.Ftest” doesn’t exist for our user outsider. This is because the procedure has been created with the rights of user “aqsayasin”, and the user “outsider” has no such rights.

If you want to avoid the malfunctions by the outsiders and untrusted users of the PostgreSQL database in your system, you may have to restrict or ban the “writing” rights for those users to insert records in your database within the “search_path” parameter. Using this way, the outside users will not be able to generate malfunctioned objects to manipulate your database system, its data, and rights. You may use the temporary schema “pg_temp” at the last to be searched for to do secure management within the “search_path” in the query.

Conclusion

The use of Security Definer property in the functions to do some special task is quite a potent technique yet very risky as well. To let our users understand the concept of security definer, we have tried to create a function and a procedure having a security definer function as a property. We have seen how an error occurs when a user with no “security definer” owner rights tries to call the function or a procedure. To sum up, we usually prefer not to use it often within the database as it may be unsaved for the PostgreSQL database.

from https://ift.tt/p3MmxW9