#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    144
    Rep Power
    4

    Grant select on information_schema breaks pw login?


    I have an application with a read/write login (which owns the database) and a read-only login (which has been granted SELECT on tables explicitly).

    The application needs to query views in information_schema for some information to perform various automatic processes. When the read/write users queries information_schema, the information is returned correctly. When the read-only user queries, no error is thrown but the result set is empty.

    I attempted to grant SELECT on the needed views to the read-only user, but after doing that the user cannot log in any more. Attempts to log in are met with "password authentication failure". I've tried resetting the password, but no matter what I get 'password authentication failure'.

    I also tried revoking the permissions I granted, but the user cannot log in.

    What I need to know is:

    -- What have I done to the read-only account, and how do I fix it?
    -- How can I make the information_schema available to the read_only user without granting them write access to the tables?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    144
    Rep Power
    4
    OK, Nevermind the failed login part; apparently I fat-fingered a dialog box in pgadmin3 and expired the account. That's what comes of using the GUI!

    I still, even with explicit grants, can't get results from information_schema with the read-only user. No error, just no results when there should be.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    144
    Rep Power
    4
    OK, more information:

    The part of the query that's failing is in trying to read the information_schema.table_constraints view (trying to automatically determine the primary key fields for a table). The read-only user gets no results when selecting from this view, even with an explicit SELECT permission granted.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    144
    Rep Power
    4
    Ok, it's not a permissions issue, apparently here's the problem (from the docs):

    The view table_constraints contains all constraints belonging to tables that the current user owns or has some non-SELECT privilege on.
    I guess I'll have to refactor my query using pg_catalog views?

    This is the query I currently use; I need the name of each primary key column and whether or not it has a default value:
    Code:
    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
    Last edited by admoore; August 29th, 2013 at 11:53 AM.

IMN logo majestic logo threadwatch logo seochat tools logo