Access rights in PostgreSQL

I Want to describe one way of controlling access to data in a DBMS, which I think is quite flexible and interesting. This method allows to obtain information about the current user by calling a simple stored procedure. But first, consider the well-known existing methods with their advantages and disadvantages, among which can be highlighted using built-in authentication mechanisms, database and access control at the application level.

Method 1. Built-in authentication mechanisms

For each business user has a corresponding user in the database, which distributed the necessary rights.

the Advantages of this approach: its simplicity and transparency. The logs of the DBMS's easy to see what queries users are running multiple rights can be grouped into roles and giving them out to users right out of the box. The main disadvantage of this approach is the lack of access control at the row level. Yes, in 9.5 appeared row-level security, but this mechanism does not work as fast as we would like, especially to JOIN.

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

Method 2. Checking at the application level

A carry out access control directly at the application level. You can use an external service to authenticate users and to store hashes of passwords directly in the database and check them in the app. It doesn't matter. The key is that all users eventually go to the database under a single user. In this approach I do not see any advantages, but disadvantages abound:

the
1. no access control at the row level, or it becomes very complicated.
the
2. In the case of compromise of the password of the DBMS user, the attacker gets full access to all the data, and it can not only read them, but to change.
the
3. the Application becomes the single point of controlling access and if you, say, want to implement some service, working with the database, you have to write all the code that performs the checking again.

in Spite of such a large number of disadvantages, in my experience it is the most common method of access today.

Method 3. Introduction session at the DBMS level

this method today I want to tell you in more detail. Its essence is simple: in the database creates the authorization procedure, which checks the username and password of the user and, if successful, sets the value of a session variable that would be available for reading until the end of the current session. To store the value of a variable will use the global array is GD available procedures a Pl/Python:

the

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

the authorization procedure will look as follows:

the

create or replace
function login(text, user_, 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);
/* the function code set_is_admin() is similar
function code 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;

were to implement a function that will return the ID of logged in user:

the

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

Now, how it's used. And use is very simple. After the user's authorization inside any function you can now easily find out what the user requests access to the data and what it has rights. For example:

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 to work the access control at the row level, write a function that will return a list of Bank accounts, and only those which are opened in the branch to which the user belongs (branch_id).

the

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 is the pros and cons of this approach? Pros:

the
1. Usability, flexibility, extensibility.
the
2. Provide access control at the row level practically without compromising performance tuning.
the
3. All the logic is concentrated in a DBMS, so you can grant access to the database for multiple applications, which have implemented only the authentication mechanism.
the
4. in addition to information about the user can promptly obtain any metadata associated with it — for example, whether the current user an administrator, the name to display in some private office, the group to which it belongs, and so on.

Despite this, there are also disadvantages:

the
1. All of the logic work with the data must be wrapped in a stored procedure (actually, for me that's a plus).
the
2. the Need for user authentication at the beginning of each session, and if the code is wrapped in a transaction, at the beginning of each transaction. This can be critical for so-called "fat clients", but for web applications is becoming urgent. In this case, the problem is solved by wrapping the driver, which provides access to the DBMS custom code so that authorization is performed before executing each request. Doesn't sound very good, but it is actually not so bad. My projects used Flask module flask_login, which greatly simplifies this task.

Summary

of course, Certainly there are projects where I described an approach would be inappropriate and I would be glad if you share your thoughts on this, perhaps this method can be modified and improved. But, in General, this approach seems to me quite interesting.

Article based on information from habrahabr.ru