What are alternatives for currently existing authentication scheme?

[bapcyk]

I have users with LDAP accounts and Postgres is configured to auth. them with LDAP. Also I have usual authenticator user (for PostgREST) with md5 auth. method. LDAP users are included is some roles like "admin", "guest", etc... The frontend calls PostgREST as an authenticator user, that logs in to PostgREST with his password, then, as I understand, PostgREST does "set local role", to switch to some user "Joe". I wrote custom login stored function which logs into LDAP and returns JWT token (the function gets user name "joe" and his password), very similar to the PostgREST's documentation - ...create or replace function login(email text, pass text) returns basic_auth.jwt_token ... . And so on...

But I see that if some a bad guy will steal the authenticator password, he can login under authenticator account then to do this "set local role" to "Joe", who is in "admin" group role. The problem with current authentication PostgREST scheme IMHO is that it does not log in as "joe" but as an authenticator and then it switch the role. The difference between passwords in both cases is: joe's password is in LDAP, not in my DB. But authenticator is in my DB with his password (as MD5 hash) and I should create him with his password... Is it possible to login in different way, for example, to login not as an "authenticator" but directly as "joe" ? And Postgres will authenticate "joe" via LDAP without custom function at all. Is it possible?

[steve-chavez]

Hey @bapcyk,

Sorry, I haven't used postgrest with LDAP before.

According to this so answer, couldn't you set up the authenticator credentials on pg_hba.conf and use a regular connection string(without password) for postgrest?

[bapcyk]

Yep, I did exactly this.

• all users are authenticated via LDAP
• authenticator user is authenticated as a simple/local user ("md5" method in pg_hba.conf)
• all LDAP users are included in some role (admin, guest, etc)
• also authenticator is granted to all LDAP users
• I have a custom login function which verifies the password by login to the same DB via dblink (which does it via LDAP when it is required)

This works sure, but the question is - is it possible instead of:

login-as-authenticator => set-local-role-joe => now-joe-has-admin-perms # joe is in admin group role

just to do:

login-as-joe  # directly as joe, not via "authenticator"

?

Because in the first case, everyone who has an authenticator's password can switch to any LDAP user including users with admin roles. I am not sure that the second scheme is better/more secure, it's just a question :)

[steve-chavez]

I have some thoughts about this scenario:

But I see that if some a bad guy will steal the authenticator password, he can login under authenticator account then to do this "set local role" to "Joe", who is in "admin" group role.

Disregarding the admin role

If all your tables are protected with Row Level Security and your users have uuids, even if the authenticator password is stolen, the attacker cannot see all the tables contents. He would have to try different uuids(unguessable) to access your rows by doing many set local "request.jwt.claim.id" = '<uuid>', until he gets a valid one. During this time, your intrusion system should have detected the incident and you should be able to change the password or take other measures. Am I correct?

Considering the admin role

Now, the problem comes with the admin role. If authenticator can switch to a role that has BYPASSRLS(assuming this is the case) or worse, SUPERUSER, then we don't have any protection against this scenario.

Possible solution

How about having no admin role and instead have an admins table. Your policies could include a check for user uuids that are inside this table, and you could let them do anything inside the policies.

---

@bapcyk What do you think?

[bapcyk]

How can I let them do anything inside the policies without some a role for it? admin role, to be honest, is not super-user at all, it is an admin for my business model only - it can select/update/insert only in limited set of tables, nothing more...

So, the problem IMHO is not that the user becomes super-user after switch the role by PostgREST (it does not, it takes just a limited tables set permissions, because admin is not real super-user) but that PostgREST does authentication for the authenticator only. Yes, I have a custom function which verifies the password. But I am talking about different scenario:

• I steal the authenticator password
• then I open psql and log-in as the authenticator
• then I switch to any LDAP user and get admin, guest, whatever permissions...

Because, if I use PostgREST (with my custom function login()) - everything is fine.

But if I use psql - it is not, because the existing PostgREST authentication model (as well as the existing authenticator role) is some kind of a problem IMHO. Because without it, everyone must log-in using users LDAP password - when he uses psql. But PostgREST authentication model introduces this authenticator which allows to switch to any LDAP user without real authentication at all (it delegates it to the custom login() instead of to do normal login under the LDAP user).

And the difference is that I must hardcode authenticator password in some schema - he is a local/MD5-based user, while LDAP users are protected and I don't hardcode their passwords.

The diagrams show this:

psql -U ldap_joe ... ---> I've been authenticated with LDAP password,
                          so I have admin role now

vs.

psql -U authenticator ---> I've been authenticated with stolen password,
                           then switch to ldap_joe without to authenticate
                           with ldap_joe's LDAP password. How to be sure that I
                           am really ldap_joe? No way...

custom login() function fixes it but only for scenario with PostgREST, but in the scenario with psql - the problem exists. And it happens due to authenticator role and PostgREST auth. model.

[wolfgangwalther]

I think you should set up your pg_hba.conf, so that the authenticator user can only be logged in from the host that PostgREST is running on. So even if somebody steals the authenticator password in theory, they can't use it to login.

[bapcyk]

yep, I did it

[wolfgangwalther]

Cool. Did any of the suggestions answer the question for you or is the best solution still unclear? If answered, could you mark one of the answers with "Mark as answer"?