From: | Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Toomas <toomas(dot)kristin(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Can db user change own password? |
Date: | 2021-10-20 15:43:13 |
Message-ID: | CAM+6J96rw6_PA7vxbogGgnhPmha2A9Ux33sodmYwC6S404g42w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 20 Oct 2021 at 20:52, Adrian Klaver <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
From | Date | Subject | |
---|---|---|---|
Next Message | Joel Perren | 2021-10-20 16:08:01 | Re: Selectivity and row count estimates for JSONB columns |
Previous Message | Adrian Klaver | 2021-10-20 15:22:42 | Re: Can db user change own password? |