Обсуждение: [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