Home > mailing lists

Thread: How to use createdb command with newly created user?

How to use createdb command with newly created user?

From

毛毛

Date:

23 June, 21:43:06

Hi,

I tried to create a user with CREATEDB permission.

Then I wanted to run command line tool `createdb` with this newly created user.

So I ran SQL first to create a user:

```

CREATE USER Baba WITH PASSWORD 'xxx' CREATEDB;

```

Then I run the following command on PowerShell on Windows 10:

```

createdb -U Baba -W test_db

```

But no mater how I tried, the password always failed.

If I specify the user as postgres, the defaut user, everything works fine.

```

createdb -U postgres -W test_db_1

```

Do you have any suggestions?

Re: How to use createdb command with newly created user?

From

"David G. Johnston"

Date:

23 June, 21:47:44

On Sun, Jun 23, 2024, 11:43 毛毛 <krave@163.com> wrote:

Hi,

I tried to create a user with CREATEDB permission.
Then I wanted to run command line tool `createdb` with this newly created user.

So I ran SQL first to create a user:

```
CREATE USER Baba WITH PASSWORD 'xxx' CREATEDB;
```

Then I run the following command on PowerShell on Windows 10:

```
createdb -U Baba -W test_db
```

But no mater how I tried, the password always failed.

If I specify the user as postgres, the defaut user, everything works fine.

```
createdb -U postgres -W test_db_1
```

Do you have any suggestions?

You named the user "baba" all lower-case but your createdb command uses Baba and in the OS the case-folding of identifiers does not happen. Baba != baba is your issue.

David J.

Re: How to use createdb command with newly created user?

From

Tom Lane

Date:

23 June, 22:00:37

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sun, Jun 23, 2024, 11:43 毛毛 <krave@163.com> wrote:
>> Then I run the following command on PowerShell on Windows 10:
>> createdb -U Baba -W test_db
>> But no mater how I tried, the password always failed.

> You named the user "baba" all lower-case but your createdb command uses
> Baba and in the OS the case-folding of identifiers does not happen.  Baba
> != baba  is your issue.

FWIW, I think using -W in interactive commands is a bad habit
that you should drop.  Because it forces a password prompt, it
easily confuses people into thinking that their problem is
password-related whether it actually is or not.

(I notice that with createdb, it actually seems to force *two*
password prompts when there is something wrong.  That's unlike
what happens with psql; maybe we should try to improve that.
But on the other hand, it's hard to get excited about putting
work into improving a behavior that we deprecate using at all.)

            regards, tom lane

Re:Re: How to use createdb command with newly created user?

From

毛毛

Date:

23 June, 22:22:19

Thank you! You are right!

After putting quotes around the username, it works!

在 2024-06-24 02:47:44，"David G. Johnston" <david.g.johnston@gmail.com> 写道：

On Sun, Jun 23, 2024, 11:43 毛毛 <krave@163.com> wrote:
Hi,

I tried to create a user with CREATEDB permission.
Then I wanted to run command line tool `createdb` with this newly created user.

So I ran SQL first to create a user:

```
CREATE USER Baba WITH PASSWORD 'xxx' CREATEDB;
```

Then I run the following command on PowerShell on Windows 10:

```
createdb -U Baba -W test_db
```

But no mater how I tried, the password always failed.

If I specify the user as postgres, the defaut user, everything works fine.

```
createdb -U postgres -W test_db_1
```

Do you have any suggestions?

You named the user "baba" all lower-case but your createdb command uses Baba and in the OS the case-folding of identifiers does not happen. Baba != baba is your issue.

David J.

Re: Re: How to use createdb command with newly created user?

From

Ron Johnson

Date:

23 June, 22:38:21

Better to run now, and save yourself hassle in the future:

ALTER ROLE "Baba" RENAME TO baba;

Also, use a .pgpass file: https://www.postgresql.org/docs/14/libpq-pgpass.html

On Sun, Jun 23, 2024 at 3:22 PM 毛毛 <krave@163.com> wrote:

Thank you! You are right!
After putting quotes around the username, it works!

在 2024-06-24 02:47:44，"David G. Johnston" <david.g.johnston@gmail.com> 写道：

On Sun, Jun 23, 2024, 11:43 毛毛 <krave@163.com> wrote:
Hi,

I tried to create a user with CREATEDB permission.
Then I wanted to run command line tool `createdb` with this newly created user.

So I ran SQL first to create a user:

```
CREATE USER Baba WITH PASSWORD 'xxx' CREATEDB;
```

Then I run the following command on PowerShell on Windows 10:

```
createdb -U Baba -W test_db
```

But no mater how I tried, the password always failed.

If I specify the user as postgres, the defaut user, everything works fine.

```
createdb -U postgres -W test_db_1
```

Do you have any suggestions?

You named the user "baba" all lower-case but your createdb command uses Baba and in the OS the case-folding of identifiers does not happen. Baba != baba is your issue.

David J.

Re:Re: Re: How to use createdb command with newly created user?

From

毛毛

Date:

24 June, 06:36:33

Thank you for your advice.

.pgpass file would help a lot.

I recently started to writing SQL on PostgreSQL.

I think I should use capitial letters as sparingly as possible for identifiers.

At 2024-06-24 03:38:21, "Ron Johnson" <ronljohnsonjr@gmail.com> wrote:

Better to run now, and save yourself hassle in the future:
ALTER ROLE "Baba" RENAME TO baba;

Also, use a .pgpass file: https://www.postgresql.org/docs/14/libpq-pgpass.html

On Sun, Jun 23, 2024 at 3:22 PM 毛毛 <krave@163.com> wrote:

Thank you! You are right!
After putting quotes around the username, it works!

在 2024-06-24 02:47:44，"David G. Johnston" <david.g.johnston@gmail.com> 写道：

On Sun, Jun 23, 2024, 11:43 毛毛 <krave@163.com> wrote:
Hi,

I tried to create a user with CREATEDB permission.
Then I wanted to run command line tool `createdb` with this newly created user.

So I ran SQL first to create a user:

```
CREATE USER Baba WITH PASSWORD 'xxx' CREATEDB;
```

Then I run the following command on PowerShell on Windows 10:

```
createdb -U Baba -W test_db
```

But no mater how I tried, the password always failed.

If I specify the user as postgres, the defaut user, everything works fine.

```
createdb -U postgres -W test_db_1
```

Do you have any suggestions?

You named the user "baba" all lower-case but your createdb command uses Baba and in the OS the case-folding of identifiers does not happen. Baba != baba is your issue.

David J.

Re: Re: Re: How to use createdb command with newly created user?

From

Muhammad Ikram

Date:

24 June, 06:51:09

Hi Krave,

It does not matter whether you use capital letters, mixed case or lower case. Things only matter when you use double quotes. E.g. "Baba" and "BABA" are different but Baba and BABA or baba are all the same.

Double quotes make the things case sensitive. Double quoting lower case won't cause any issue (IMO) as this is the default PG case.

Regards,

Muhammad Ikram,

Bitnine Global

On Mon, Jun 24, 2024 at 8:36 AM 毛毛 <krave@163.com> wrote:

Thank you for your advice.
.pgpass file would help a lot.

I recently started to writing SQL on PostgreSQL.
I think I should use capitial letters as sparingly as possible for identifiers.

At 2024-06-24 03:38:21, "Ron Johnson" <ronljohnsonjr@gmail.com> wrote:
Better to run now, and save yourself hassle in the future:
ALTER ROLE "Baba" RENAME TO baba;

Also, use a .pgpass file: https://www.postgresql.org/docs/14/libpq-pgpass.html

On Sun, Jun 23, 2024 at 3:22 PM 毛毛 <krave@163.com> wrote:

Thank you! You are right!
After putting quotes around the username, it works!

在 2024-06-24 02:47:44，"David G. Johnston" <david.g.johnston@gmail.com> 写道：

On Sun, Jun 23, 2024, 11:43 毛毛 <krave@163.com> wrote:
Hi,

I tried to create a user with CREATEDB permission.
Then I wanted to run command line tool `createdb` with this newly created user.

So I ran SQL first to create a user:

```
CREATE USER Baba WITH PASSWORD 'xxx' CREATEDB;
```

Then I run the following command on PowerShell on Windows 10:

```
createdb -U Baba -W test_db
```

But no mater how I tried, the password always failed.

If I specify the user as postgres, the defaut user, everything works fine.

```
createdb -U postgres -W test_db_1
```

Do you have any suggestions?

You named the user "baba" all lower-case but your createdb command uses Baba and in the OS the case-folding of identifiers does not happen. Baba != baba is your issue.

David J.

Muhammad Ikram

Re: How to use createdb command with newly created user?

From

Adrian Klaver

Date:

24 June, 07:20:57

On 6/23/24 20:36, 毛毛 wrote:
> 
> 
> Thank you for your advice.
> 
> .pgpass file would help a lot.
> 
> 
> I recently started to writing SQL on PostgreSQL.
> 
> I think I should use capitial letters as sparingly as possible for 
> identifiers.

Take a look at:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

It will answer a lot of questions.


-- 
Adrian Klaver
adrian.klaver@aklaver.com