Find primary key of table in Postgresql from information_schema with only SELECT

Question

I am using the following query to discover (1) the primary key columns and (2) if the columns have a default value from the information_schema in Postgresql 9.1.

SELECT kcu.column_name, (c.column_default is not null) AS has_default 
FROM information_schema.key_column_usage kcu 
JOIN information_schema.table_constraints tc ON tc.constraint_name = kcu.constraint_name 
JOIN information_schema.columns c on c.column_name = kcu.column_name and c.table_name = kcu.table_name  
WHERE tc.constraint_type = 'PRIMARY KEY' AND kcu.table_name like :tablename

It works fine when run as the database owner, but when I run it as a "read-only" user (which I need to do in my application), it returns no data. Some research revealed that the problem is the information.table_constraints view; from the documentation:

The view table_constraints contains all constraints belonging to tables that the current user owns or has some non-SELECT privilege on.

So in order to retrieve table_constraints, my login role needs more than SELECT on the table? Is there no way to get the information from information_schema without giving write permissions to the login role?

Answer 1

Use pg_* views instead of information_schema views.
pg_* views display all information regardles of granted privileges.

Try this query:

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name,
    d.adsrc   as default_value
from
    pg_class t
    join pg_attribute a on a.attrelid = t.oid
    join pg_index ix    on t.oid = ix.indrelid AND a.attnum = ANY(ix.indkey)
    join pg_class i     on i.oid = ix.indexrelid
    left join pg_attrdef d on d.adrelid = t.oid and d.adnum = a.attnum  
where
    t.relkind = 'r'
    and t.relname in ( 'aa', 'bb', 'cc' )
order by
    t.relname,
    i.relname,
    a.attnum;

An example of the query results:

create table aa(
  x int primary KEY
);

create table bb(
  x int default 1,
  constraint pk primary key ( x )
);

create table cc(
  x int default 20,
  y varchar(10) default 'something',
  constraint cc_pk primary key ( x, y )
);

 table_name | index_name | column_name |         default_value
------------+------------+-------------+--------------------------------
 aa         | aa_pkey    | x           |
 bb         | pk         | x           | 1
 cc         | cc_pk      | x           | 20
 cc         | cc_pk      | y           | 'something'::character varying