我在Postgres中创建了一张新表,但在使用Sequelize访问该表时出现了问题。理论上,我已经授予了该表的所有权限,并且我可以使用相同的账户通过我的DB GUI访问该表。
这个关系需要额外的权限吗?请帮忙解决。
在开发中,主机是postgres,因为它是与数据库连接相关联的Kubernetes服务。以下是查询的代码(尚未清理):
这个关系需要额外的权限吗?请帮忙解决。
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
编辑:当前权限 - 如您所见,它们看起来是正确的。我现在正在尝试连接的用户是postgresadmin(在生产之前将更改)
编辑:我不认为这与权限有关 - 我已经尝试过与它们相关的几乎所有事情,并且当我从本地机器连接到数据库时,查询可以工作,但从dev中无法工作。但问题仅出现在这个新创建的表格上。
此外,如果对任何人有帮助,这是在AWS上的。
这是我的dev设置 - 除了涉及新表的查询之外,所有查询都可以从这里工作:
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,
},
});
这是我的本地设置,可以从本机运行 - 查询可以在此处工作:
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,
},
});
在开发中,主机是postgres,因为它是与数据库连接相关联的Kubernetes服务。以下是查询的代码(尚未清理):
let merchantId = parseInt(req.param("merchantId"), 10);
let noticeWhere = {
merchant_id: merchantId,
};
if (req.query.startdate && 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;
用于PostgreSQL Kubernetes服务的YAML输出:
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: {}
WITH "names"("name") AS (SELECT n.nspname AS "name" FROM pg_catalog.pg_namespace n WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema') SELECT "name", pg_catalog.has_schema_privilege('postgresadmin', "name", 'CREATE') AS "create", pg_catalog.has_schema_privilege('postgresadmin', "name", 'USAGE') AS "usage" FROM "names";
- undefined