Cannot create a new user in Amazon Redshift using SQL Workbench/J

Question

I have a problem with creating a new user in Amazon Redshift. I followed the description on Getting Started with Amazon Redshift and was able to create a cluster with data from the example described there. I can connect to the cluster using the latest version of SQL Workbench/J (Build 122, 2017-02-04) and of the driver (JDBC 4.2–compatible) from an EC2 instance. It fails when I try to create a new user:

create user dbuser25x with password 'abcD1234';

One of these things happens:

I get the message confirming that the user was created, but there is no user:
```
User dbuser25x created
Execution time: 0.04s
```

The query fails:

An error occurred when executing the SQL command:
create user dbuser25x with password 'abcD1234'
[Amazon](500310) Invalid operation: user "dbuser25x" already exists;
1 statement failed.
Execution time: 0s

The query hangs.

In every situation I use this statement to check the result:

    select * from pg_user;

This user is not there and I do not know why.

Another developer tried to create a new user in the same way and got the same problems. I tried solutions from the section There Is a Potential Deadlock of Troubleshooting Queries - they did not help. I used a dedicated Windows EC2 instance on which I installed only SQL Workbench/J to exclude influence of other software - this did not solve the problem.

Aside from not appearing in pg_user, are you able to actually login as that user? — John Rotenstein
I have just tried. Unfortunately, I could not connect.Thanks for the hint. — Chris
I get the message: Connection failed. [Amazon](500310) Invalid operation: password authentication failed for the user "dbuser25x"; — Chris
If you try and connect with a different (non-existent) username, does it give the same error? That will tell whether the username is being recognised. — John Rotenstein

John Rotenstein John Rotenstein · Accepted Answer · 2017-05-15T01:04:33

It works fine for me:

create user dbuser25x with password 'abcD1234';

select * from pg_user;

rdsdb         1 true    true    true    ********    infinity    (null)
masteruser  100 true    true    false   ********    (null)  (null)
dbuser25x   101 false   false   false   ********    (null)  (null)

Cannot create a new user in Amazon Redshift using SQL Workbench/J

2 Answers