Postgres 数据库用户规划

在SQL标准里，同一个模式下的对象是不能被不同的用户拥有的，而且有些数据库系统不允许创建和它们的所有者不同名的模式，如Oracle数据库。
实际上，在那些只实现了标准中规定的基本模式的数据库系统里，模式和用户的概念几乎是一样的，比如Oracle数据库。因此，许多用户考虑对名字加以修饰，使它们真正由“username.tablename”组成。如果在PostgreSQL中为每个用户都创建一个与用户名同名的模式，那么就能与Oracle数据库兼容了。
同样，在SQL标准中也没有public模式的概念。为了最大限度地遵循标准，并且与其他数据库兼容（如Oracle数据库），建议不要使用（甚至是应该删除）public模式。

规划
数据库可以包含多个模式，本文规划每个库仅对应一个模式，用户权限仅包含对模式中对象的增删改查

创建用户
create user tencent identified by 'tencent';
1
创建数据库
create database tencent;
1
创建模式
公共模式的处理还需要调研，目前还没有资料显示必须要删除

\c tencent
1
drop schema public;

create schema tencent;  --- 默认没有同名schema
1
设置默认路径
$user表示用户名，在这里表示tencent

postgres=# show search_path;
   search_path   
-----------------
 "$user", public    -- 默认没有同名schema，但如果建立了就先放在同名的。  悖论
(1 row)

-----

第一个元素声明搜索和当前用户同名的模式。因为还没有这样的模式存在，所以这条记录被忽略。第二个元素指向我们已经看过的公共模式。搜索路径中第一个存在的模式是创建新对象的缺省位置。这就是为什么缺省的对象都会创建在 public 模式里的原因。如果在其它环境中引用对象且没有模式修饰，那么系统会遍历搜索路径，直到找到一个匹配的对象。因此，在缺省的配置里，任何未修饰的访问只能引用 public 模式。要设置模式的搜索路径，可以用(省略了$user是因为并不立即需要它)

SET search_path TO myschema,public;
 

---------------------

alter user tencent  set search_path="$user",public;
1
授予权限
先赋予模式的权限

grant usage on schema tencent to tencent;
1
只对历史的一些对象授权，后期增加的对象是没有权限的，需要授予默认权限

grant select,insert,update,delete on all tables in schema tencent to tencent; 
grant select,update on all sequences in schema tencent to tencent; 
1
2
授予默认权限，后期增加的对象也会获得权限

ALTER DEFAULT PRIVILEGES IN SCHEMA tencent GRANT select,insert,update,delete ON TABLES TO tencent; 
ALTER DEFAULT PRIVILEGES IN SCHEMA tencent GRANT select,update ON sequences TO tencent;

----------------------------只读  读写用户-----------------

我正在尝试使用读写用户jirauser和只读用户设置数据库controlling_ro。这是我根据此Blog文章进行设置的脚本。testuser是主用户。

PGPASSWORD=XXX psql \
  --dbname=postgres \
  --host=10.0.193.45 \
  --port=5432 \
  --username=jirauser \
<<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数据库中。但是，postgres使用dbeaver查看默认数据库也可以找到它们。这是否意味着他们也有权访问postgres数据库？

 
-------工具的强大
Laurenz Albe2020-12-02 23:05:25
那只是你的客户端工具的产物。

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

你可以使用CONNECT对数据库对象的权限，也可以（通常）配置pg_hba.conf权限，以确定哪个用户可以访问哪个数据库。

sql:

create role test with login password 'test' noinherit;
create database test owner test;

pg_hba:

host all all 0.0.0.0/0 md5;
local all all md5;

登录:

psql -U test -d test

test#= \c 其他数据库
其他数据库#= ??

怎么像mysql那样主动授权后才能进不授权不能进。这个好像是默认都能进。

pgsql好像刚好反过来，主动取消授权的不能进，其他都能进。

因为默认每个用户都在 PUBLIC 里，你需要把它的 VIEW ANY DATABASE 权限关掉就可以了。

-
你先执行一下这条 SQL: REVOKE VIEW ANY DATABASE TO PUBLIC 然后再看看非 owner 或超级账号还能不能连接到这个库了？

 9.2 以前的版本

# 第一种写法
REVOKE CONNECT ON DATABASE <database> FROM PUBLIC;
# 第二种写法
REVOKE ALL PRIVILEGES ON DATABASE <database> FROM PUBLIC;

新版本要用上面的写法

但这样一来你得每个库在创建后都执行一次。

不过起码不用给每个用户挨个授权了，毕竟库少用户多。

postgres=# REVOKE ALL PRIVILEGES ON DATABASE pg FROM PUBLIC;
REVOKE
postgres=# REVOKE ALL PRIVILEGES ON any DATABASE FROM PUBLIC;
ERROR:  syntax error at or near "any"
LINE 1: REVOKE ALL PRIVILEGES ON any DATABASE FROM PUBLIC;
                                 ^
postgres=# REVOKE ALL PRIVILEGES ON  DATABASE FROM PUBLIC;
ERROR:  relation "database" does not exist
postgres=# REVOKE ALL PRIVILEGES ON  DATABASE all FROM PUBLIC;
ERROR:  syntax error at or near "all"
LINE 1: REVOKE ALL PRIVILEGES ON  DATABASE all FROM PUBLIC;
                                           ^
postgres=# 
 

pgsql 的默认安全行为通过 pg_hba.conf 来控制，默认是不做任何限制的（即 trust）。

-
官方对 PUBLIC 的描述原文是：

PostgreSQL grants default privileges on some types of objects to  PUBLIC. The default privileges granted to  PUBLIC are as follows:  CONNECT and  TEMPORARY (create temporary tables) privileges for databases;  EXECUTE privilege for functions; and  USAGE privilege for languages and data types (including domains).

翻译过来就是 PUBLIC 默认就有高亮的这些权限。

也就是说从目前来看 『官方』推荐使用 pg_hba来进行控制权限