在Postgresql中，“数据库”和“数据库”有什么区别？和“关系”？ （“错误关系x不存在”，“错误数据库x已经存在”） [英] In postgresql, what's the difference a "database" and a "relation"? ('error relation x does not exist', 'error database x already exists')

问题描述

我看到这两个错误的并列，鉴于Google搜索结果的匮乏，我不得不问。

I see the juxtaposition of these two errors and, given the dearth of Google search results, had to ask. What is the difference and what do I need to be doing here?

deploy=# GRANT SELECT ON angel_research_production TO angel_research;
ERROR:  relation "angel_research_production" does not exist
deploy=# create database angel_research_production;
ERROR:  database "angel_research_production" already exists

我的猜测是我需要做

所以我在postgres（dbroot）上运行它并得到它：

So I run this on postgres (dbroot) and get this:

postgres=# GRANT SELECT ON angel_research_production TO angel_research;
ERROR:  relation "angel_research_production" does not exist

因此它确实作为数据库存在，但不是关系。我该如何纠正这个问题？潜在的问题是什么？我有点不知所措。谢谢

So it does exist as a database, but not as a relation. How might I rectify this and what are the underlying issues here? I'm a little overwhelmed. Thanks

推荐答案

我的猜测是，您真的想递归 GRANT SELECT 对数据库 angel_research_production 中每个关系（表和视图）的访问权限。

My guess is that you really want to recursively GRANT the SELECT right to every relation (table and view) within the database angel_research_production. Correct?

如果是这样，在PostgreSQL 9.0及更高版本中有：

If so, in PostgreSQL 9.0 and above you have:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

来自 GRANT手册。请注意 SCHEMA中的所有表子句。用法：

from the manual for GRANT. Note the ALL TABLES IN SCHEMA clause. Usage:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO angel_research;

如果所有用户定义的对象都在 public 模式（请参见下文），

If all your user-defined objects are in the public schema (see below) that'll do the trick.

在以前的版本中没有这种功能，但是用户定义的函数作为解决方法而存在。

In prior versions there is no such feature, but user defined functions exist as workarounds.

Pg 9.0也具有< a href = http://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html rel = noreferrer>更改默认权限，它会更改默认 >分配给新创建的对象的特权。它不会影响现有对象。

Pg 9.0 also has ALTER DEFAULT PRIVILEGES, which changes the default privileges assigned to newly created objects. It does not affect existing objects.

如TokenMacGuy所述，关系是表或视图，而不是数据库。

As noted by TokenMacGuy, a relation is a table or view, not a database.

GRANT SELECT ON angel_research_production TO angel_research;

可以看作是以下内容的简写：

can be thought of as shorthand for:

GRANT SELECT ON TABLE angel_research_production TO angel_research
                ^^^^^

并且该表（关系）不存在，所以您收到了上面报告的错误。

and that table(relation) doesn't exist, so you're getting the error reported above.

在 GRANT手册或 psql \h GRANT 输出，您将看到：

In the manual for GRANT or the psql \h GRANT output you'll see:

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

这表明您可以 GRANT 到数据库是创建， CONNECT 和临时。数据库上没有 SELECT 。

This shows that the privileges you can GRANT to a database are CREATE, CONNECT and TEMPORARY. There is no SELECT right on a database.

Pg有四个组织级别：

There are four levels of organisation in Pg:

• 集群-由邮局局长控制，接受给定连接IP /端口组合，包含一个或多个数据库，包括内置的 template0 ， template1 和 postgres 数据库。由 postgresql.conf 和 pg_hba.conf 控制。数据库集群通常是由安装程序或软件包为您创建的。不要与集群作为计算集群或常规英语含义。

• Cluster - controlled by the postmaster, accepts connections on a given IP/port combo, contains one or more databases including the built-in template0, template1 and postgres databases. Controlled by postgresql.conf and pg_hba.conf. Your DB cluster is often created for you by an installer or package. Not to be confused with the normal meaning of cluster as a compute cluster or the general english language meaning.

数据库-包含一个或多个方案或 schemas 。连接到Pg时，您将连接到特定的数据库。

Database - contains one or more schemata or schemas. You connect to a specific database when connecting to Pg.

模式-包含对象，包括 relations 。如果没有另外指定，则用户创建的所有内容都会进入 public 模式。查询可以显式引用多个架构中的对象，也可以通过

Schema - contains objects including relations. If you don't specify otherwise, anything user-created goes into the public schema. Queries can reference objects in multiple schema explicitly or, via search_path, implicitly.

对象-某种程度上特定于PostgreSQL，模式中存在的任何内容（包括关系）。

Objects - Somewhat PostgreSQL specific, anything (including a relation) that exists in a schema.

• 关系-外观和行为类似于表格的事物，例如视图和表

其他对象也位于模式中，例如函数，强制转换，索引，序列，运算符，聚合等。

Other objects also reside in schemas, like functions, casts, indexes, sequences, operators, aggregates, etc.

这篇关于在Postgresql中，“数据库”和“数据库”有什么区别？和“关系”？ （“错误关系x不存在”，“错误数据库x已经存在”）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！