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 <melvin6...@yahoo.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.a...@gmail.com> > *To:* Melvin Davidson <melvin6...@yahoo.com>; pgsql-general@postgresql.org > *Cc:* Ravi Roy <ravi.a...@gmail.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 > > >
