Re: Creating a role with read only privileges but user is allowed to change password

[Ravi Roy <ravi.aroy@xxxxxxxxx>]

Thanks Melvin for your investigation.

>Could it be you were enclosing the password in double, not single quotes >that caused the problem?
In fact i'm using single quote for password.

>1. Try connecting as user postgres, then DROP ROLE "MyRole";
>and recreate as above.
I just tested on Windows 7 and it works as expected.
But it gives the error on Debian Linux (64 bit)

>Verify you are currently logged in as user "MyRole"
>with 
>SELECT CURRENT_USER;

Yes, i'm loggged in as "MyRole" user to change the password

using command : ALTER ROLE MyRole WITH PASSWORD 'myPassword';

but it gives : ERROR: cannot execute ALTER ROLE in a read-only transaction.

Do not understand why.

Thanks again.

Regards,

Ravi.

On Sun, May 11, 2014 at 10:12 PM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

Ravi,

 
This works for me on Windows XP PostgresQL 9.2
psql -U postgres postgres
postgres#: CREATE ROLE "userguy" NOSUPERUSER LOGIN NOCREATEDB NOCREATEROLE NOINHERIT PASSWORD 'password';
CREATE ROLE
postgres#: \q

psql -U userguy postgres
postgres#: ALTER ROLE userguy WITH PASSWORD 'newpass';
ALTER ROLE
postgres#: \q

Could it be you were enclosing the password in double, not single quotes that caused the problem?

1. Try connecting as user postgres, then DROP ROLE "MyRole";
and recreate as above.

2. Verify you are currently logged in as user "MyRole"
with
SELECT CURRENT_USER;

Good luck and may the bytes be with you.

Melvin Davidson


I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Folk Alley - All Folk - 24 Hours a day
www.folkalley.com

---

From: Ravi Roy <ravi.aroy@xxxxxxxxx>
To: Melvin Davidson <melvin6925@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx
Cc: Ravi Roy <ravi.aroy@xxxxxxxxx>
Sent: Sunday, May 11, 2014 11:49 AM
Subject: Re: [GENERAL] Creating a role with read only privileges but user is allowed to change password

Thanks Melvin for your reply. Sorry I missed the command i'm using.

>Finally, I see no reason why this user should not be able to change it's >own password. >What is the exact command you are using to try to change the >password? What is the >exact error?

Command : ALTER ROLE MyRole WITH PASSWORD 'myPassword'

It first confirm the existing password which is fine and authorization is granted.

As soon I try to change the password it give me the error :

ERROR: Can not ALTER ROLE in a read only transaction.

Thanks!

Regards