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

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(at)yahoo(dot)com>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(dot)aroy(at)gmail(dot)com>
> *To:* Melvin Davidson <melvin6925(at)yahoo(dot)com>; pgsql-general(at)postgresql(dot)org
> *Cc:* Ravi Roy <ravi(dot)aroy(at)gmail(dot)com>
> *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
>
>
>