1

I have an existing API connected to an AWS PostgreSQL database that uses AWS Cognito for User authentication.

The goal is for users to insert data via the API with some field mapped to their Cognito id, and retrieve the same data. The idea would be for each user to only have access to the data 'owned' by them. Similarly to the way row level access works.

But I do not want to create a role for each user which seems to be necessary.

The idea would be that I need to somehow setup a connection to the PostgreSQL DB with the user_id without creating a user and handle the accessible data via a policy, or somehow pass the data to the policy directly.

What would be an ideal way to do this, or is creating a PG user for each user a necessity for this setup?

Thanks in advance

EDIT: I am currently querying the database through my backend with custom code. But I would rather have a system where instead of writing the code myself, the PostgreSQL system handles the security itself using policies(or something similar). I fully understand how PostgreSQL row-level-access works with roles and policies and I would prefer a system where PostgreSQL does the major work without me implementing custom back-end logic and preferably not creating thousands of PostgreSQL roles for the users.

Kival M
  • 182
  • 1
  • 10
  • What do you mean by "an existing API connected to an AWS PostgreSQL database"? Are you saying that they connect directly to the database, or do you have some back-end code running somewhere that accesses the database? Typically, your back-end code would access the database and return information to the caller. You would need to add logic to this back-end code to only access rows relevant for the user, based on their identity provided by Cognito. – John Rotenstein Feb 03 '23 at 22:28
  • @JohnRotenstein I currently have back-end code with an existing connection to the database that handles all the calls via a dedicated user. But I would like to also allow users to establish a direct connection and make queries – Kival M Feb 04 '23 at 11:53
  • "I would like to also allow users to...make queries" means you *trust* your users a lot more than I ever would. Row-level security has often been a weak-spot in any RDBMS, and even if you have that, the next thing people will want is column-level permissions, if not a full ACL, and the complexity explodes from there. This is why this is best done in the application layer, **not** the database. – tadman Feb 04 '23 at 20:34
  • 1
    I know this is a bit orthogonal to your request, but the way this is likely to be solved for future applications is through a more flexible query layer like [GraphQL](https://graphql.org) where they may not be able to do SQL queries, but they can get the data they need through an API with considerable capability beyond the usual REST model. – tadman Feb 04 '23 at 20:35
  • 1
    If you wanted your users to directly connect to the database, they would definitely need separate accounts with separate passwords. But it's a bad idea anyway. So yes, you'll still need your backend. – Bergi Feb 04 '23 at 22:09
  • 2
    However, you can leave the data visibility aspect to postgres, using RLS policies on the tables. A single role is sufficient, it is customary to pass the user id (Cognito ID in your case) via a [custom](https://www.postgresql.org/docs/current/runtime-config-custom.html) [session parameter](https://www.postgresql.org/docs/current/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION). You still need the backend to deal with the authentication, pass it to postgres, and make sure the user cannot execute arbitrary SQL (which would trivially allow changing the parameter to impersonate others). – Bergi Feb 04 '23 at 22:15
  • @Bergi that is more or less what i was trying to find. Thank you – Kival M Feb 07 '23 at 14:52
  • If it matters at all that one of your users shouldn't see another user's data, then please don't do this. Don't expose your database to your end users. Good luck. – ron rothman Jun 27 '23 at 15:54

1 Answers1

2

You should not allow users to make a direct connection to the database.

Instead, they should make requests to your back-end, where you have business logic that determines what each user is permitted to access. Your back-end then makes the appropriate calls to the database and returns the response to the user.

This is a much 'safer' response because it prevents users having direct access to your database and it is also a better architecture because it allows you to swap-out the database engine for another one without impacting your service.

The database is for your application, not for your users.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • 1
    Good advice but does nothing to answer the question. The OP did not specify who/where the connection is made form. Only that they would like to use postgres RLS without creating a role for each user which is a valid question regardless of where you connect from. – ste_kwr Feb 05 '23 at 14:49