agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedsimple "select / if found" isn't
6+ messages / 3 participants
[nested] [flat]
* simple "select / if found" isn't
@ 2016-12-16 11:02 Gary Stainburn <gary.stainburn@ringways.co.uk>
2016-12-16 11:12 ` Re: simple "select / if found" isn't Karsten Hilbert <Karsten.Hilbert@gmx.net>
2016-12-16 11:34 ` Re: simple "select / if found" isn't Jan Otto <asche@me.com>
0 siblings, 2 replies; 6+ messages in thread
From: Gary Stainburn @ 2016-12-16 11:02 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: simple "select / if found" isn't
2016-12-16 11:02 simple "select / if found" isn't Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2016-12-16 11:12 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2016-12-16 11:24 ` Re: simple "select / if found" isn't Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 6+ messages in thread
From: Karsten Hilbert @ 2016-12-16 11:12 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: simple "select / if found" isn't
2016-12-16 11:02 simple "select / if found" isn't Gary Stainburn <gary.stainburn@ringways.co.uk>
2016-12-16 11:12 ` Re: simple "select / if found" isn't Karsten Hilbert <Karsten.Hilbert@gmx.net>
@ 2016-12-16 11:24 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2016-12-16 11:29 ` Re: simple "select / if found" isn't Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Gary Stainburn @ 2016-12-16 11:24 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: simple "select / if found" isn't
2016-12-16 11:02 simple "select / if found" isn't Gary Stainburn <gary.stainburn@ringways.co.uk>
2016-12-16 11:12 ` Re: simple "select / if found" isn't Karsten Hilbert <Karsten.Hilbert@gmx.net>
2016-12-16 11:24 ` Re: simple "select / if found" isn't Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2016-12-16 11:29 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 6+ messages in thread
From: Karsten Hilbert @ 2016-12-16 11:29 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: simple "select / if found" isn't
2016-12-16 11:02 simple "select / if found" isn't Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2016-12-16 11:34 ` Jan Otto <asche@me.com>
2016-12-16 11:41 ` Re: simple "select / if found" isn't Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 6+ messages in thread
From: Jan Otto @ 2016-12-16 11:34 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
hi gary,
> On 16 Dec 2016, at 12:02, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
>
> 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);
return exists(select 1 from user_previous_passwords
where u_id=ID and crypt(PASS,u_previous_password) = u_previous_password);
> END;
> $$ LANGUAGE plpgsql;
regards, jan
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: simple "select / if found" isn't
2016-12-16 11:02 simple "select / if found" isn't Gary Stainburn <gary.stainburn@ringways.co.uk>
2016-12-16 11:34 ` Re: simple "select / if found" isn't Jan Otto <asche@me.com>
@ 2016-12-16 11:41 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Gary Stainburn @ 2016-12-16 11:41 UTC (permalink / raw)
To: pgsql-sql
On Friday 16 December 2016 11:34:48 Jan Otto wrote:
>
> return exists(select 1 from user_previous_passwords
> where u_id=ID and crypt(PASS,u_previous_password) =
> u_previous_password);
>
> > END;
> > $$ LANGUAGE plpgsql;
>
> regards, jan
What a muppet am I???????
It had to be something that simple, but I was going code blind. The ironic
bit was that the where clause was the only thing that remained the same with
ever different idea I tried.
Thanks Jan
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2016-12-16 11:41 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-12-16 11:02 simple "select / if found" isn't Gary Stainburn <gary.stainburn@ringways.co.uk>
2016-12-16 11:12 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2016-12-16 11:24 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2016-12-16 11:29 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2016-12-16 11:34 ` Jan Otto <asche@me.com>
2016-12-16 11:41 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox