From: | Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> |
---|---|
To: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Re: Creating a role with read only privileges but user is allowed to change password |
Date: | 2014-05-11 21:56:34 |
Message-ID: | 536FF212.6060502@archidevsys.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Rafał Pietrak | 2014-05-11 22:05:15 | Re: Re: Partitioning such that key field of inherited tables no longer retains any selectivity |
Previous Message | David G Johnston | 2014-05-11 20:33:18 | Re: Creating a role with read only privileges but user is allowed to change password |