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

On Sun, Mar 21, 2004 at 02:21:51PM +0200, Mihai Tanasescu wrote:
> 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.

Note how the tables in the first \dt are in schema bbstatus which by
default only user bbstatus will see. \dt *.* should list all tables
IIRC.

Did you intend for those tables to be in their own schema?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> If the Catholic church can survive the printing press, science fiction
> will certainly weather the advent of bookwarez.
>    http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow

On Sun, Mar 21, 2004 at 14:21:51 +0200,
  Mihai Tanasescu <Mihai.Tanasescu@skyraven.pcnet.ro> wrote:
>
> 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)

Not necessarily. The username of the user that does the initdb is the
postgres superuser by default. You can use the -U option if you
want the username to be something else.