\dt doesn't show all relations in user's schemas (8.4.2)

From: Ralph Graulich <ralph(dot)graulich(at)t-online(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: \dt doesn't show all relations in user's schemas (8.4.2)
Date: 2009-12-19 20:43:17
Message-ID: B1DF65BF-26A1-460A-A0B7-ED4D59E07F24@t-online.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I am running PostgreSQL 8.4.2. For the testcase I have a database, a
user, and two schemas within the database. Two tables in those two
different schemas have the same name, but only on of those tables
shows up using the "\dt" command.

How-To-Repeat:

-- psql template1

CREATE ROLE testrole
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
NOINHERIT
LOGIN
ENCRYPTED PASSWORD 'testrole'
;

CREATE DATABASE test
OWNER testrole
TEMPLATE template0
LC_COLLATE = 'de_DE.UTF-8'
LC_CTYPE = 'de_DE.UTF-8'
ENCODING 'UTF-8'
;

ALTER ROLE testrole SET search_path=schema1, schema2;

-- psql test testrole

show search_path;
-- search_path
-- ------------------
-- schema1, schema2
-- (1 row)

CREATE SCHEMA schema1;
CREATE SCHEMA schema2;

CREATE TABLE schema1.table1
(
field1 VARCHAR(10)
);

CREATE TABLE schema2.table1
(
field1 VARCHAR(10)
);

\dt

-- List of relations
-- Schema | Name | Type | Owner
-- ---------+--------+-------+----------
-- schema1 | table1 | table | testrole
-- (1 row)

-- Only one of the two relations is shown

CREATE TABLE schema2.table2
(
field1 VARCHAR(10)
);

\dt

-- List of relations
-- Schema | Name | Type | Owner
-- ---------+--------+-------+----------
-- schema1 | table1 | table | testrole
-- schema2 | table2 | table | testrole
-- (2 rows)

I think both table1 in schema1 and schema2 should show up in the \dt
listing, because the user has access to both tables, is the owner of
both tables and has the search_path set accordingly.
If I delete the "AND pg_catalog.pg_table_is_visible(c.oid)" from the
where clause of the SQL statement that is issued when using "\dt"
command, all the tables show up as expected (but I am unaware of the
possible side effects...):

Schema | Name | Type | Owner
---------+--------+-------+----------
schema1 | table1 | table | testrole
schema2 | table1 | table | testrole
schema2 | table2 | table | testrole
(3 rows)

Does it work as expected? Where is that behaviour explained? Is there
a command to show all the relations (/objects) a user has access to?

Best regards,
Ralph

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2009-12-19 20:44:09 Re: Triggers made with plpythonu performance issue
Previous Message Garry Saddington 2009-12-19 20:36:38 modelling question