Release stage: Generally Available
Follow these steps to allow Fiber to write data into your database. Instead of using the same database credentials as your own backend services, you will be creating a new user with scope limited to particular tables and a connection cap.
By default, new users in Postgres have access to all the tables in the public schema. We will demonstrate how to limit access to only the specific tables that will receive the Shopify data. This will help your team (and our team!) sleep better at night, knowing Fiber can't access any of your existing tables.
If you are creating a destination using the app, we're going to ask you to provide credentials to access your Postgres instance. We might expect the same information as seen in Postgres Connection Strings.
This is what we need:
Check the postgres documentation for more information.
CREATE USER fiber
PASSWORD '<insert a long password here>'
CONNECTION LIMIT 10;
GRANT CREATE, TEMPORARY ON DATABASE "DatabaseName" TO fiber;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM fiber;
Setting a connection limit smaller than 10 may lead to data backlog issues that eventually trigger Fiber to suspend sync unexpectedly.
Every Postgres user is part of an implicitly-defined group called PUBLIC
, which by default allows them to read and write to all tables in the public
schema. In order to limit the scopes of the newly-created fiber
user, we must first lower the privileges of this PUBLIC
group.
Before we continue, you will want to check that no other Postgres users depend on these default PUBLIC
privileges which we are about to revoke. To do so, you can run \dg
via psql.
Here’s an example showing a Postgres database running on GCP:
~ $ psql postgres://postgres:PASSWORD@DATABASE_IP/prod
psql (14.6 (Homebrew), server 14.4)
Type "help" for help.
prod=> \dg
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+--------------------------------
cloudsqladmin | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
cloudsqlagent | Create role, Create DB | {cloudsqlsuperuser}
cloudsqliamserviceaccount | Cannot login | {}
cloudsqliamuser | Cannot login | {}
cloudsqlimportexport | Create role, Create DB | {cloudsqlsuperuser}
cloudsqlreplica | Replication | {pg_monitor}
cloudsqlsuperuser | Create role, Create DB | {pg_monitor,pg_signal_backend}
fiber | 10 connections | {}
postgres | Create role, Create DB | {cloudsqlsuperuser}
prod=>
As you can see, other than the internal roles created by Cloud SQL, the only users in this database are fiber
, which we just created above, and postgres
, which actually owns the “prod” database and therefore has privileges beyond the PUBLIC
group.
For the database in this example, we would skip straight to the revoke command below. If you do, however, see listed other users created by your team, you may want to grant them privileges on the tables directly, like so:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO <username>;
Finally, you can revoke PUBLIC
access to the tables in the public
schema:
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM PUBLIC;
That’s it — now the fiber
user can’t read or write any data into your other tables.