Local Users "su'ing"
Hi,
I'm running postgresql 7.0.2 under FreeBSD 4.1-STABLE. If a user runs
pgsql from the command line and then types \c - <user> they can connect to
the database with the priveleges of <user>. No password is required,
presumably because of the line in pg_hba.conf:
local all trust
Great fun for someone who su's to pgsql...
A couple of questions...
1) This seems to be an odd default behaviour. Should it be documented
fairly clearly somewhere (perhaps it is but I missed it) or should the
default pg_hba.conf require passwords?
2) Is it possible to not require passwords if the local user connects to
postgres as a postgres user of the same name but require a password in all
other circumstances?
3) Does the search engine on the web site usually work? It keeps telling
me its stalled when I try to search the list archives. The same thing
happened a while ago but I thought it was just a temporary thing.
Thanks,
Andrew
Hi all,
Can I use ODBC driver for windows version 6.5 to connect to postgres 7.0.2?
Thanks
As has been talked about recently, I have a int8 field, which when searched for
as 'value' will use the index, but as value, with the quotes will not.
I subquery to get a list of the values to look up:
SELECT [fields]
FROM table1
WHERE id in (
select distinct id from namelookup where name = '$tname'
)
When I do an explain on this, it is doing a table scan, and not using the index
table1. It will only use the index when the value to be matched is in quotes.
How can I force it to use the index, or create a new index which would be used.
This causes a HUGE performance hit. (30-45 seconds, as opposed to less than 1
second.)
Any help would be greatly appreciated!
-Pete
From: pdaly <petedaly@ix.netcom.com>
As has been talked about recently, I have a int8 field, which when
searched for
as 'value' will use the index, but as value, with the quotes will not.
I subquery to get a list of the values to look up:
SELECT [fields]
FROM table1
WHERE id in (
select distinct id from namelookup where name = '$tname'
)When I do an explain on this, it is doing a table scan, and not using the
index
table1. It will only use the index when the value to be matched is in
quotes.
How can I force it to use the index, or create a new index which would be
used.
This causes a HUGE performance hit. (30-45 seconds, as opposed to less
than 1
second.)
Unfortunately, PostgreSQL doesn't use indexes with IN clauses.
Its on the list of things TODO though. At the moment, you have
to write the query using EXISTS instead:
SELECT table1.[fields] FROM table1
WHERE EXISTS
(SELECT namelookup.id FROM namelookup WHERE
namelookup.id = table1.id AND name = '$tname');
Hope that helps,
Mike Mascari
Import Notes
Resolved by subject fallback
pdaly <petedaly@ix.netcom.com> writes:
As has been talked about recently, I have a int8 field, which when
searched for as 'value' will use the index, but as value, with the
quotes will not.
Right now, you have exactly two choices: quote the literal, or
explicitly cast it to int8. Thus
select distinct id from namelookup where name = '$tname'
select distinct id from namelookup where name = $tname::int8
select distinct id from namelookup where name = int8($tname)
will work; anything else will not use the index.
The problem is basically that an unadorned integer-looking literal
will be taken to be int4 not int8, so the parser ends up considering
the '=' operator to be int8-vs-int4 equality, which is not an operator
that can be used with an int8 index. You can find past discussions
of this issue in the archives, though I think previous complainers have
been more interested in int2 indexes which have a similar problem.
Fixing this without making the system do the wrong thing in other cases
turns out to be trickier than you might think. It's on the todo list
but isn't likely to be fixed for 7.1 ...
regards, tom lane
