Persistent identifiers for Postgres users

From: Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com>
To: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Persistent identifiers for Postgres users
Date: 2010-02-17 11:21:08
Message-ID: db471ace1002170321q350b64ddvf5b58d3398fd0619@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dimitri Fontaine 2010-02-17 13:15:48 Re: Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion
Previous Message Devrim GÜNDÜZ 2010-02-17 10:16:57 Re: Source RPMs for PostgreSQL 7.4.27 on RHEL4