Обсуждение: [SQL] simple "select / if found" isn't
I'm creating a simple function that must have been done millions of times before, but I can't get it to work. In this case, I'm checking a user ID and password against previously used passwords: All I want to do is return 'found' based on the select but I can't get it to work. If I run select 1 from user_previous_passwords where u_id=25 and crypt('MyPaSSword',u_previous_password) = u_previous_password; then it returns the matching row(s) If I run my function create or replace function check_previous_passwords (ID int4, PASS varchar) returns boolean as $$ DECLARE UID int4; BEGIN return exists(select 1 from user_previous_passwords where u_id=ID and crypt(PASS,u_previous_password) = PASS); END; $$ LANGUAGE plpgsql; I always get false; I've tried things like if exist(....) then .... select 1 into UID select count(u_id) into UID update .....set u_id=u_id ...... if found then but I never get the correct result, so I think I must me doing something much more fundamentally wrong. Can someone spot it please? Gary
On Fri, Dec 16, 2016 at 11:02:37AM +0000, Gary Stainburn wrote: > All I want to do is return 'found' based on the select but I can't get it to > work. > > If I run > > select 1 from user_previous_passwords > where u_id=25 and > crypt('MyPaSSword',u_previous_password) = u_previous_password; > > then it returns the matching row(s) > > If I run my function > > create or replace function check_previous_passwords (ID int4, PASS varchar) > returns boolean as $$ It just _might_ have to do with permissions to user_previous_passwords. If the function somehow got installed as "security definer" and definer does not have RLS-based (!) permissions on user_previous_passwords then it won't find rows. A shot in the dark... Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Friday 16 December 2016 11:12:13 Karsten Hilbert wrote: > If the function somehow got installed as "security definer" > and definer does not have RLS-based (!) permissions on > user_previous_passwords then it won't find rows. > > A shot in the dark... > > Karsten Thank you for the reply, but I'm not sure what you mean. To give more context (which I should have not straight off) I'm on an old box which is running PoshgreSQL 8.3 for both the server and client (psql). The select from table, create function, and select from function are all happening in the same psql sesson, logged in the owner of the database.
On Fri, Dec 16, 2016 at 11:24:41AM +0000, Gary Stainburn wrote: > > If the function somehow got installed as "security definer" > > and definer does not have RLS-based (!) permissions on > > user_previous_passwords then it won't find rows. > > To give more context (which I should have not straight off) > > I'm on an old box which is running PostgreSQL 8.3 for both the server That would preclude the RLS idea :-/ Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346