Ability to select database roles for auto-user-provisioning database session

[greedy52]

What would you like Teleport to do?
When using auto-user provisioning feature, able to select a subset of allowed database roles for a database session, e.g.:

$ tsh db connect --db-name dev --db-role roleA -r roleB my-postgres

What problem does this solve?

A Teleport user may be granted multiple database roles for a select database. When Database Service provisions the user, it tries to assign all allowed database roles and will fail the session if any of the roles do not exist on the database.

If a workaround exists, please include it.

Teleport roles can be carefully crafted so that they only match the databases that have these roles preset in the database. Not feasible when no common database roles exist on a large number of databases.

Tasks

Beta Give feedback

1. Show database roles in tsh db ls -v
2. tsh db connect --db-role
3. Teleport Connect (now tracked separately Teleport Connect to support Database Auto-user provisioning features #36703)

[Tener]

Database roles can also impose restrictions on a user, so I wonder if this feature should be opt-in? Otherwise user may sidestep the restrictions by skipping on a particular role.

[greedy52]

Database roles can also impose restrictions on a user, so I wonder if this feature should be opt-in? Otherwise user may sidestep the restrictions by skipping on a particular role.

@Tener that's a good point. At least for MongoDB, authenticationRestrictions of a role can be used to control where login from/to. Can you specify "deny" roles for Postgres and MySQL? For example, on MySQL, I cannot revoke permissions from a role that doesn't already have the grant.

[Tener]

Hmm, looking into Postgres in particular: https://www.postgresql.org/docs/current/role-attributes.html

There is "connection limit", but I think a maximum one will be taken from multiple roles, so dropping a role can only limit you. A connection limit is not a very strong limitation anyway. Otherwise I think the roles in Postgres are designed to be "positive/allow", rather than "negative/deny".

[greedy52]

Database roles can also impose restrictions on a user, so I wonder if this feature should be opt-in? Otherwise user may sidestep the restrictions by skipping on a particular role.

@jentfoo @r0mant what are your thoughts on this?

To maintain current behaviour and be safe, a new role option like select_db_roles: false can be added.

[jentfoo]

@greedy52 It may be difficult for customers to know when this option would be safe to use or not. I thought Database Permission Management was the path we were using to reduce this friction. When that RFD lands will this feature still be generally useful?

[greedy52]

thought #33734 was the path we were using to reduce this friction. When that RFD lands will this feature still be generally useful?

@jentfoo

• It will take a very long time before DB permission management can support more database types and more database object types. This feature is requested to be delivered soon.
• This feature is still useful as many databases have pre-defined built-in roles. pg_read_all_data in PostgreSQL 14+, readAnyDatabase@admin in MongoDB, etc.

[jentfoo]

If we want to introduce this maybe naming the option user_selected_db_roles would be slightly better. It helps show that this now puts the role limitations within the users control.

It adds more options, but if db roles could be marked as required (and unable to be excluded using this option), then it could provide all the options an administrator needs to ensure deny actions are enforced. In documentation we would probably describe it as only necessary if the role is restricting access.

[greedy52]

@jentfoo Could you confirm that it would be a security concern if we do not add any additional flags at all, and just always allow a user to pick a subset of db roles assigned? Would rather be simple if possible. ( I assume the answer is yes its a concern but really want your opinion)

[r0mant]

@jentfoo @greedy52 @Tener I would not introduce any new flags/options for now and just let users select roles. Seems like this is a very "edge-casey" scenario to be worth of adding another role option.

[greedy52]

@jentfoo @greedy52 @Tener I would not introduce any new flags/options for now and just let users select roles. Seems like this is a very "edge-casey" scenario to be worth of adding another role option.

Sounds good. I will add a note to the guide on this. Something like if any role is meant to restrict permissions, assign that role to every role in db_roles.