patternsqlMinor
What permissions are required to return rows from information_schema.schemata?
Viewed 0 times
rowspermissionswhatarereturninformation_schemaschematafromrequired
Problem
In postgres (I'm on 9.3.4) what permissions are required to return rows for
The 9.3 documentation says "are owned by a currently enabled role". Does that mean the user calling
Later in 9.5 documentation it says schemata that "the current user has access to (by way of being the owner or having some privilege)".
The account currently trying to run the query and getting 0 rows back has had
What are the privileges that allow a database account to see a schema in the information_schema.schemata` view?
select * from information_schema.schemataThe 9.3 documentation says "are owned by a currently enabled role". Does that mean the user calling
schemata must own (or be part of a role that owns) the various schemata?Later in 9.5 documentation it says schemata that "the current user has access to (by way of being the owner or having some privilege)".
The account currently trying to run the query and getting 0 rows back has had
grant usage on schema applied to it.What are the privileges that allow a database account to see a schema in the information_schema.schemata` view?
Solution
The user needs to be the owner of a schema to get any rows from
Or the user needs to be a member of a group that owns the schema
Usage grant is not sufficient. The user does not need to be a superuser. The user can own a table in a schema, but that is not sufficient either.
information_schema.schemataalter schema $schemaName owner to $user;Or the user needs to be a member of a group that owns the schema
Usage grant is not sufficient. The user does not need to be a superuser. The user can own a table in a schema, but that is not sufficient either.
Code Snippets
alter schema $schemaName owner to $user;Context
StackExchange Database Administrators Q#138116, answer score: 2
Revisions (0)
No revisions yet.