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

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.
>
>
>
>