Thread: 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

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

> Can I use ODBC driver for windows version 6.5 to connect to postgres
7.0.2?

Yes, you must (there is no ODBC driver version > 6.5 for win, I think)