Home > mailing lists

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

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

From

Ravi Roy

Date:

11 May 2014, 17:17:32

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.

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

From

Ravi Roy

Date:

11 May 2014, 17:30:24

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:

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.

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

From

Ravi Roy

Date:

11 May 2014, 18:36:35

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:

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.aroy@gmail.com>
To: pgsql-general@postgresql.org
Cc: Ravi Roy <ravi.aroy@gmail.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.

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

From

Ravi Roy

Date:

11 May 2014, 18:49:57

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:

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:
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.aroy@gmail.com>
To: pgsql-general@postgresql.org
Cc: Ravi Roy <ravi.aroy@gmail.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.

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

From

Tom Lane

Date:

11 May 2014, 19:56:37

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

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

From

Ravi Roy

Date:

11 May 2014, 20:01:36

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:

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

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

From

Ravi Roy

Date:

11 May 2014, 20:17:16

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:

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

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

From

Adrian Klaver

Date:

11 May 2014, 21:10:09

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

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

From

David G Johnston

Date:

11 May 2014, 23:24:09

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.

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

From

Gavin Flower

Date:

11 May 2014, 23:27:47

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:

my_role_id
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

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

From

David G Johnston

Date:

11 May 2014, 23:33:23

I
suggest that you move the password to a separate table (my_role_password) with 2 columns:
my_role_id
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: Re: Creating a role with read only privileges but user is allowed to change password
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

From

Gavin Flower

Date:

12 May 2014, 00:56:49

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:
my_role_id
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!