Обсуждение: Simple list tables question

Hello,

I've been a mysql user for about 2 years and I decided to try Postgre
SQL out.

I have the following problem that I'm trying to figure out:

- in mysql as user root I can connect to any databases not owned by
myself and I can list the tables they contain
- in postgre I have created another database owned by user "bbstatus"
    If I login with user bbstatus to database bbstatus then I can do a
"\dt" and show the tables it contains but if I login with user postgres
which is a superuser and I try a "\dt" I get no results.

/usr/local/pgsql/bin/psql -U bbstatus bbstatus
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

bbstatus=> \dt
                      List of relations
  Schema  |              Name              | Type  |  Owner
----------+--------------------------------+-------+----------
bbstatus | acct_clientip                  | table | bbstatus
bbstatus | acct_clients_and_filters       | table | bbstatus



/usr/local/pgsql/bin/psql -U postgres bbstatus
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

bbstatus=# \dt
No relations found.




Isn't the postgres user the same as the root user from mysql ?
I mean there must be a user that has enough rights by default to list
tables in any databases ( I don't like granting myself right for every
database someone creates)

Mihai Tanasescu <Mihai.Tanasescu@skyraven.pcnet.ro> writes:
>     If I login with user bbstatus to database bbstatus then I can do a
> "\dt" and show the tables it contains but if I login with user postgres
> which is a superuser and I try a "\dt" I get no results.

It's got nothing to do with permissions --- it has to do with your
search path.  Schema bbstatus isn't in postgres' default search path
and so by default \dt doesn't show its contents.

You could see 'em with \dt *.* or \dt bbstatus.*, or you could set
search_path to include bbstatus.

            regards, tom lane

Thanks,
that worked...
I'm now looking through the docs to see how I can change the search path.




Tom Lane wrote:

>Mihai Tanasescu <Mihai.Tanasescu@skyraven.pcnet.ro> writes:
>
>
>>    If I login with user bbstatus to database bbstatus then I can do a
>>"\dt" and show the tables it contains but if I login with user postgres
>>which is a superuser and I try a "\dt" I get no results.
>>
>>
>
>It's got nothing to do with permissions --- it has to do with your
>search path.  Schema bbstatus isn't in postgres' default search path
>and so by default \dt doesn't show its contents.
>
>You could see 'em with \dt *.* or \dt bbstatus.*, or you could set
>search_path to include bbstatus.
>
>            regards, tom lane
>
>

Thanks.

My mistake..I thought there was a path variable in postgre ...I know how
to set the bash one.


ghaverla@freenet.edmonton.ab.ca wrote:

>On Sun, 21 Mar 2004, Mihai Tanasescu wrote:
>
>
>
>>Thanks,
>>that worked...
>>I'm now looking through the docs to see how I can change the search path.
>>
>>
>
>I don't know if it is in the PostgrSQL docs or not.  I believe Tom
>was talking about the PATH variable which is used in the program
>environment.  For example, to set the PATH in bash
> export PATH=/usr/bin:/bin:/usr/local/bin
>would be an example of setting the PATH.  Csh does it slightly
>differently.
>
>Gord
>
>
>

Mihai Tanasescu <Mihai.Tanasescu@skyraven.pcnet.ro> writes:
> My mistake..I thought there was a path variable in postgre ...I know how
> to set the bash one.

No, I was talking about "SET search_path".  See the SET command and the
list of run-time configuration variables.

            regards, tom lane