Re: Can db user change own password?
От | Toomas |
---|---|
Тема | Re: Can db user change own password? |
Дата | |
Msg-id | 7FC82398-5C8C-4F2D-9620-D16E54E77091@gmail.com обсуждение исходный текст |
Ответ на | Re: Can db user change own password? (Vijaykumar Jain <vijaykumarjain.github@gmail.com>) |
Ответы |
Re: Can db user change own password?
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
Список | pgsql-general |
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.github@gmail.com> wrote:On Wed, 20 Oct 2021 at 20:52, Adrian Klaver <adrian.klaver@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:~$ psqlpsql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))Type "help" for help.postgres=# \duList of rolesRole name | Attributes | Member of-----------+------------------------------------------------------------+-----------postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}postgres=# create role vijay login nosuperuser password '1234';CREATE ROLEpostgres=# grant CONNECT on database postgres to vijay;GRANTpostgres=# \qpostgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1Password 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=> \passwordEnter new password:Enter it again:postgres=> \qpostgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1 # old passwordPassword 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 passwordPassword 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=> \qpostgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1Password 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=> \passwordEnter new password:Enter it again:postgres=> alter role vijay password '666'; -- trying both ways, worksALTER ROLEpostgres=> \qpostgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1Password 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:~$ psqlpsql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))Type "help" for help.postgres=# create database vijay owner vijay;CREATE DATABASEpostgres=# \qpostgres@u1:~$ psql -U vijay -p 5432 -d vijay -h 127.0.0.1Password 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, worksALTER ROLEvijay=> \qpostgres@u1:~$ psql -U vijay -p 5432 -d vijay -h 127.0.0.1Password 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
В списке pgsql-general по дате отправления:
Предыдущее
От: hubert depesz lubaczewskiДата:
Сообщение: Re: Can we get rid of repeated queries from pg_dump?