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.
