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.
How do I get sequelize/apollo to add set role role_write;
for each new connection to the database?
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.