DBforPostgreSQL User can only be assigned lowercase roles

[Paul-B98]

Describe the current behavior
It seems that the current behaviour only allows the assignment of roles that are written in lowercase to a user. This results in an error that the corresponding role cannot be found when deviating from lowercase lettering. A testcase to reproduce this behaviour is to add uppercase letters or hyphens to the name of the admin role. When creating a new user that role should be assignable but is not.

Describe the improvement
It would be helpful if there is a way to also allow the assignment of roles that contains letter in uppercase or hyphens.

[theunrepentantgeek]

Do you have the exact error message available to share? This would help us isolate the underlying code.

[matthchr]

Can you also share an example user input (a User) you've given that produces an error? Don't forget to redact the password.

My quick scan of the code suggests that other than performing some sql-injection-escaping safety checks, the value you pass for roles is passed as is directly to the server, so if you have defined the roles as uppercase you should be able to refer to them as uppercase.

See https://stackoverflow.com/questions/27638358/role-names-are-case-sensitive-in-postgres which talks about this some.

You'll need to match the case of the role in the server in the User specification.

[Paul-B98]

@matthchr I’m sorry for the late response but I was busy the last two weeks. The following is a reconstruction of the issue I had with the sample of the documentation. The problem can be reduced to the point that it was not possible to add a user to the server admin role if the name contained upper case letters. I don’t have the error anymore and I hope this is enough to help you.

FlexibleServer

# FlexibleServer
apiVersion: dbforpostgresql.azure.com/v1api20230601preview
kind: FlexibleServer
metadata:
  name: samplepostgresql
  namespace: default
spec:
  location: uksouth
  owner:
    name: aso-sample-rg
  version: "16"  # We used a newer Version
  sku:
    name: Standard_D4s_v3
    tier: GeneralPurpose
  administratorLogin: adminLogin  # this was the user/role name we specifed
  administratorLoginPassword: 
    name: server-admin-pw
    key: password
  storage:
    storageSizeGB: 128

User

# User
apiVersion: dbforpostgresql.azure.com/v1
kind: User
metadata:
  name: sampleuser
  namespace: default
spec:
  owner:
    name: samplepostgresql
  roles:
    - adminLogin  #  this was the server admin user/role name we specifed. This role couldn't be found
  # Specify server-level role options of the user.
  roleOptions:
    login: true
    createRole: true
  localUser:
    serverAdminUsername: admin
    serverAdminPassword:
      name: server-admin-pw
      key: password
    password:
      name: sampleuser-password
      key: password

[theunrepentantgeek]

Adding to v2.12 to investigate where this behaviour originates - if it's an ASO bug, we'll address. If it comes from PostgreSQL, we'll need to discuss whether varying from that behaviour is appropriate.