Adrian Klaver-4 wrote
> On 05/11/2014 10:17 AM, Ravi Roy wrote:
>> Thanks a lot Tom, it worked by putting off the read only mode to off
>> before changing the password and putting it on again.
>>
>>> SET default_transaction_read_only = off;
>>
>> Worked for me..
>
> It works but the point Tom was making is here:
>
> "You realize, I hope, that breaking out of that restriction is no harder
> than issuing
>
> SET default_transaction_read_only = off;
>
> or even
>
> BEGIN TRANSACTION READ WRITE;
>
> So that ALTER ROLE might be of some use as a protection against accidental
> changes, but it's certainly no form of security restriction. (What you
> probably want to do instead of this is make sure the role doesn't have
> select/update/delete privileges for any of your tables.)
> "
>
> Given that in your original post you said:
>
> "Because I wanted this role to readonly (can not change anything in DB
> but only view)."
>
>
> you might want to rethink what you are doing.
IOW - default_transaction_read_only IS NOT a substitute for properly
granting SELECT permissions to the proper tables and view - along with USAGE
on corresponding schema and those functions that are necessary to use
particular views wrapping them - and making sure that no INSERT/DELETE or
similar permissions have been granted directly or indirectly to that user.
It is a convenience capability - not a security knob.
Note, too, that typically it is better to perform the GRANT to a "group
role" that does not use a password then allow the appropriate user role(s)
to inherit from that group as well as manage their password.
It is unclear *just looking at the documentation* (surrounding SET and
ALTER/CREATE ROLE) whether settings are inherited and, if so, what occurs
when both the parent and child role define the same setting...I would
suppose the child's would win, if present, otherwise use the parent, if
present, otherwise use the database setting - with the various database-role
combinations taking priority over any of these single object assignments.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Creating-a-role-with-read-only-privileges-but-user-is-allowed-to-change-password-tp5803562p5803580.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
