Hi, Basically the case was, when session_user != current_user then command \password failed with error message “ERROR: permission denied”. All was good when session_user == current_user.
In terms of statement “session_user user was set as the owner of the database automatically” - I have a setup where session_user is changed automatically as database owner when user logs into database. BR, Toomas > On 20. Oct 2021, at 18:43, Vijaykumar Jain <vijaykumarjain.git...@gmail.com> > wrote: > > > > On Wed, 20 Oct 2021 at 20:52, Adrian Klaver <adrian.kla...@aklaver.com > <mailto:adrian.kla...@aklaver.com>> wrote: > On 10/20/21 08:07, Toomas wrote: > > Hi Adrian, > > > > Thank you for your help. The issue was that when user logged into database > > his session_user user was set as owner of database automatically. User had > > success to change password when session_user = current_user was set before. > > I'm not understanding. You will need to sketch this out: > > 1) Connection parameters for log in with <user_name>. > > 2) On log in the output from: select session_user, current_user; > > 3) Define '...set as owner of database automatically'. > > > Toomas, > things work for me as expected. > > I guess as asked, you may want to show an example for your below statement to > help understand better. > "The issue was that when a user logged into the database his session_user > user was set as the owner of the database automatically." > > > postgres@u1:~$ psql > psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)) > Type "help" for help. > > postgres=# \du > List of roles > Role name | Attributes | > Member of > -----------+------------------------------------------------------------+----------- > postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} > > postgres=# create role vijay login nosuperuser password '1234'; > CREATE ROLE > postgres=# grant CONNECT on database postgres to vijay; > GRANT > postgres=# \q > postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1 > Password for user vijay: > psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)) > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, > compression: off) > Type "help" for help. > > postgres=> \password > Enter new password: > Enter it again: > postgres=> \q > postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1 # old password > Password for user vijay: > psql: error: FATAL: password authentication failed for user "vijay" > FATAL: password authentication failed for user "vijay" > postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1 # new password > Password for user vijay: > psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)) > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, > compression: off) > Type "help" for help. > > postgres=> \q > > postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1 > Password for user vijay: > psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)) > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, > compression: off) > Type "help" for help. > > postgres=> select session_user, current_user; > session_user | current_user > --------------+-------------- > vijay | vijay > (1 row) > > postgres=> \password > Enter new password: > Enter it again: > postgres=> alter role vijay password '666'; -- trying both ways, works > ALTER ROLE > postgres=> \q > postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1 > Password for user vijay: > psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)) > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, > compression: off) > Type "help" for help. > > postgres=> select session_user, current_user; > session_user | current_user > --------------+-------------- > vijay | vijay > (1 row) > > > postgres@u1:~$ psql > psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)) > Type "help" for help. > > postgres=# create database vijay owner vijay; > CREATE DATABASE > postgres=# \q > postgres@u1:~$ psql -U vijay -p 5432 -d vijay -h 127.0.0.1 > Password for user vijay: > psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)) > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, > compression: off) > Type "help" for help. > > vijay=> select session_user, current_user; > session_user | current_user > --------------+-------------- > vijay | vijay > (1 row) > > vijay=> alter role vijay password '999'; -- trying both ways, works > ALTER ROLE > vijay=> \q > postgres@u1:~$ psql -U vijay -p 5432 -d vijay -h 127.0.0.1 > Password for user vijay: > psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1)) > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, > compression: off) > Type "help" for help. > > vijay=> \q
