<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>The permission denied for relation error is a general PostgreSQL error meaning that the user that's connected to the database doesn't have access to write or read a specific table.</p>

<p><a href="https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html" rel="nofollow noreferrer">DEFAULT PRIVILEGES</a> do not change permissions for existing objects.They are the default privileges for newly created objects and only for the particular role they belong to. If you do not define the role when running ALTER DEFAULT PRIVILEGES, it defaults to the current role (when executing the ALTER DEFAULT PRIVILEGES statement.</p>

<p>Common causes of this error are as follows:</p>

<ul>
<li><p>If you have created a new credential for your database, you'll need to configure the appropriate permissions for this credential well.</p></li>
<li><p>Row limits also may be the reason for this error.</p></li>
</ul>

<blockquote>
  <p>GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO someuser;</p>
</blockquote>

<p>you can simply move that GRANT ALL.. query all the way down to the bottom (the point where you created all the necessary table for your database). You can check the <a href="https://poanchen.github.io/blog/2018/03/07/How-to-fix-permission-denied-for-relation-some_table_name-in-PostgreSQL" rel="nofollow noreferrer">link</a> </p>
