August 29th, 2013, 10:49 AM
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?
August 29th, 2013, 11:02 AM
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.
August 29th, 2013, 11:17 AM
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.
August 29th, 2013, 11:39 AM
Ok, it's not a permissions issue, apparently here's the problem (from the docs):
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:
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.