Обсуждение: 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 https://www.postgresql.org/message-id/flat/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.

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.

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 as
>
> group_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

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.

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://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Fapp-psql.html&amp;data=05%7C01%7C%7Cc83926a17401426a2a0108dac8eebb62%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638043224916120207%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=Ut5e4LHw0nElI7xA6EL3v59LPApIn95RAfUpK1Mvq%2FQ%3D&amp;reserved=0

for more information.

>
> Murillo.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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.

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