Sidekiq worker insufficient Postgres privileges

Questions / Help
1

I have a Rails app that runs sidekiq as a worker, like so many.

I’m about to try multi process apps for the problem that I’m describing, but I just want to be sure I understand everything correctly (IMHO there are still upsides to running individual apps for worker processes, to make them individually horizontally scalable)

My setup looks like this

  • a web app
  • a worker app
  • a PG cluster

First I attached the web app, then the worker app to the DB cluster

flyctl pg attach -a foo --postgres-app=foo-pg
flyctl pg attach -a foo-worker --postgres-app=foo-pg --database-name=foo

The problem I have is that any job I run inside the foo-worker app reports a PG::InsufficientPrivilege: ERROR: permission denied for table users (ActiveRecord::StatementInvalid) error.

This makes sense, because apparently the second user has been created without any privileges:

foo=# SELECT grantee, privilege_type
FROM information_schema.role_table_grants WHERE table_name = 'users';
            grantee             | privilege_type
--------------------------------+----------------
 foo                            | INSERT
 foo                            | SELECT 
 foo                            | UPDATE
 foo                            | DELETE
 foo                            | TRUNCATE
 foo                            | REFERENCES
 foo                            | TRIGGER
(7 rows)

Note that no foo_worker user has any privileges (though it exists). If I run the above attach command with --database-user=foo it fails, because that user exists, and is expected to fail as per the docs.

My question above all: Is this expected behavior? Is the second attached DB user expected to have no privileges on a DB?

Of course I can flyctl pg connect in and grant the respective privileges, but shouldn’t that be provided by the tooling?

Thanks!

2

Hey @julianrubisch,

When a table is created it will receive an owner, which is typically the user who created the table.

\dt
              List of relations
 Schema | Name  | Type  |        Owner
--------+-------+-------+---------------------
 public | names | table | foo
 public | users | table | foo
(2 rows)

By default, no other standard users will be able to access the given table until permissions are explicitly granted.

There are a number of ways you could address this and it ultimately comes down to your requirements.
For example, if you want foo_worker to have access to everything within the public schema, you can run the following:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON tables TO foo_worker;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, USAGE ON sequences TO foo_worker;

If you would prefer to give foo_worker specific privileges to a single table, you could run something like:

GRANT SELECT, UPDATE ON TABLE users TO foo_worker;

Note: The above commands can be run by the table owner or superuser.

This all being said, permissions can get quite complicated. We will have to think a bit about how we want to address this in the future.

3

Thanks! Yes I figured I have these two options

  • grant privileges manually, like you suggested
  • copy the DATABASE_URL from the web app, like on Heroku e.g.

Now, the provisioning of sidecar apps like this isn’t such an edge use case - at least that’s what I thought.

I had hoped that I could just attach to it with the web app‘s DB username, quasi executing option 2 for me.

Thanks for the clarification though! I‘ll try to write up my steps in a gist/blog post later.