Username case sensitivity is inconsistent

Question

The [doc](https://docs.aws.amazon.com/redshift/latest/dg/r_enable_case_sensitive_identifier.html) states that by default, Redshift will preserve the case of a username.

> The case of a username enclosed in double quotation marks is always preserved regardless of the setting of the enable_case_sensitive_identifier configuration option.

Creating a user with mixed case works as expected. However, when using the username in schema/table/column modifications, it appears that the case is ignored and lowercase is used. This behavior seems inconsistent and is hard to reason about from an application perspective. If the case of a username is preserved, then it would be beneficial if that username can be used in schema modification/DDL type statements as well. See example below.

Would someone from the AWS side be able to comment on this, and would fixing this be considered?

```sql
-- Create user "User_A" with mixed case. Works.

create user "User_A" with password disable;

-- Select user from "pg_user". Works.

select * from pg_user where usename = 'User_A';

-- Rename user. Works.

alter user "User_A" rename to "User_B";

alter user "User_B" rename to "User_A";

-- Create table.

create table "public"."table_a" ("id" int);

-- Change table owner to "User_A". Does not work! Case is apparently ignored.

alter table "public"."table_a" owner to "User_A";

-- SQL Error [42704]: ERROR: user "user_a" does not exist
```

Answer

Hello,

I was able to replicate the similar behavior with the DDL statements. As a workaround for the time being you can use the below mentioned configuration.

```
1. create user "User_A" with password disable;

2. create table "public"."table_a" ("id" int);

3. SET enable_case_sensitive_identifier TO true;

4. alter table "public"."table_a" owner to "User_A";`
```

This works and can be verified from

```
SET enable_case_sensitive_identifier TO false;

select * from pg_tables where tableowner = 'User_A' ;
```

Test 2 : Username case sensitivity works with Grant statement.

```
create user "User_B" with password disable;

grant select on table  "public"."table_a" to "User_B"  ;

SET SESSION AUTHORIZATION "User_B"

select * from "public"."table_a" ;
```

Thank you for bringing it to our notice. We will check with our team.

Answer

Great, many thanks for the response and for the work-around. I was aware of the `enable_case_sensitive_identifier` setting but enabling it globally will require a large refactor in our application.

Good to see that user case sensitivity works for grant statements. Perhaps this is just an isolated issue for the grant permission command. Would be great if it could be fixed. Thanks again.

Username case sensitivity is inconsistent

Relevant content