7

I am thinking of storing the user's OID in the "owner" column of a table, so I don't have to do anything if they change their username.

I understand one can use pg_has_role() with OIDs, so that's good.

Is there a fast and easy way to get the current user's OID without just matching it against one of the system tables?

10

The fastest way I know of is a lookup in the system catalog view pg_roles:

SELECT * FROM pg_roles WHERE rolname = 'postgres';

Strictly speaking, it would be even slightly faster to use the underlying table pg_authid, but access to it is restricted to superusers for good reasons.

There is no object identifier type like for tables or types, which would allow a simple cast like 'mytable'::regclass.

Be aware though, that OIDs are not stable across a dump / restore cycle. So, the OID is no good for the use case!

In some databases I have a separate login table with a serial primary key that I use for similar purposes. Maintained manually. And functions using it are prepared to occasionally not find a user in this table. A very basic and fast table:

CREATE TABLE users.login (
  login_id serial PRIMARY KEY 
 ,username text NOT NULL UNIQUE
);

When creating new users I use a plpgsql function that creates the new user in the system and enters it into my table at the same time. And I use this login_id in many places. For instance I keep track of who made the last change to a row in most tables. I use this simple function:

CREATE OR REPLACE FUNCTION public.f_login_id()
  RETURNS int AS
$func$
SELECT COALESCE((SELECT login_id FROM users.login
                 WHERE  username = session_user::text), 0::int)
$func$ LANGUAGE sql STABLE;

No foreign key constraints so to keep things fast and simple. Obviously, I don't need strict referential integrity ...

  • Darn it. Thanks anyways. Can you describe your login table / system a little? – Neil McGuigan Jan 9 '14 at 23:27
  • 1
    @NeilMcGuigan: I added the basic layout of my very simple design. – Erwin Brandstetter Jan 9 '14 at 23:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.