Re: Can db user change own password?

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(dot)github(at)gmail(dot)com> wrote:
>
>
>
> On Wed, 20 Oct 2021 at 20:52, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(at)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(at)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(at)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(at)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(at)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(at)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(at)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(at)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(at)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