0

I'm thinking about how to hide as many attributes of relations from a user as possible & if functions can help with that.

Example:

  • The user "dummy_user" should have as little insight as possible.
  • The table "user" contains user data incl. password. The password should not be accessible for the dummy_user.
  • The function "does_password_match(user_id, password)" checks if the password matches the user.

My question now: I read in the PostgreSQL doc: "SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that owns it.". So that means I can create the function does_password_match as admin and it can access the password that way and the user is allowed to execute it with the privileges of the admin?

But the dummy_user can still see the body of the function. So you can't really hide an internal structure this way, right?

And if within this function the password is searched via a SELECT, can the dummy_user then potentially still see the value of the field? And so the password of a user can potentially still be read?

Improve this question

2 Answers 2

Reset to default
2

The most secure way is to revoke the select privilege on the columns.

Then the user will never be able to see the contents no matter what. You can't really hide the body of a function, and you can't hide the table structure from a user (e.g. by querying pg_attribute).

But the information that there is a column named "password" doesn't reveal anything the user didn't know beforehand, so I don't consider this a problem.

A password stored in the database should always be encrypted anyway and with a one way encryption, so it can't be decrypted (e.g. SHA-256).

So even in the extremely unlikely case that the user finds a way to work around the privilege system in Postgres, he would only see the encrypted value and couldn't do anything with it.

Improve this answer
7
  • So that's clear to me. dummy_user will not get SELECT permissions on the column. But then I was wondering how the auth service can still check if the password input matches? Without having to submit the encrypted password to the service and preferably keeping it internal. Ideally, from my point of view, the dummy_user never even gets to see the stored password.
    – Sven M.
    Commented Feb 12, 2021 at 12:21
  • The usual approach is to encrypt the password the user entered, then compare that value with the encrypted value in the database. Everything else is unsafe. Never, ever store password in plain text - never.
    – user1822
    Commented Feb 12, 2021 at 12:30
  • Yes, sorry, maybe I'm expressing myself wrong. That is absolutely clear to me. I was then rather wondering how a user can access it? Somehow the password must be able to be matched? I don't want to give the dummy_user SELECT rights to the password column, but then how can I match the input?
    – Sven M.
    Commented Feb 12, 2021 at 12:39
  • Your application would do that with a database user that is allowed to read the contents of the column
    – user1822
    Commented Feb 12, 2021 at 12:43
  • Okay, so there's no other way? My idea was just as described above, to create a function that does this, which accesses the column via the SECURITY DEFINER with a user with more permissions. But this does not work?
    – Sven M.
    Commented Feb 12, 2021 at 13:04
0

You could create a derived view of the table and only grant read on the view, not the underlying table.

But if you are trying to code an auth system; you should read the extensive literature available and find one written in your application language that will work for you.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.