'Getting 'permission denied for table foo' error even though permissions have been granted

I have logged into my database as the database owner (a superuser as well) and have created a role_write role with nologin and noinherit flags.

I've revoked the default create permissions from the public schema with:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

And revoked public access to the database with:

REVOKE ALL ON DATABASE mydb FROM PUBLIC;

I have granted roles on each table I want the role to be able to have read/write access. Using the user table for this example, I have added:

grant insert, select, update, delete on users to role_write;

Then I've also given the user proper permissions for the tables, schema, sequences and database connection and altered the default privileges with:

GRANT CONNECT ON DATABASE my to role_write;
grant usage on schema public to role_write;
grant select, insert, update, delete on all tables in schema public to role_write;
grant usage, select on all sequences in schema public to role_write;
alter default privileges in schema public
   grant select, insert, update, delete on tables to role_write;
alter default privileges in schema public
   grant usage, select on sequences to role_write;

I have also created a new user (with noinherit flag) and have added the role_write role to this user with:

grant role_write to newuser;

Then I signed in as that user as ran:

set role role_write;

Then just to be safe, I restarted postgres.

Yet even after all that, when I try to connect to the database through sequelize/apollo server as newuser and run a query, I get the error:

permission denied for table users

I have no idea what I've done wrong. If I check the privileges of the user table it shows that role_write is correctly setup.

Any help?

EDIT: So I've realized that I need to run set role role_write on every session, not just once. If I connect to the database and run a query straight away then I get the error, however if I first set the role then it allows me.

So my question now is...

How do I get sequelize/apollo to add set role role_write; for each new connection to the database?

postgresqldatabase-permissions


Solution 1:[1]

DEAR PEOPLE FROM THE FUTURE: Here's what we've figured out so far ...

As explained in my edit, the issue is that I created the newuser with the noinherit flag. This causes an issue when using sequelize/apollo as the only way to get the read/write permissions from the role_write role is by first running set role role_write;.

Maybe someone will answer with a solution that lets you send set role role_write; with each sequelize/apollo request to the server, but for now I solved the issue by simply running alter role newuser with inherit;. Now I don't get the error message anymore.

Solution 2:[2]

All you need to do is

ALTER ROLE newuser INHERIT;

Then newuser will inherit the privileges from rolw_write, and there is no need to run SET ROLE.

Sources

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

Source: Stack Overflow

Solution Source
Solution 1 Jake
Solution 2 Paul Whelan

Related Questions