Differentiation of access rights in PostgreSQL

I want to describe one of the ways to differentiate access to data in a DBMS, which seems to me quite flexible and interesting. This method allows you to get information about the current user by calling a simple stored procedure. But first, let's consider the well-known existing methods with their pros and cons, among which we can highlight the use of built-in DBMS authentication mechanisms and access control at the application level.

Method 1. Built-in authentication mechanisms

For each business user, a corresponding user is created in the DBMS, to which the necessary rights are distributed.

The advantages of this approach: its simplicity and transparency. According to the DBMS logs, it is easy to see which requests are performed by users, several rights can be combined into roles and distributed to users directly out of the box. The main disadvantage of this approach is the lack of access control at the row level. Yes, row-level security appeared in 9.5, but this mechanism does not work as fast as we would like, especially for JOIN .

The built-in authentication mechanisms also include LDAP, PAM, GSSAPI, and others .

Method 2: Application Level Checks

Many implement access control directly at the application level. In this case, you can use an external service to authorize users and store password hashes directly in the database and check them in the application. It does not matter. The main thing is that all users ultimately go to the database under one user. In this approach, I do not see any advantages at all, but there are plenty of minuses:

1. There is no access control at the row level, or it becomes very difficult.
2. If the password of the DBMS user is compromised, the attacker gets full access to all the data, and he can not only read them, but also change them.
3. The application becomes the only link controlling access, and if, say, you want to implement another service that works with the database, you will have to write all the code that performs the checks, again.

Despite such a large number of disadvantages, according to my observations, this is the most common way to delineate access to date.

Method 3. Introduction of the session at the DBMS level

About this method today and I want to tell in more detail. Its essence is simple: an authorization procedure is created in the database, which checks the user's login and password and, if successful, sets the value of some session variable that would be readable until the end of the current session. To store the value of a variable, we will use a global GD array, accessible to Pl / Python procedures:

create or replace function set_current_user_id(user_id integer) as $$ GD['user_id'] = user_id $$ language plpythonu; 

The very same authorization procedure will be as follows:

 create or replace function login(user_ text, password_ text) returns integer as $$ declare vuser_id integer; vis_admin boolean; begin select id, is_admin into vuser_id, is_admin from users where login = login_ and password = password_; if found then perform set_current_user_id(vuser_id); /*   set_is_admin()    set_current_user_id() */ perform set_is_admin(vis_admin); else raise exception 'Invalid login or password'; end if; return vuser_id; end; $$ language plpgsql security definer; 

After that, it remains to implement a function that will return the ID of the logged in user:

 create or replace function get_current_user_id() returns integer as $$ return GD.get('user_id') $$ language plpythonu stable; 

Now, how to use it all. And it is very easy to use. After the user is authorized within any function, it is now easy to find out what the user is requesting access to the data and what rights it has. For example:

 create or replace function delete_branch(branch_id_ integer) returns void as $$ begin if not current_user_is_admin() then raise exception 'Access denied: this operation needs admin privileges'; end if; ... end; $$ language plpgsql; 

To demonstrate how access control at the row level will work, we will write a function that will return a list of bank accounts, and only those that are open in the branch to which the user belongs (branch_id).

 create or replace function get_accounts() returns table (account_number text) as $$ begin return query select a.account_number from accounts a join users u on u.branch_id = a.branch_id where u.id = get_current_user_id(); end; $$ language plpgsql; 

What are the pros and cons of this approach? Pros:

1. Usability, flexibility, extensibility.
2. Providing access control at the row level, almost without prejudice to the performance of the DBMS.
3. All logic is concentrated in the DBMS, thus it is possible to provide access to the database to several applications in which only the authorization mechanism will have to be implemented.
4. In addition to information about the user, you can quickly receive any metadata associated with him - for example, whether the current user is an administrator, his name to be displayed in a personal account, groups to which he belongs, and so on.

Despite this, there are also disadvantages:

1. All the logic of working with data needs to be wrapped in stored procedures (in fact, for me this is a plus).
2. The need to authorize the user at the beginning of each session, and if the code is wrapped in a transaction, then at the beginning of each transaction. This may be uncritical for so-called "fat clients", but for web applications it is already becoming relevant. In this case, the problem is solved by wrapping the driver, which provides access to the DBMS with a custom code so that authorization is performed before executing each request. It does not sound very beautiful, but in fact everything is not so scary. I used Flask and flask_login in my projects, which greatly simplifies this task.

Summary

Of course, for sure there are projects where the approach described by me will be inappropriate and I will be happy if you share your thoughts on this matter - perhaps this method can be improved and improved. But, in general, this approach seems to me quite interesting.