Fastest Way to Get Current User’s OID in PostgreSQL

postgresqlpostgresql-9.3users

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?

Best Answer

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 ...

Related Solutions

PostgreSQL – How to Select a Dynamic List of Columns

Another way, similar to what I proposed to your previous question: Return a set of well known type. Since your column list is dynamic, create a temporary table for the purpose. This announces the type to the system. As a side-effect you get a temp table to keep results for the duration of the session - like you needed in your last question.

CREATE OR REPLACE FUNCTION select_prices(_tbl anyelement, _cols text)
 RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT ' || colList || '
 FROM   prices
 WHERE  ...
 ORDER  BY ...';

END
$func$ LANGUAGE plpgsql;

Call:

CREATE TEMP TABLE t (col1, int, col2, date);
SELECT * FROM select_prices(NULL::t, 'col1, col2')

Or, to keep results in the temp table:

INSERT INTO t
SELECT * FROM select_prices(NULL::t, 'col1, col2')

If you need multiple tables in the same sessions, employ a sequence to get unique names. Related answer on SO:
Create a temporary table from a selection or insert if table already exist

However, this method (just like the other two in your question) are susceptible to SQL injection. You need to make sure it can't be abused.
SQL injection in Postgres functions vs prepared queries

Again, I would try to use this simple statement instead:

CREATE TEMP TABLE t AS
SELECT col1, col2 FROM prices;

PostgreSQL WHERE Clause – Prioritizing Conditions with OR

You have two conditions to check during the authentication:

There is a user with matching username; do not need to match by email.
There are no users with matching usernames; need to match by email.

So the query is:

WITH matching_username AS (SELECT * FROM member WHERE username = 'john@domain.com')
SELECT * FROM matching_username WHERE (SELECT COUNT(*) FROM matching_username) = 1
                                   AND pw_hash = 'x1j34'
UNION 
SELECT * FROM member            WHERE (SELECT COUNT(*) FROM matching_username) = 0
                                   AND email = 'john@domain.com' 
                                   AND pw_hash='x1j34';

On the other hand, there is a problem with your design of the table.

What if two users have have the same email, but neither of them have email as their username? If one of them tries to authenticate with an email, you will not be able to tell, which user record to authenticate against.

Best Answer

Related Solutions

PostgreSQL – How to Select a Dynamic List of Columns

PostgreSQL WHERE Clause – Prioritizing Conditions with OR

Related Question