0

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.

1
  • Can we define more closely, that you want a list of all views with the SELECT privilege for public (except system views)? Commented Sep 15, 2014 at 3:33

4 Answers 4

1

The information about views is stored in information_schema.tables:

SELECT *
FROM Information_Schema.Tables
WHERE table_type = 'VIEW';
Sign up to request clarification or add additional context in comments.

4 Comments

but is it all views or only those that all users can write queries to them?
@user4039853 . . . The information about views is stored in information_schema.tables. I'm not sure what your question is, but system views are in there too.
I need only those views that all users has access to them
The information schema consists of views exclusively, based on tables in the system catalogs. They obviously do not "store" information. More importantly, the presented query is inferior to what the question already presented and just as wrong - without crucial additional information (even after @Kombajn's edit). I added another answer.
0

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.

Comments

0
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

Comments

0

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:

1 Comment

what about this > select * from INFORMATION_SCHEMA.views WHERE table_schema = ANY (current_schemas(false))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.