'How can I GRANT to a specific DATABASEX.SCHEMAY in PostgreSQL?

I want to create a new user that only has access to database airflow_mlo_metadata and the schema xxxx under it.

It seems that ON SCHEMA databasename.schemaname is invalid, I just get ERROR: syntax error at or near "."

CREATE ROLE airflow_mlo_rw;

GRANT CONNECT ON DATABASE airflow_mlo_metadata TO airflow_mlo_rw;
GRANT USAGE ON SCHEMA airflow_mlo_metadata.xxxx TO airflow_mlo_rw; 
...

So how can I specify that I mean schema xxxx in database airflow_mlo_metadata and not schema xxxx?

Am I forced to disconnect and connect again to airflow_mlo_metadata instead? That's what I've done so far.

# \c airflow_mlo_metadata
GRANT USAGE ON SCHEMA xxxx TO airflow_mlo_rw;

But is there a way to do it without reconnecting?

postgresql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions