Upgrade PG from 12 to latest

[Doron Tsur]

Hello Folks,

We are running PG 12 on AWS RDS. I want to run an upgrade process for the
DB. The org I work for attempted to upgrade before but failed (12->15). The
upgrade resulted in unexplained slowness, and performance issues were only
resolved via a downgrade. Do you have any upgrade docs or recommendations
you can share about the upgrade process before I try to go on the journey?


Thank you,
-Doron

Stack Smashing Detected When Executing initdb

[Xu Haorong]

Hi everyone,

Today I tried to build the latest devel version of PostgreSQL(commit 
70a845c04a47645b58f8276a6b3ab201ea8ec426). The compilation was successful, but 
when I ran initdb an error occured:

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... *** stack smashing detected ***: 
terminated
Aborted (core dumped)
child process exited with exit code 134
initdb: removing contents of data directory "/usr/local/pgsql/data"

After searching for solutions on the Internet, I disabled the stack smashing 
protector by performing
./configure CC='clang -fno-stack-protector'

However, the problem still exists. Is there anyone else having this issue?

Regards,
Haorong Xu

Re: Upgrade PG from 12 to latest

[Muhammad Ikram]

Hi Doron Tsur,

Could you please look into

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html
https://aws.amazon.com/blogs/database/best-practices-for-upgrading-amazon-rds-to-major-and-minor-versions-of-postgresql/

Best of luck :)



Regards,
Muhammad Ikram
Bitnine Global.

On Sun, Jun 23, 2024 at 12:09 PM Doron Tsur  wrote:

> Hello Folks,
>
> We are running PG 12 on AWS RDS. I want to run an upgrade process for the
> DB. The org I work for attempted to upgrade before but failed (12->15). The
> upgrade resulted in unexplained slowness, and performance issues were only
> resolved via a downgrade. Do you have any upgrade docs or recommendations
> you can share about the upgrade process before I try to go on the journey?
>
>
> Thank you,
> -Doron
>
>

-- 
Muhammad Ikram

Re: pg_dump restores as expected on some machines and reports duplicate keys on others

[Shaheed Haque]

First, thanks for all the kind replies.

To my eternal shame, after spending hours trying to debug this, I found,
buried deep in one of my own initialisation scripts, the creation of a
handful of "seed" database objects which, of course, caused all my woes.

Thanks again,

Shaheed

Re: Stack Smashing Detected When Executing initdb

[Kashif Zeeshan]

Hi Xu

To pinpoint the issue, please use gdb debugger to get the backtrace to
exactly find out where the error is occurring and you can then do the
required setups.


For Reference :
https://stackoverflow.com/questions/50093327/how-do-i-solve-postgresql-aborted-core-dumped-error


Thanks
Kashif Zeeshan

On Sun, Jun 23, 2024 at 12:19 PM Xu Haorong  wrote:

> Hi everyone,
>
> Today I tried to build the latest devel version of PostgreSQL(commit
> 70a845c04a47645b58f8276a6b3ab201ea8ec426). The compilation was successful,
> but when I ran initdb an error occured:
>
> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
> The files belonging to this database system will be owned by user
> "postgres".
> This user must also own the server process.
>
> The database cluster will be initialized with locale "en_US.UTF-8".
> The default database encoding has accordingly been set to "UTF8".
> The default text search configuration will be set to "english".
>
> Data page checksums are disabled.
>
> fixing permissions on existing directory /usr/local/pgsql/data ... ok
> creating subdirectories ... ok
> selecting dynamic shared memory implementation ... posix
> selecting default "max_connections" ... 100
> selecting default "shared_buffers" ... 128MB
> selecting default time zone ... Asia/Shanghai
> creating configuration files ... ok
> running bootstrap script ... ok
> *performing post-bootstrap initialization ... *** **stack smashing
> detected** ***: terminated*
> *Aborted (core dumped)*
> *child process exited with exit code 134*
> *initdb: removing contents of data directory "/usr/local/pgsql/data"*
>
> After searching for solutions on the Internet, I disabled the stack
> smashing protector by performing
> *./configure CC='clang -fno-stack-protector'*
>
> However, the problem still exists. Is there anyone else having this issue?
>
> Regards,
> Haorong Xu
>

Re: Password complexity/history - credcheck?

[Martin Goodson]

On 23/06/2024 01:23, Tom Lane wrote:


Don't suppose it would help to push back on whether your security
team knows what they're doing.
...
Anyway, considerations like these are why there's not features
of this sort in community PG.  You can use an extension that
applies some checks, but there's no good way around the "needs
cleartext password" problem for that.

regards, tom lane


I believe that our security team is getting most of this from our 
auditors, who seem convinced that minimal complexity, password history 
etc are the way to go despite the fact that, as you say, server-side 
password checks can't really be implemented when the database receives a 
hash rather than a clear text password and password minimal complexity 
etc is not perhaps considered the gold standard it once was.


In fact, I think they see a hashed password as a disadvantage.

credcheck seems to satisfy their requirements - password complexity, 
password history, etc but - and this is the crucial bit - only on 
cleartext passwords.


If I'm forced to go to cleartext passwords, which would be a nightmare, 
credcheck might be worth looking at, but I'm not sure whether or not it 
is well adopted, reliable, and without significant issues. I only heard 
about it a few days ago from a friend/colleague, so I was wondering if 
anybody else was using it and what experiences with it might be.


Regards,

Martin.

--
Martin Goodson.

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."

Re: Password complexity/history - credcheck?

[Christoph Moench-Tegeder]

## Martin Goodson (kaema...@googlemail.com):

> I believe that our security team is getting most of this from our
> auditors, who seem convinced that minimal complexity, password history
> etc are the way to go despite the fact that, as you say, server-side
> password checks can't really be implemented when the database receives
> a hash rather than a clear text password and password minimal
> complexity etc is not perhaps considered the gold standard it once
> was.

The current state of the art is documented (as in, "official", for
arguing with auditors) at
https://pages.nist.gov/800-63-3/sp800-63b.html#sec5

My advice would be to not use secrets stored in the database -
that is, do not use scram-sha-256 - but use an external authentication
system, like Kerberos (might be AD) or LDAP (might also be AD) and have
that managed by the security team: that way all these compliance
topics which they brought up also become their problem :) and a lot
of the processes of recovering and disabling accounts and changing
passords move into a central location, which is most often a good thing[tm].
Or maybe move to client certificates, but if you're managing more
than a few personal accounts rotating certificates might become a
hassle (depending on your user base).

Regards,
Christoph

-- 
Spare Space

Re: Password complexity/history - credcheck?

[Martin Goodson]

On 23/06/2024 11:49, Christoph Moench-Tegeder wrote:


My advice would be to not use secrets stored in the database -
that is, do not use scram-sha-256 - but use an external authentication
system, like Kerberos (might be AD) or LDAP (might also be AD) and have
that managed by the security team: that way all these compliance


Crikey, that would be  quite a lot of  lot of SSL/TLS to set up. We have 
quite a few (massive understatement :( ... ) PostgreSQL database 
clusters spread over quite a lot (another understatement) of VMs.


The last time I suggested LDAP there was a lot of enthusiasm ... until 
they went down and looked at what might have to be done, after which it 
all became very quiet ...


Regards,

Martin.

Re: Stack Smashing Detected When Executing initdb

[Kashif Zeeshan]

Hi Xu

You need to enable some settings for the code dump to generated.

For the settings you can use following link.
https://access.redhat.com/solutions/4896

Regards
Kashif Zeeshan

On Sun, Jun 23, 2024 at 7:07 PM Xu Haorong  wrote:

> Hi Zeeshan,
>
> Thank you for helping me. I tried to analyze the core dump file, but
> unfortunately it didn't seem to have generated one. Therefore I ran initdb
> with gdb, which produced following output:
>
> Program received signal SIGPIPE, Broken pipe.
> 0x7791c574 in __GI___libc_write (fd=4, buf=0x55636a60,
> nbytes=64) at ../sysdeps/unix/sysv/linux/write.c:26
> warning: 26 ../sysdeps/unix/sysv/linux/write.c: No such file or directory
>
> The error message clearly indicates that something like write.c is
> missing, which is included in GNU C Library. However, I found that I've
> already installed it on my computer. It also confuses me that there was no
> core dump file at all, since initdb says "Aborted (core dumped)". I'd
> really appreciate if you can give me further information.
>
> Regards,
> Haorong Xu
> --
> *发件人:* Kashif Zeeshan 
> *发送时间:* 2024年6月23日 16:50
> *收件人:* Xu Haorong 
> *抄送:* pgsql-gene...@postgresql.org 
> *主题:* Re: Stack Smashing Detected When Executing initdb
>
> Hi Xu
>
> To pinpoint the issue, please use gdb debugger to get the backtrace to
> exactly find out where the error is occurring and you can then do the
> required setups.
>
>
> For Reference :
> https://stackoverflow.com/questions/50093327/how-do-i-solve-postgresql-aborted-core-dumped-error
>
>
> Thanks
> Kashif Zeeshan
>
> On Sun, Jun 23, 2024 at 12:19 PM Xu Haorong 
> wrote:
>
> Hi everyone,
>
> Today I tried to build the latest devel version of PostgreSQL(commit
> 70a845c04a47645b58f8276a6b3ab201ea8ec426). The compilation was successful,
> but when I ran initdb an error occured:
>
> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
> The files belonging to this database system will be owned by user
> "postgres".
> This user must also own the server process.
>
> The database cluster will be initialized with locale "en_US.UTF-8".
> The default database encoding has accordingly been set to "UTF8".
> The default text search configuration will be set to "english".
>
> Data page checksums are disabled.
>
> fixing permissions on existing directory /usr/local/pgsql/data ... ok
> creating subdirectories ... ok
> selecting dynamic shared memory implementation ... posix
> selecting default "max_connections" ... 100
> selecting default "shared_buffers" ... 128MB
> selecting default time zone ... Asia/Shanghai
> creating configuration files ... ok
> running bootstrap script ... ok
> *performing post-bootstrap initialization ... *** **stack smashing
> detected** ***: terminated*
> *Aborted (core dumped)*
> *child process exited with exit code 134*
> *initdb: removing contents of data directory "/usr/local/pgsql/data"*
>
> After searching for solutions on the Internet, I disabled the stack
> smashing protector by performing
> *./configure CC='clang -fno-stack-protector'*
>
> However, the problem still exists. Is there anyone else having this issue?
>
> Regards,
> Haorong Xu
>
>

回复: Stack Smashing Detected When Executing initdb

[Xu Haorong]

Hi Zeeshan,

Thank you for replying me. I'm aware that core dump files are not generated by 
default, so I did change my settings to allow them to be generated. But initdb 
still doesn't generate any core dump file, that's what makes this issue 
especially unusual.

Regards,
Haorong Xu

发件人: Kashif Zeeshan 
发送时间: 2024年6月23日 22:13
收件人: Xu Haorong ; Postgres General 

主题: Re: Stack Smashing Detected When Executing initdb

Hi Xu

You need to enable some settings for the code dump to generated.

For the settings you can use following link.
https://access.redhat.com/solutions/4896

Regards
Kashif Zeeshan

On Sun, Jun 23, 2024 at 7:07 PM Xu Haorong 
mailto:db_haor...@outlook.com>> wrote:
Hi Zeeshan,

Thank you for helping me. I tried to analyze the core dump file, but 
unfortunately it didn't seem to have generated one. Therefore I ran initdb with 
gdb, which produced following output:

Program received signal SIGPIPE, Broken pipe.
0x7791c574 in __GI___libc_write (fd=4, buf=0x55636a60, nbytes=64) 
at ../sysdeps/unix/sysv/linux/write.c:26
warning: 26 ../sysdeps/unix/sysv/linux/write.c: No such file or directory

The error message clearly indicates that something like write.c is missing, 
which is included in GNU C Library. However, I found that I've already 
installed it on my computer. It also confuses me that there was no core dump 
file at all, since initdb says "Aborted (core dumped)". I'd really appreciate 
if you can give me further information.

Regards,
Haorong Xu

发件人: Kashif Zeeshan mailto:kashi.zees...@gmail.com>>
发送时间: 2024年6月23日 16:50
收件人: Xu Haorong mailto:db_haor...@outlook.com>>
抄送: pgsql-gene...@postgresql.org 
mailto:pgsql-gene...@postgresql.org>>
主题: Re: Stack Smashing Detected When Executing initdb

Hi Xu

To pinpoint the issue, please use gdb debugger to get the backtrace to exactly 
find out where the error is occurring and you can then do the required setups.


For Reference : 
https://stackoverflow.com/questions/50093327/how-do-i-solve-postgresql-aborted-core-dumped-error


Thanks
Kashif Zeeshan

On Sun, Jun 23, 2024 at 12:19 PM Xu Haorong 
mailto:db_haor...@outlook.com>> wrote:
Hi everyone,

Today I tried to build the latest devel version of PostgreSQL(commit 
70a845c04a47645b58f8276a6b3ab201ea8ec426). The compilation was successful, but 
when I ran initdb an error occured:

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... *** stack smashing detected ***: 
terminated
Aborted (core dumped)
child process exited with exit code 134
initdb: removing contents of data directory "/usr/local/pgsql/data"

After searching for solutions on the Internet, I disabled the stack smashing 
protector by performing
./configure CC='clang -fno-stack-protector'

However, the problem still exists. Is there anyone else having this issue?

Regards,
Haorong Xu

Re: Password complexity/history - credcheck?

[Greg Sabino Mullane]

On Sun, Jun 23, 2024 at 5:30 AM Martin Goodson 
wrote:

> I believe that our security team is getting most of this from our
> auditors, who seem convinced that minimal complexity, password history
> etc are the way to go despite the fact that, as you say, server-side
> password checks can't really be implemented when the database receives a
> hash rather than a clear text password and password minimal complexity
> etc is not perhaps considered the gold standard it once was.
>
> In fact, I think they see a hashed password as a disadvantage.


Wow, full stop right there. This is a hill to die on.

Push back and get some competent auditors. This should not be a DBAs
problem. Your best bet is to use Kerberos, and throw the password
requirements out of the database realm entirely.

Also, the discussion should be about 2FA, not password history/complexity.

Cheers,
Greg

Re: Stack Smashing Detected When Executing initdb

[Tom Lane]

Xu Haorong  writes:
> Today I tried to build the latest devel version of PostgreSQL(commit 
> 70a845c04a47645b58f8276a6b3ab201ea8ec426). The compilation was successful, 
> but when I ran initdb an error occured:

> performing post-bootstrap initialization ... *** stack smashing detected ***: 
> terminated
> Aborted (core dumped)
> child process exited with exit code 134

No such problem is visible in our build farm [1], so what we have to
figure out is how your machine is different from all of those.
You've provided exactly zero context:

* What platform is this on (be specific)?  Is the system software
  up-to-date?
* What C compiler are you using, and what version exactly?
* What configure options did you use?

regards, tom lane

[1] https://buildfarm.postgresql.org/cgi-bin/show_status.pl

Re: Stack Smashing Detected When Executing initdb

[Tom Lane]

I wrote:
> Xu Haorong  writes:
>> performing post-bootstrap initialization ... *** stack smashing detected 
>> ***: terminated
>> Aborted (core dumped)
>> child process exited with exit code 134

> No such problem is visible in our build farm [1], so what we have to
> figure out is how your machine is different from all of those.

Also, before you spend a lot of time chasing this, make sure it's
not a mirage.  Reset your source tree fully with "git clean -dfxq"
then configure, make, make install; then see if problem still exists.

If it does, the PG community's accumulated wisdom about getting stack
traces is here:

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

Note that the "child process exited" message you show implies that the
failure was not in initdb itself, but in the single-user postgres
backend process that it spawns.  This means that any core file would
have been dumped into the created data directory, so you would have
to use initdb's --no-clean option to prevent it from being removed
immediately.

Also, if you are using a systemd-based Linux distribution, you may
have to negotiate with systemd-coredump to get back any core dump
at all.  "man 5 core" can be helpful reading here (personally
I just disable systemd-coredump per the directions shown there).

regards, tom lane

How to use createdb command with newly created user?

[毛毛]

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?

[David G. Johnston]

On Sun, Jun 23, 2024, 11:43 毛毛  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?

[Tom Lane]

"David G. Johnston"  writes:
> On Sun, Jun 23, 2024, 11:43 毛毛  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?

[毛毛]

Thank you! You are right!


After putting quotes around the username, it works!








在 2024-06-24 02:47:44，"David G. Johnston"  写道：




On Sun, Jun 23, 2024, 11:43 毛毛  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?

[Ron Johnson]

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 毛毛  wrote:

>
>
> Thank you! You are right!
>
> After putting quotes around the username, it works!
>
>
>
> 在 2024-06-24 02:47:44，"David G. Johnston"  写道：
>
>
>
> On Sun, Jun 23, 2024, 11:43 毛毛  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?

[毛毛]

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"  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 毛毛  wrote:








Thank you! You are right!


After putting quotes around the username, it works!








在 2024-06-24 02:47:44，"David G. Johnston"  写道：




On Sun, Jun 23, 2024, 11:43 毛毛  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?

[Muhammad Ikram]

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 毛毛  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"  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 毛毛  wrote:
>
>>
>>
>> Thank you! You are right!
>>
>> After putting quotes around the username, it works!
>>
>>
>>
>> 在 2024-06-24 02:47:44，"David G. Johnston" 
>> 写道：
>>
>>
>>
>> On Sun, Jun 23, 2024, 11:43 毛毛  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?

[Adrian Klaver]

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.kla...@aklaver.com

Re: Stack Smashing Detected When Executing initdb

[Xu Haorong]

Hi Lane,

Thank you for your advice. The problem did disappear after running git clean, 
and I'm sorry for not providing enough context at first.

Regards,
Haorong Xu

Re: Stack Smashing Detected When Executing initdb

[Kashif Zeeshan]

Thats great, it also gave us a learning opportunity as well.


On Mon, Jun 24, 2024 at 10:18 AM Xu Haorong  wrote:

> Hi Lane,
>
> Thank you for your advice. The problem did disappear after running git
> clean, and I'm sorry for not providing enough context at first.
>
> Regards,
> Haorong Xu
>