Re: Persistent identifiers for Postgres users

Peter Geoghegan escribió:
> Hello,
>
> I maintain an app where database users correspond to actual users,
> with privileges granted or denied to each. At the moment, records that
> each user creates are identified as such by a text column that has a
> default value of session_user(). I don't need to tell you that this is
> suboptimal, because db users (as far as I'm aware) lack persistent
> identifiers - names may change, users may be dropped, etc. Also, there
> is no way that I am aware of to fake row level privileges by adding a
> ...AND id NOT IN (SELECT forbidden_department FROM user_priveleges
> WHERE user_id = current_user_id() ) to relevant queries . Actually,
> that approach is probably preferable to actual row level privileges,
> as it allows me to deny access based on a domain-level concept,
> departments.

You could use OIDs as identifiers for roles instead of names, but of
course you don't have any way to know that one of them is dropped.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.