ERROR: permission denied for schema user1_gmail_com at character 46

Question

I need to restrict a user, access only on a particualr schema tables only.So I tried following query and login as user1_gmail_com. But I got following error when I try to browse any schema table.

My Query:

SELECT clone_schema('my_application_template_schema','user1_gmail_com');
CREATE USER user1_gmail_com WITH PASSWORD 'myloginpassword';
REVOKE  ALL ON ALL TABLES IN SCHEMA user1_gmail_com FROM PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA user1_gmail_com TO user1_gmail_com;

SQL error:

ERROR:  permission denied for schema user1_gmail_com at character 46
In statement:
SELECT COUNT(*) AS total FROM (SELECT * FROM "user1_gmail_com"."organisations_table") AS sub

Updated Working Query:

SELECT clone_schema('my_application_template_schema','user1_gmail_com');
CREATE USER user1_gmail_com WITH PASSWORD 'myloginpassword';
REVOKE  ALL ON ALL TABLES IN SCHEMA user1_gmail_com FROM PUBLIC;
GRANT USAGE ON SCHEMA user1_gmail_com TO user1_gmail_com;
GRANT SELECT ON ALL TABLES IN SCHEMA user1_gmail_com TO user1_gmail_com;

Answer 1

You need to grant access not only to the tables in the schema, but also to the schema itself.

From the manual:

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema.

So either make your created user the owner of the schema, or grant USAGE on the schema to this user.

Answer 2

This confused me. Still not sure I'm handling it correctly. Run \h grant for the syntax within psql. Here is how I managed to get my other users and groups to work as I needed:

GRANT ALL PRIVILEGES ON SCHEMA foo TO GROUP bar;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO GROUP bar;

Answer 3

I kept getting this error when using flyway to deploy database changes. I do some manual setup first, such as creating the database, so flyway wouldn't need those super-admin permissions.

My Fix

I had to ensure that the database user that flyway job used had ownership rights to the public schema, so that the flyway user could then assign the right to use the schema to other roles.

Additional setup Details

I am using AWS RDS (both regular and Aurora), and they don't allow super users in the databases. RDS reserves super users for use by AWS, only, so that consumers are unable to break the replication stuff that is built in. However, there's a catch-22 that you must be an owner in postgres to be able to modify it.

My solution was to create a role that acts as the owner ('owner role'), and then assign both my admin user and the flyway user to the owner role, and use ALTER scripts for each object to assign the object's owner to the owner role.

I missed the public schema, since that was auto-created when I created the database script manually. The public schema defaulted to my admin role rather than the shared owner role. So when the flyway user tried to assign public schema permissions to other roles, it didn't have the authority to do that. An error was not thrown during flyway execution, however.