#24 PostgreSQL incorrect query for tables list

[Sergey Otinov]

libdbi-drivers-0.8.3

Function dbd_list_tables uses incorrect query to seach for tables. To retrieve relation owner OID it uses following subquery 'SELECT datdba FROM pg_database WHERE datname = ?' which is return only one (DBA) user however database may containt hudreds of users.

One way to solve ther problem use following subquery. It returns OID of the currently connected user
'SELECT usesysid FROM pg_user WHERE usename = user'
where 'user' built-in function wich returns currently connected user.

Another option use information schema for searching tables
ex. select * from information_schema.tables where table_name like <tabpattern>

[Moshe Katz]

I know this is a very old bug, with no activity, but there are now many cases in which extended permission assignment means that the "owner" is entirely useless for determining whether tables exist.

For example, if a database is owned by one role user_a but another role user_b is also given permission to create tables in that database, then tables created by user_b do not show up in the table list when user_a does a query because they are not owned by the database user.

Returning the OID of the currently connected user (as suggested in the original report) is also not going to work in this case, as the currently connected user won't see the tables created by the other user even if the connected user has been granted access to those tables.

I believe that using information_schema.tables is the correct way to do this query, as the documentation says:

Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege).

This means that it will handle ownership internally without having to check it like the query is doing currently.