Can I use the same role for auto-provisioning DB users in Postgresql and MySQL ?

[vmignot]

Hello,

Technical env

• Kubernetes 1.28 on AWS (EKS)
• Teleport 15.3.7, installed via official Helm Chart (teleport-cluster with Operator enabled & teleport-kube-agent)
• AWS RDS with PostgreSQL instances, Aurora-postgresql, Aurora-mysql and MariaDB

What am I trying to achieve?

• I am trying to give access to a user to different kind of DB engine (mysql, postgresql)
• I want to leverage the auto-user creation
• I followed both Postgresql and Mysql guides for auto-user provisionning. Postgresql's one speaks of spec.allow.db_permissions but Mysql's one speaks of spec.allow.db_roles

From my understanding (and reading the documentation), I see a limitation to what I'm trying to achieve.
In a postgresql environment, the users GRANTS are done via the spec.allow.db_permissions attribute in the role. This works great with the following role:

apiVersion: resources.teleport.dev/v1
kind: TeleportRoleV7
metadata:
  name: tests
spec:
  allow:
    app_labels:
      '*': '*'
    db_labels:
      '*': '*'
    db_names:
    - '*'
    db_permissions:
    - match:
        database:
        - '{{internal.db_names}}'
        name:
        - '!pg_statistic'
        object_kind:
        - table
      permissions:
      - SELECT
      - INSERT
      - UPDATE
      - DELETE
      - TRUNCATE
      - REFERENCES
      - TRIGGER
 ...
  deny: {}
  options:
    create_db_user: true
    create_db_user_mode: keep

But I have not been able to make that work when initiating a mysql connection.
The documentation is quite different on this case, because it refers to the spec.allow.db_roles attribute, that is not part of spec.allow.db_permissions, but furthermore is mutually exclusive with it (see RFD).

Maybe the solution is to create two roles (one with spec.allow.db_roles and one with spec.allow.db_permissions, that my user should both be part of? But I am not sure what behavior will result of such a configuration.

So my question is, what is the best way to achieve what I'm trying to do?

Thanks! :-)

[vmignot]

This is what I end-up having with two roles (using spec.allow.db_labels.engine for role filtering):

apiVersion: resources.teleport.dev/v1
kind: TeleportRoleV7
metadata:
  name: tests-psql
spec:
  allow:
    db_labels:
      engine:
      - aurora-postgresql
      - postgres
    db_names:
    - '{{internal.db_names}}'
    db_permissions:
    - match:
        database:
        - '{{internal.db_names}}'
        name:
        - '!pg_statistic'
        object_kind:
        - table
      permissions:
      - SELECT
      - INSERT
      - UPDATE
      - DELETE
      - TRUNCATE
      - REFERENCES
      - TRIGGER
 ...
  deny: {}
  options:
    create_db_user: true
    create_db_user_mode: keep

Then, in addition to what is in the MySQL Automatic User Provisioning Guide I create a role named admin:

CREATE ROLE IF NOT EXISTS 'read-only', 'admin'@'%' ;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'admin' ;

(please do not be alarmed by the extend of the GRANTs, it's for my testing purpose.

And then, the role for MySQL

apiVersion: resources.teleport.dev/v1
kind: TeleportRoleV7
metadata:
  name: tests-mysql
spec:
  allow:
    db_labels:
      engine:
      - aurora-mysql
      - mariadb
    db_names:
    - '{{internal.db_names}}'
    db_roles:
    - read-write
...
  deny: {}
  options:
    create_db_user: true
    create_db_user_mode: keep

Is this the correct way to do this?

Thanks!

[webvictim]

Yes, this looks correct to me. Having two roles which target different databases is perfectly fine and will
work as you expect.