HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

What permissions are required to return rows from information_schema.schemata?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
rowspermissionswhatarereturninformation_schemaschematafromrequired

Problem

In postgres (I'm on 9.3.4) what permissions are required to return rows for

select * from information_schema.schemata


The 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 information_schema.schemata

alter 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.