Search Postgresql Archives

Re: Can db user change own password?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Wed, 20 Oct 2021 at 20:52, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux