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

I'm sorry, i forgot to mention my environemnt :
Postgresql version 9.1 on Windows 7 (64 bit) and Debian Linux 6.x (64 bit)

Thanks!
Regards
Ravi.

On Sun, May 11, 2014 at 7:47 PM, Ravi Roy <ravi.aroy@gmail.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@yahoo.com>wrote:

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.aroy@gmail.com> wrote:

Ravi Roy <ravi.aroy@gmail.com> writes:

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

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

But later I realized this role is not even allowed to change his password.

Just do one of the above things first...

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 <melvin6925@yahoo.com>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..

Many thanks to you!

Regards
Ravi

On Sun, May 11, 2014 at 10:26 PM, Tom Lane <tgl@sss.pgh.pa.us> 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.

Many thanks to you!

Regards
Ravi

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/05/14 06:09, Adrian Klaver 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.

Many thanks to you!

Regards
Ravi

I suggest that you move the password to a separate table
(my_role_password) with 2 columns:

1. my_role_id
2. password.

This way you can make the my_role table totally unalterable by the user,
yet they can change their own password.

Actually, you should NOT be storing passwords in plain text, they should
be stored as a secure hash (better than MD5).

Cheers,
Gavin

​I​
suggest that you move the password to a separate table (my_role_password)
with 2 columns:

1. my_role_id
2. password.

This way you can make the my_role table totally unalterable by the user,
yet they can change their own password.

Actually, you should NOT be storing passwords in plain text, they should
be stored as a secure hash (better than MD5).

​I have no clue what you are trying to get at here...the core problem is
with database defined roles - which are maintained in the system catalog -
and the fact that marking a session read-only disallows updates to the
system catalog...

I do not see how adding a user table with role and password overcomes that
problem since the user table would be read-only too - so how would they
still be able to change their password if the cannot alter the table (data
alter, not structure).

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-tp5803562p5803582.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 12/05/14 08:33, David G Johnston wrote:

​ I​
suggest that you move the password to a separate table
(my_role_password) with 2 columns:

1. my_role_id
2. password.

This way you can make the my_role table totally unalterable by the
user, yet they can change their own password.

Actually, you should NOT be storing passwords in plain text, they
should be stored as a secure hash (better than MD5).

​I have no clue what you are trying to get at here...the core problem
is with database defined roles - which are maintained in the system
catalog - and the fact that marking a session read-only disallows
updates to the system catalog...

I do not see how adding a user table with role and password overcomes
that problem since the user table would be read-only too - so how
would they still be able to change their password if the cannot alter
the table (data alter, not structure).

David J.

I was thinking of using privileges to control access, for user 'fred':

REVOKE ALL ON my_role FROM fred;
GRANT SELECT ON my_role TO fred;

That's the guts of it, you will obviously need to check other tables and
their appropriate privileges.

Note that both commands have the option 'ALL TABLES IN SCHEMA schema_name'.

Cheers,
Gavin

P.S. You forgot to include my name (and date/time) from the bit you
quoted from me!