7

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?

1

1 Answer 1

2

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.