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.
Am I correct in my belief that postgres users lack persistent identifiers?
I believe that some other similar systems implement their own users
and privileges systems to achieve this, but I hesitate to do that. I
also hesitate to assume that the DB user name will never change, and
go ahead and use session_user() in lieu of a real persistent
identifier.
Regards,
Peter Geoghegan