Re: 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

On Sun, May 11, 2014 at 9:06 PM, Ravi Roy <ravi(dot)aroy(at)gmail(dot)com> wrote:

> Thanks Melvin for your reply.
>
> >First, it would be helpful if you indicated the O/S and version of
> >PostgreSQL you are using.
> I'm using Postgresl 9.1 on Windows 7 (64 bit) and Debian Linux 6.x (64 bit)
>
> >Also, it is not a good idea to use CamelCase names in POstgreSQL. There
> is >no need for >that and PostgreSQL will always try to convert names to
> >lowercase unless bounded by >double quotes.
>
> I just wrote for clarity :-)
>
> >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?
>
> 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
> Ravi
>
>
> On Sun, May 11, 2014 at 8:52 PM, Melvin Davidson <melvin6925(at)yahoo(dot)com>wrote:
>
>> Ravi,
>>
>> First, it would be helpful if you indicated the O/S and version of
>> PostgreSQL you are using.
>>
>> Also, it is not a good idea to use CamelCase names in POstgreSQL. There
>> is no need for that and PostgreSQL will always try to convert names to
>> lowercase unless bounded by double quotes.
>>
>> 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?
>>
>> Melvin Davidson
>>
>> Folk Alley - All Folk - 24 Hours a day
>> www.folkalley.com
>>
>>
>> ------------------------------
>> *From:* Ravi Roy <ravi(dot)aroy(at)gmail(dot)com>
>> *To:* pgsql-general(at)postgresql(dot)org
>> *Cc:* Ravi Roy <ravi(dot)aroy(at)gmail(dot)com>
>> *Sent:* Sunday, May 11, 2014 10:17 AM
>> *Subject:* [GENERAL] Creating a role with read only privileges but user
>> is allowed to change password
>>
>> Hi Guys,
>>
>> I've created a role named "MyRole" in posgresql with the following :
>>
>> CREATE ROLE "MyRole" NOSUPERUSER LOGIN NOCREATEDB NOCREATEROLE NOINHERIT
>> PASSWORD "MyPassword";
>>
>> ALTER ROLE "MyRole" set default_transaction_read_only = on;
>>
>> Because I wanted this role to readonly (can not change anything in DB but
>> only view).
>>
>> But later I realized this role is not even allowed to change his
>> password. I've tried several permutations and combinations but it does not
>> work.
>>
>> Can somebody help as how to allow this role to change his password only
>> and everything else is read only? I'm not sure if this is possible.
>>
>> Thanks.
>> Regards
>> Ravi.
>>
>>
>>
>>
>