仅在特定数据库中创建角色和用户，而不是在默认postgres数据库中创建角色和用户

Question

我正在尝试使用readwrite用户jirauser和只读用户controlling_ro来设置数据库。这是我基于这博客文章设置它的脚本。testuser是主要用户。

PGPASSWORD=XXX psql \
  --dbname=postgres \
  --host=XXX.XXX.eu-central-1.rds.amazonaws.com \
  --port=5432 \
  --username=testuser \
<<EOF
-- Clean DB
DROP DATABASE jiradb;
DROP USER jirauser;
DROP USER controlling_ro;
DROP SCHEMA jiraschema;
DROP ROLE readonly;
DROP ROLE readwrite;

-- Create DB
CREATE DATABASE jiradb;
\connect jiradb;
CREATE SCHEMA jiraschema;

-- Revoke privileges from 'public' role
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE jiradb FROM PUBLIC;

-- Read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE jiradb TO readonly;
GRANT USAGE ON SCHEMA jiraschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA jiraschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA jiraschema GRANT SELECT ON TABLES TO readonly;

-- Read/write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE jiradb TO readwrite;
GRANT USAGE, CREATE ON SCHEMA jiraschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA jiraschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA jiraschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA jiraschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA jiraschema GRANT USAGE ON SEQUENCES TO readwrite;

-- Users creation
CREATE USER controlling_ro WITH PASSWORD 'XXX';
CREATE USER jirauser WITH PASSWORD 'XXX';

-- Grant privileges to users
GRANT readonly TO controlling_ro;
GRANT readwrite TO jirauser;
EOF

在运行这个脚本之后，我希望角色和用户只在jiradb数据库中。但是，使用dbeaver查看默认数据库postgres时，它们也存在。这是否意味着他们也可以访问postgres数据库？

​

​

Answer 1

那只是你客户工具的产物。

实际上，PostgreSQL用户不属于任何数据库；它们是由所有数据库共享的。因此，无论在创建用户时连接到哪个数据库，它对于所有数据库都同样存在。

您可以对数据库对象使用CONNECT权限，或者(更典型地)配置pg_hba.conf以确定哪个用户可以访问哪个数据库。

Answer 2

根据https://www.postgresql.org/docs/current/sql-createrole.html

CREATE角色向PostgreSQL数据库集群添加一个新角色。角色是一个可以拥有数据库对象并具有数据库特权的实体；角色可以被视为“用户”、“组”，或者两者兼而有之，这取决于它的使用方式。有关管理用户和身份验证的信息，请参阅第21章和第20章。您必须拥有CREATEROLE特权，或者是数据库超级用户才能使用此命令。 注意，角色是在数据库集群级别定义的，因此在集群中的所有数据库中都是有效的。

所以这只是你的GUI误导了你。