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