How to implement data authorization logic inside a Posgres db?

Question

We are building a web app that sits on top of a postgres db. We would like to implement authorization logic inside the database so that it is opaque to the app. For example, suppose a server side controller requests all users from a view v_user. We would like for the db to handle the authorization of which users the currently logged in user can or cannot see. Obviously the server is going to need to send over the login_pkey (user_pkey of logged in user) on every request for this to work.

The issue we are having is with reads. We were able to do this for inserts, updates and deletes by putting the logic in the triggers behind those operations on all views. The issue we are having is how to do this for reads. How can we include variable logic (i.e. logic that depends on which login_pkey is passed) in a view (or some other place) and how can we pass this information for each query.

If it is important, the server we are using is Node and the ORM is Sequelize.

Thanks in advance.

score 1 · Answer 1 · answered Dec 04 '15 at 14:47

Ideally you really want row security to do this well. It's available in the 9.5 version in beta now.

But you can do what you need without.

To pass a user identity you can use a custom variable, e.g.

SET myapp.appuser = 'fred';

then access it with current_setting e.g.

SELECT current_setting('myapp.appuser')

This will raise an ERROR if the setting does not exist, so you should set a default blank value in postgresql.conf, with ALTER DATABASE SET, etc. Or use PostgreSQL 9.5's current_setting('settingname', true) to return null on missing values.

To filter what users can see, use views that check the user identity setting your app sets at connect-time, per the above.

This is not safe if your users can run arbitrary SQL, because nothing stops them RESETing the setting or doing a SET myapp.appuser = 'the-admin'.

@a_horse_with_no_name Thanks, fixed – Craig Ringer Dec 05 '15 at 11:13 — Craig Ringer, Dec 05 '15 at 11:13

score 0 · Answer 2 · answered Dec 04 '15 at 14:30

It's very easy to implement this using Pl/Python global dict GD. First, you need to write auth() function:

create or replace function auth(login text, pass text) as $$
-- Check auth login here
GD['user_id'] = get_user_id_by_login(login)
$$ language plpythonu;

Then you have to write get_current_user() function

create or replace function get_current_user() returns integer as $$
  return GD['user_id']
$$ langugage plpythonu;

Now, you can get current user any time you want. For example:

-- inside stored procedure
vUserId := get_current_user()
-- in query
select * from some_table where owner_id = get_current_user()

Remember, that GD is stored per session, so, as you wrote, you need to login every time you connect to database. In my ORM I do like this:

class MyORM():

    def login(self, user, password):
        cursor = self.__conn.cursor()
        result = cursor.execute('select core.login(%s, %s)', (user, password,))
        data = cursor.fetchone()
        cursor.close()
        return data[0]

    def auth(self, cursor):
        cursor.execute('select core.auth(%s)', (g.user_id,))

    def query(self, query):
        cursor = self.__conn.cursor()
        self.auth(cursor)
        cursor.execute(query)
        data = cursor.fetchall()
        cursor.close()
        return data

How to implement data authorization logic inside a Posgres db?

2 Answers2