How do I get session information in postgres?

[chintuku]

I am trying to implement an auth system similar to supabase-auth, using postgres and RLS for the user database. I am creating users that map to 2 postgres roles (admin and regular), and I want to create some sort of policy to be able to let users only view their information. As such, I need to be able to tell who is logged in, but I don't want to create individual postgres users for each app user.

I noticed that supabase provides a auth.uid() sql function, that returns the user's id and can be used like this

create policy "Individuals can view their own todos."
on todos for select
using ( (select auth.uid()) = user_id );
How would I go about implementing something like this? I don't know how to set session variables in postgres, or anything similar to that.

For reference, this is how supabase does it.

Thanks in advance!

[EstebanBustamante]

Hi,

This is an Adobe forum. Could you please rephrase your question so we can understand which Adobe tool you are trying to use for this? If this is related solely to PostgreSQL, I would recommend posting your question in another community, such as: postgresteam.slack.com or stackoverflow

Hope this helps.

[martinph]

To retrieve session information in PostgreSQL, query the pg_stat_activity system view. xfinity billing issue This view provides details such as the current query being executed (current_query), client address (client_addr), application name (application_name), and more for each active session. Use SQL commands like SELECT * FROM pg_stat_activity; to obtain comprehensive session data, aiding in monitoring and troubleshooting database activity and performance issues effectively.

[TinaChen12]

Hey there,

You can achieve a similar functionality by using PostgreSQL's capabilities to set and retrieve user-defined variables. While PostgreSQL doesn't have built-in user session management, you can emulate this behavior with some tricks.

Firstly, you can create a function to set the user ID as a custom variable. For example:

CREATE OR REPLACE FUNCTION set_user_id(user_id uuid) RETURNS void AS $$
BEGIN
PERFORM set_config('my.auth_user_id', user_id::text, false);
END;
$$ LANGUAGE plpgsql;

Then, you can create a function to retrieve this variable:

CREATE OR REPLACE FUNCTION get_user_id() RETURNS uuid AS $$
DECLARE
user_id uuid;
BEGIN
SELECT current_setting('my.auth_user_id')::uuid INTO user_id;
RETURN user_id;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

This way, you can set the user ID for the current session by calling the `set_user_id` function during login and then use the `get_user_id` function in your RLS policy. For example:

CREATE POLICY "Individuals can view their own todos."
ON todos FOR SELECT
USING ((get_user_id()) = user_id);

During user login, you just need to call the `set_user_id` function to set the user's ID. For instance:

SELECT set_user_id('your-user-id-here');