postgres function help

Hello! Im trying to do something like this;

DO $$
DECLARE
id INTEGER;
BEGIN
-- Set the role to 'postgres'
SET ROLE postgres;

-- Your SQL statements go here
SELECT tenant_id INTO id FROM users WHERE id = auth.uid();

-- Reset the role to the original user
RESET ROLE;

-- Return the result
RETURN id;
END $$;

but it keep returning syntax error, what should I do?

JotaOP•1/16/24, 7:24 PM

changed to only;

-- Set the role to 'postgres'
SET ROLE postgres;

-- Your SQL statements go here
SELECT tenant_id INTO id FROM users WHERE id = auth.uid();

-- Reset the role to the original user
RESET ROLE;

-- Return the result
return id;

and stills returning;
Failed to create function: syntax error at or near "return"

JotaOP•1/16/24, 7:37 PM

okay, now i figured out

JotaOP•1/16/24, 7:37 PM

-- Create the function
CREATE OR REPLACE FUNCTION your_function_name()
RETURNS integer -- Change the data type according to your needs
AS $$
DECLARE
id integer; -- Assuming 'id' is of type integer
BEGIN
-- Set the role to 'postgres'
SET ROLE postgres;

-- Your SQL statements go here
SELECT tenant_id INTO id FROM users WHERE id = auth.uid();

-- Reset the role to the original user
RESET ROLE;

-- Return the result
RETURN id;
END;
$$ LANGUAGE plpgsql;

JotaOP•1/16/24, 7:37 PM

this worksworks

JotaOP•1/16/24, 7:37 PM

buuut, in a logic way, postgres did not allow to change the role to postgres

garyaustin•1/16/24, 7:39 PM

Normally if you want to be user postgres in a function you just declare it security definersecurity definer type.

JotaOP•1/16/24, 7:40 PM

hmm let me see what it is

JotaOP•1/16/24, 7:44 PM

-- Create the function
CREATE OR REPLACE FUNCTION public.get_current_tenant_id()
RETURNS integer -- Change the data type according to your needs
SECURITY DEFINER
AS $$
DECLARE
id integer; -- Assuming 'id' is of type integer
BEGIN
-- Set the role to 'postgres'
SET ROLE postgres;

-- Your SQL statements go here
SELECT tenant_id INTO id FROM users WHERE id = auth.uid();

-- Reset the role to the original user
RESET ROLE;

-- Return the result
RETURN id;
END;
$$ LANGUAGE plpgsql;

JotaOP•1/16/24, 7:44 PM

just like this?

JotaOP•1/16/24, 7:46 PM

hmm naah

vick•1/16/24, 7:46 PM

yes, then delete the SET/RESET role bits. If you're not already a user with role postgres permissions, you cannot "upgrade" yourself to it.

JotaOP•1/16/24, 7:46 PM

{
"code": "42501",
"details": null,
"hint": null,
"message": "cannot set parameter "role" within security-definer function"
}

Vvick yes, then delete the SET/RESET role bits. If you're not already a user with role...

JotaOP•1/16/24, 7:46 PM

cool, i will try it so

vick•1/16/24, 7:46 PM

but why do you need a function to do this?

JotaOP•1/16/24, 7:47 PM

so the police of "users" should ignore the user if his role is postgres

JotaOP•1/16/24, 7:53 PM

so far working

JotaOP•1/16/24, 7:53 PM

thanks guys

postgres function help

Similar Threads

Similar Threads

Similar Threads