<p>I created a new table in Postgres, and I am having issues accessing it with Sequelize. I have already GRANTED all permissions to the table theoretically, and I can use the same account to access the table via my DB GUI.</p>

<p>Is there some additional permission needed for this relationship?</p>

<pre><code>Executing (default): SELECT date_trunc('day', "created_at"), COUNT("id") AS "COUNT" FROM "notice_opened_tbl" AS "notice_opened_tbl" WHERE "notice_opened_tbl"."merchant_id" = 40 GROUP BY date_trunc('day', "created_at") ORDER BY date_trunc('day', "created_at") ASC;
(node:45) UnhandledPromiseRejectionWarning: SequelizeDatabaseError: permission denied for relation notice_opened_tbl
</code></pre>

<p>EDIT: Current permissions - as you can see, they look correct. The user I am trying to connect with right now is postgresadmin (will be changed before production)</p>

<p><a href="https://i.stack.imgur.com/SwPFo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SwPFo.png" alt="permissions"></a></p>

<p>EDIT: I don't think it is related to the permissions - I've tried virtually everything related to them, <strong>and the query worked when I connected to the database from my local machine, but not from dev</strong>. The issue is only with this one newly created table though.</p>

<p>Also, this is on AWS if it helps anyone</p>

<p>Here is my dev setup - all queries BUT the query involving the new table works from here:</p>

<pre><code>const sequelize = new Sequelize(
    process.env.DATABASE_NAME || "DATABASENAMEHERE",
    process.env.DATABASE_USERNAME || "postgresadmin",
    process.env.DATABASE_PASSWORD || "PASSWORDHERE",
    {
        host: process.env.DATABASE_HOST || "postgres",
        dialect: "postgres",
        port: process.env.DATABASE_PORT || 5432,
        pool: {
            max: 10,
            min: 0,
            acquire: 30000,
            idle: 10000,
        },
        define: {
            timestamps: false,
        },
    });
</code></pre>

<p>Here is my local setup, which works from my local machine - the query works from here:</p>

<pre><code>const sequelize = new Sequelize(
    process.env.DATABASE_NAME || "DATABASEHERE",
    process.env.DATABASE_USERNAME || "postgresadmin",
    process.env.DATABASE_PASSWORD || "PASSWORDHERE",
    {
        host: process.env.DATABASE_HOST || "localhost",
        dialect: "postgres",
        port: process.env.DATABASE_PORT || 5465,
        pool: {
            max: 10,
            min: 0,
            acquire: 30000,
            idle: 10000,
        },
        define: {
            timestamps: false,
        },
    });
</code></pre>

<p>In dev, the host is postgres because that's the Kubernetes service associated with the database connection</p>

<p>Here's the code for the query (it is not cleaned up yet):</p>

<pre><code>let merchantId = parseInt(req.param("merchantId"), 10);
let noticeWhere = {
       merchant_id: merchantId,
    };
    if (req.query.startdate &amp;&amp; req.query.enddate) {
        // @ts-ignore
        noticeWhere.created_at = {
            [Op.between]: [req.query.startdate, req.query.enddate]
        };

    }

    let noticesOpened = NoticeOpened.aggregate(
        "id",
        "COUNT", {
            plain: false,
            where: noticeWhere,
            group: [sequelize.fn("date_trunc", "day", sequelize.col("created_at"))],
            order: [
                [sequelize.fn("date_trunc", "day", sequelize.col("created_at")), "ASC"]
            ],
        });
    return noticesOpened;
</code></pre>

<p>YAML output for the postgres Kubernetes service:</p>

<pre><code>apiVersion: v1
kind: Service
metadata:
  annotations:
    kubectl.kubernetes.io/last-applied-configuration: |
      {"apiVersion":"v1","kind":"Service","metadata":{"annotations":{},"creationTimestamp":"2019-07-21T02:44:44Z","name":"postgres","namespace":"default","resourceVersion":"169556","selfLink":"/api/v1/namespaces/default/services/postgres","uid":"7ef0a60f-ab61-11e9-8d66-06505b5dee68"},"spec":{"externalName":"URLHERE","ports":[{"port":5432,"protocol":"TCP","targetPort":5432}],"sessionAffinity":"None","type":"ExternalName"},"status":{"loadBalancer":{}}}
  creationTimestamp: 2019-11-23T00:07:00Z
  name: postgres
  namespace: default
  resourceVersion: "14358655"
  selfLink: /api/v1/namespaces/default/services/postgres
  uid: 2c1a58a4-0d85-11ea-a8cb-02dead532c7a
spec:
  externalName: URLHERE
  ports:
  - port: 5432
    protocol: TCP
    targetPort: 5432
  sessionAffinity: None
  type: ExternalName
status:
  loadBalancer: {}
</code></pre>

Steven Matthews
<p>Try running the following:</p>

<blockquote>
  <p>SCHEMANAME fill in</p>
</blockquote>

<pre><code>GRANT USAGE ON SCHEMA SCHEMANAME TO postgresadmin;

GRANT SELECT, INSERT, UPDATE, DELETE ON notice_opened_tbl IN SCHEMA SCHEMANAME TO postgresadmin;

GRANT SELECT, UPDATE, USAGE ON ALL SEQUENCES IN SCHEMA SCHEMANAME to postgresadmin

GRANT ALL PRIVILEGES ON TABLE notice_opened_tbl TO postgresadmin;
</code></pre>
