On Wed, 20 Oct 2021 at 20:52, Adrian Klaver <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