Support PostgreSQL Database Roles and Row Security Policies

[msand]

Does Hasura support setting the role in the same way as PostGraphile?https://www.graphile.org/postgraphile/jwt-guide/

set local role $role;

I have a system where the security is implemented using PostgreSQL Database Roles and Row Security Policies, so this would be very useful to have. Is this already possible and missing from the docs, or how much work would be involved?

[coco98]

It should be possible to convert Postgres roles and RLS into Hasura’s permissions system. Hasura doesn’t use Postgres RLS so that we can be more flexible by keeping it in the Hasura layer.

How big is the set of rules? Can you share it with us on discord if possible and it might be possible to write a quick script to convert to the right JSON/yaml config for Hasura?

I’m @tanmaig on our discord. https://discord.gg/hasura

On Sat, Mar 23, 2019 at 5:16 PM, Mikael Sand [email protected] wrote:

Does Hasura support setting the role in the same way as PostGraphile? https://www.graphile.org/postgraphile/jwt-guide/

set local role $role;

I have a system where the security is implemented using PostgreSQL Database Roles and Row Security Policies, so this would be very useful to have. Is this already possible and missing from the docs, or how much work would be involved?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/hasura/graphql-engine/issues/1848, or mute the thread https://github.com/notifications/unsubscribe-auth/AAIAWFbwF92lMjLJR6Ss-1q_RnklQ9sbks5vZpm_gaJpZM4cFJpy .

[msand]

@coco98 Thanks for the offer! I'd rather see support for native Postgresql roles and use the built in row level security. Are there any blockers for supporting to set the postgres role per request?

The system also has direct sql access using the same role system, so moving it outside postgresql would have relatively severe repercussions in this case. And the use case I have would in that case be simpler to solve by writing a javascript client/adaptor for my current graphql setup.

[coco98]

Ah! This, is not on the roadmap for now but we’ll keep this issue open in case things change and track updates here :)

On Sat, Mar 23, 2019 at 6:07 PM, Mikael Sand [email protected] wrote:

@coco98 https://github.com/coco98 Thanks for the offer! I'd rather see support for native Postgresql roles and use the built in row level security. Are there any blockers for supporting to set the postgres role per request?

The system also has direct sql access using the same role system, so moving it outside postgresql would have relatively severe repercussions in this case. And the use case I have would in that case be simpler to solve by writing a javascript client/adaptor for my current graphql setup.

— You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub https://github.com/hasura/graphql-engine/issues/1848#issuecomment-475908756, or mute the thread https://github.com/notifications/unsubscribe-auth/AAIAWIuC-IBS57qdjWPIdzH1j_jL-UBdks5vZqWEgaJpZM4cFJpy .

[howesteve]

+1 on this.

Not sure if I should be writing this here or starting a new thread, but there should be a way to pass webhook/auth server variables into the session, so that we could use them in queries or RLS rules. Currently, if I get it right, to use permissions one must use the hasura permissions system since the connections run under a single role. Although the permissions system is nice, I'd rather use PostgreSQL's built-in permissions system, keeping it all in the database, since more manageable and portable. An example:

<Incoming request > => <Hasura Server> => <Webhook server/JWT auth> => {"postgresql_current_user: "howe"}

For that webhook reply, it's my understanding that hasura should emit a command (keeping the same connection from the pool):

howe=# SET SESSION AUTHORIZATION howe;
SET

... then continue processing the request normally as role "howe".

That alone would allow using everything RLS needs.

Another nice adding would be defining arbitrary settings coming from webhook/JWT other then just the user name as shown above. Examples:

<Incoming request > => <Hasura Server> => <Webhook server/JWT auth> => {"hasura_postgresql_mysetting_1: "xxx", "hasura_postgresql_mysetting_1": "yyy"}

Hasura should emit these SQL commands before starting any queries:

howe=# SET hasura_settings.my_setting_1 =  'xxx';
SET
howe=# SET hasura_settings.my_setting_2 =  'yyy';
SET

That way we could easily retrieve those settings coming from webhook/jwt token in any query or RLS check constraint:

howe=# select current_setting('hasura_settings.my_setting');
howe=# select current_setting('hasura_settings.my_setting_1');
 current_setting 
-----------------
 xxx
(1 row)

This is nice enough that we could import and use arbitrary json values coming from the webhook; just convert the string into json type and use as wished. Of course, sending all those settings in a single sql string would make it faster then sending each one in rounds.

As someone posted in another thread, simple built-in authentication is missing. Hasura could easily check a user login is valid by reconnecting with the given credentials and store the current_user in session information, and send a "SET SESSION AUTHORIZATION"... as above. That would be great for those who want a hasura-only authentication solution.

Finally, a great and simple way of authenticating and setting vars would be calling a function: howe=# create function

Thanks Howe

[howesteve]

Yet another way of implementing built-in authentication + session variables without webhooks/jwt would be, let's pre-define an auth function, then call it, and store the result into the hasura session; something like this:

<Incoming request > => <Hasura Server> => <Call hasura_auth()> => {"$current_user": "howe", "my_setting_1: "xxx", "my_setting_2": "yyy"}

create extension "pgcrypto";

create table users(name text, password text);
insert into users values ('howe', encode(digest('password', 'sha1'), 'hex'));

-- This type should be created in the hasura schema
create type hasura_auth_result as (key text, value text);

create function hasura_auth(name text, password text) 
returns setof hasura_auth_result as 
$$
declare
  r hasura_auth_result%rowtype;
  hash text;
begin
  -- (check password)
  select users.password into hash from users where users.name = 'howe';  
  if encode(digest(password, 'sha1'), 'hex') = hash then 
    r.key := '$current_user';
    r.value := name;
    return next r;
    r.key := 'my_setting_1';
    r.value := 'xxx';
    return next r;
    r.key := 'my_setting_2';
    r.value := 'yyy';
    return next r;
    -- ...
  else 
    raise exception 'Bad credentials;
  end if;
end
$$ language plpgsql;
select * from hasura_auth('howe', 'password');

As one might guess, in this example $current_user would be used on a "set session authentication" call and the other variables on a "SET hasura_settings.my_setting_1 = 'xxx';" as shown above. I really think this is much better for most authentication cases; no need to call webhook or jwt.

Please share your thoughts.

Thanks, Howe

[howesteve]

Any updates on this? I find it incredible how far this project has come, specially the built in permissions system, but without supporting the built in authentication and authorization system, which IMO should be the primary system since the project itself proposes to be a frontend to PostgreSQL. RLS and built in authentication could be as flexible as the database itself. There should be minimum configuration on hasura whenever possible. I'd contribute this, but I know no haskell...

Thanks

[creatorrr]

postgrest does a great job at this IMO. From the docs:

All claims are allowed but PostgREST cares specifically about a claim called role.

{
 "role": "user123"
}

When a request contains a valid JWT with a role claim PostgREST will switch to the database role with that name for the duration of the HTTP request.

SET LOCAL ROLE user123;

[keeganmccallum]

As I was looking to port the postgrest-style current_setting mechanism, I found something here that may prove useful for others(this would also be a good place to add in any nicer mechanism IMO): https://github.com/hasura/graphql-engine/blob/master/server/src-lib/Hasura/Db.hs#L145

Checking the docs lead me here: https://hasura.io/docs/1.0/graphql/manual/guides/auditing-tables.html#guides-auditing

What I've come up with is using:

SELECT current_setting('hasura.user', 't')::jsonb;
SELECT (current_setting('hasura.user', 't')::jsonb)->>'x-hasura-role' AS role;

To get the hasura info as a jsonb type, and then I am able to use that to set up RLS policies in my system (workaround for Row Security Policies ask from the OP). The docs state that this runs before mutations but from what I can tell in the code it'll run for every transaction. Still looking at a work around to do SET LOCAL ROLE without having to fork, but this could probably be done in the file linked above around that line as well, based on some config item.

EDIT: So seems this is only set for mutations, having this for SELECT statements would be a valuable addition. This is working well for me for mutations now

[tirumaraiselvan]

Hey folks, you can now use different database credentials (with different roles) via the newly launched Dynamic Routing feature: https://hasura.io/docs/latest/databases/database-config/dynamic-db-connection/

This is a bit different from the other ways of integrating database roles (and potentially better) but achieves the same outcome.

Please try it out on the latest version of Hasura.

[stlbucket]

@tirumaraiselvan

https://hasura.io/docs/latest/databases/database-config/dynamic-db-connection/#connection-template

in here I see that you can use request headers to select a pre-defined connection from your set

but how would I pass a request header into my mutation function sql call?

to paraphrase as it was stated at the top of this issue, something that has the effect of:

set local tenant_id {{$.request.session?["x-hasura-tenant-id"}};

or even better:

set local tenant_id {{$.request.session?["tenant-id-as-defined-in-my-jwt-from-auth-provider"]}};

[sidharthramesh]

Maybe this can be a configuration flag that enables the user session to be set in queries as well? I would really like to have the option to use RLS and provide an audit log for queries - I'm working in healthcare.

Moreover, Hasura may not be the only system using the database. Other applications can easily use the application and even implement proper RLS by setting custom claims and doing their usual SQL logic. I think having this feature would really help!

Any idea on if this issue if solved via a config flag will be accepted?