Release stage: Generally Available

Creating a Postgres user for Fiber

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.

About Postgres Credentials

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:

• Username and Password - The connection string should include the username and password for a valid user account on the PostgreSQL server. On a topic below, we discuss the level of permission Fiber requires.
• Host and Port - Developers should provide the hostname or IP address of the machine where the PostgreSQL server is running. Additionally, they need to specify the port number on which the server is listening for incoming connections. By default, PostgreSQL uses port 5432, but this can be configured differently.
• Database Name - Developers must specify the name of the database to which they want to connect. PostgreSQL can host multiple databases on the same server, so it is essential to indicate the correct database.

Check the postgres documentation for more information.

What you'll need

• Owner access to your Postgres database
• A Postgres client like Postico or pgAdmin
• Permission to create a new user and grant permissions within the database

Step 1. Create a new user

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;

Step 2. Lower default access

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.