CODEWITHSUNDEEP
sqlpostgresqlview
user4039853
user4039853

Reputation: 1

PostgreSQL Views Query

I am new in writing queries. I need a list of views that every user can write queries to.

SELECT *
FROM Information_Schema.Views;

I tried that, but I guess it is a list of all views, but I need only those views that all users could write queries to.

Upvotes: 0

Views: 297

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

If you are going to use the information schema, you need to be aware how it works. Per documentation:

The view views contains all views defined in the current database. Only those views are shown that the current user has access to (by way of being the owner or having some privilege).

Bold emphasis mine. This also provides a handy way of solving your problem. Run your query with a plain, new, non-superuser role that that wasn't granted membership in any other roles nor any direct privileges on any views (yet).
If you want to exclude system views add the WHERE clause:

SELECT *
FROM   information_schema.views
WHERE  table_schema NOT LIKE ALL ('{pg_%,information_schema}'::text[]);

You get only those views (and all of them), that public can access.

SQL Fiddle.

(Yes, use the query on information_schema.views you already had, no point in using information_schema.tables, like has been suggested.)

For more specific needs, I suggest you use the system catalogs instead. Actual privileges are stored in the system table pg_class in the column relacl. And the view pg_views lists all views, not just the ones the current role has privileges for.

Resolving actual privileges is not trivial. Use the dedicated "Access Privilege Inquiry Functions" like has_table_privilege() that @user17130 already suggested. Related answer:

Upvotes: 0

Vivek S.
Vivek S.

Reputation: 21895

select * from pg_catalog.pg_views
where schemaname NOT IN ('pg_catalog', 'information_schema')
order by schemaname, viewname;
  • will show you the viewname and its owner

Upvotes: 0

user17130
user17130

Reputation: 261

This will list each each users visible views (without the superusers.) 

SELECT
    usename, schemaname||'.'|| viewname as view
FROM
    pg_views
    ,pg_user
WHERE
    has_table_privilege (
        pg_user.usename,
        schemaname||'.'|| viewname,
        'select'
     )
AND
    schemaname NOT IN (
        'pg_catalog',
        'information_schema'
     )
AND usesuper=false

As far as listing a view that is visible to all users ... Postgres has fine grained permissions so there is no easy flag to search for. You would have to match that each view would produce a row of every user. But you could create a group for your users and then query for that.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

The information about views is stored in information_schema.tables:

SELECT *
FROM Information_Schema.Tables
WHERE table_type = 'VIEW';

Upvotes: 1

Related Questions