session_user different from current_user after normal login
Hi,
We have a situation where two users are members of a group, like below:
group_read_only
|- user1
|- user2
We tried to change the password for those users, using a query like below:
ALTER ROLE user1 PASSWORD 'sup3r$4fe';
but got a "ERROR: permission denied" (using pgadmin4 and psql docker clients). Using the
"\password" command, we got:
Enter new password for user "group_read_only"
and using the command "\password user1" we got the same "ERROR: permission denied".
After further investigation, and reaching the discussion /messages/by-id/B340250F-A0E3-43BF-A1FB-2AE36003F68D@gmail.com,
we first checked that using the query below:
SELECT session_user, current_user;
We got the result:
session_user: user1
current_user: group_read_only
Following the solution in the discussion above, we solved our problem using the command:
SET SESSION AUTHORIZATION <username>;
For our example:
SET SESSION AUTHORIZATION user1;
After that, it was possible to change the user password.
My question is: is that a normal behavior? should I, after a normal loggin, be logged as
group_read_only as my current_user?
Thanks in advance.
Murillo.
On 11/17/22 07:11, Murillo corvino rocha wrote:
Hi,
We have a situation where two users are members of a group, like below:
Following the solution in the discussion above, we solved our problem
using the command:SET SESSION AUTHORIZATION <username>;
For our example:
SET SESSION AUTHORIZATION user1;
After that, it was possible to change the user password.
My question is: is that a normal behavior? should I, after a normal
loggin, be logged asgroup_read_only as my current_user?
Something or someone is doing SET ROLE group_read_only.
Thanks in advance.
Murillo.
--
Adrian Klaver
adrian.klaver@aklaver.com
I am just logging in (using psql) and doing the select: SELECT session_user, current_user;
The PostgreSQL is an AWS RDS 12.12 instance. No script is being executed. No one besides me is connected to the database. Could it be any server level configuration? I’m pretty sure that few configurations were made to this database.
Murillo.
De: Adrian Klaver<mailto:adrian.klaver@aklaver.com>
Enviado:quinta-feira, 17 de novembro de 2022 14:35
Para: Murillo corvino rocha<mailto:murillo_corvinorocha@hotmail.com>; pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
Assunto: Re: session_user different from current_user after normal login
On 11/17/22 07:11, Murillo corvino rocha wrote:
Hi,
We have a situation where two users are members of a group, like below:
Following the solution in the discussion above, we solved our problem
using the command:SET SESSION AUTHORIZATION <username>;
For our example:
SET SESSION AUTHORIZATION user1;
After that, it was possible to change the user password.
My question is: is that a normal behavior? should I, after a normal
loggin, be logged asgroup_read_only as my current_user?
Something or someone is doing SET ROLE group_read_only.
Thanks in advance.
Murillo.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 11/17/22 13:26, Murillo corvino rocha wrote:
I am just logging in (using psql) and doing the select: SELECT
session_user, current_user;The PostgreSQL is an AWS RDS 12.12 instance. No script is being
executed. No one besides me is connected to the database. Could it be
any server level configuration? I’m pretty sure that few configurations
were made to this database.
Is there a psqlrc file that has SET ROLE group_read_only;?
See the:
Files
psqlrc and ~/.psqlrc
section here:
https://www.postgresql.org/docs/current/app-psql.html
for more information.
Murillo.
--
Adrian Klaver
adrian.klaver@aklaver.com
No, I’m using a clean psql docker container everytime I need to connect to the database like below:
docker run -it --rm postgres psql -d database -h host -U user1
Murillo.
De: Adrian Klaver<mailto:adrian.klaver@aklaver.com>
Enviado:quinta-feira, 17 de novembro de 2022 19:54
Para: Murillo corvino rocha<mailto:murillo_corvinorocha@hotmail.com>; pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
Assunto: Re: RES: session_user different from current_user after normal login
On 11/17/22 13:26, Murillo corvino rocha wrote:
I am just logging in (using psql) and doing the select: SELECT
session_user, current_user;The PostgreSQL is an AWS RDS 12.12 instance. No script is being
executed. No one besides me is connected to the database. Could it be
any server level configuration? I’m pretty sure that few configurations
were made to this database.
Is there a psqlrc file that has SET ROLE group_read_only;?
See the:
Files
psqlrc and ~/.psqlrc
section here:
for more information.
Murillo.
--
Adrian Klaver
adrian.klaver@aklaver.com
Murillo corvino rocha <murillo_corvinorocha@hotmail.com> writes:
I am just logging in (using psql) and doing the select: SELECT session_user, current_user;
The PostgreSQL is an AWS RDS 12.12 instance. No script is being executed. No one besides me is connected to the database. Could it be any server level configuration? Im pretty sure that few configurations were made to this database.
I believe it's possible to do ALTER USER ... SET ROLE ... or
ALTER DATABASE ... SET ROLE ..., which'd produce this sort of
effect. psql's "\drds" metacommand would help remind you of
any such settings. I can't offhand think of any other server-side
configuration that would do it.
regards, tom lane
Could it be the role=group_read_only? The fact that user1 is a member of group_read_only is changing current_user variable?
I’m using a clean psql docker container everytime I need to connect to the database, so, I don’t think it’s related to psql.
\drds
List of settings
Role | Database | Settings
--------------+--------------+-----------------------------------
user1 | mydb | role=group_read_only
rdsadmin | rdsadmin | log_min_messages=panic
rdsadmin | | TimeZone=utc +
| | log_statement=all +
| | log_min_error_statement=debug5 +
| | log_min_messages=panic +
| | exit_on_error=0 +
| | statement_timeout=0 +
| | role=rdsadmin +
| | auto_explain.log_min_duration=-1 +
| | temp_file_limit=-1 +
| | search_path=pg_catalog, public +
| | pg_hint_plan.enable_hint=off +
| | default_transaction_read_only=off +
| | default_tablespace=
| rdsadmin | auto_explain.log_min_duration=-1
(4 rows)
Thanks.
Murillo.
De: Tom Lane<mailto:tgl@sss.pgh.pa.us>
Enviado:sexta-feira, 18 de novembro de 2022 11:31
Para: Murillo corvino rocha<mailto:murillo_corvinorocha@hotmail.com>
Cc:Adrian Klaver<mailto:adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
Assunto: Re: RES: session_user different from current_user after normal login
Murillo corvino rocha <murillo_corvinorocha@hotmail.com> writes:
I am just logging in (using psql) and doing the select: SELECT session_user, current_user;
The PostgreSQL is an AWS RDS 12.12 instance. No script is being executed. No one besides me is connected to the database. Could it be any server level configuration? I’m pretty sure that few configurations were made to this database.
I believe it's possible to do ALTER USER ... SET ROLE ... or
ALTER DATABASE ... SET ROLE ..., which'd produce this sort of
effect. psql's "\drds" metacommand would help remind you of
any such settings. I can't offhand think of any other server-side
configuration that would do it.
regards, tom lane
On 11/18/22 12:52, Murillo corvino rocha wrote:
Could it be the role=group_read_only? The fact that user1 is a member of
group_read_only is changing current_user variable?
Well role=group_read_only means this:
psql -d database -h host -U user1
effectively becomes:
psql -d database -h host -U user1
log in as user1 then
SET ROLE group_read_only;
which results in
session_user: user1
current_user: group_read_only.
user_1 being a member of group_read_only does not automatically change
the current_user to group_read_only. This happened because it was
explicitly set up do this.
I’m using a clean psql docker container everytime I need to connect to
the database, so, I don’t think it’s related to psql.\drds
List of settings
Role | Database | Settings
--------------+--------------+-----------------------------------
user1 | mydb | role=group_read_only
--
Adrian Klaver
adrian.klaver@aklaver.com
Murillo corvino rocha <murillo_corvinorocha@hotmail.com> writes:
\drds
List of settings
Role | Database | Settings
--------------+--------------+-----------------------------------
user1 | mydb | role=group_read_only
Yeah, that would explain it ... what applied that setting?
regards, tom lane