创建 数据库 bbb且owner为 用户 b：
|
1 2 3 |
postgres9.6@[ local ]:5432 postgres# create database bbb owner b; CREATE DATABASE Time : 259.099 ms |
默认情况下使用用户c也可以连接数据库bbb：
|
1 2 |
postgres9.6@[ local ]:5432 postgres# \c bbb c You are now connected to database "bbb" as user "c" . |
回收public的connect on database bbb权限：
|
1 2 3 4 5 |
c@[ local ]:5432 bbb# \c postgres postgres9.6 You are now connected to database "postgres" as user "postgres9.6" . postgres9.6@[ local ]:5432 postgres# revoke connect on database bbb from public ; REVOKE Time : 2.088 ms |
此时用户c没有了连接数据库bbb的权限：
|
1 2 3 4 |
postgres9.6@[ local ]:5432 postgres# \c bbb c FATAL: permission denied for database "bbb" DETAIL: User does not have CONNECT privilege. Previous connection kept |
但bbb数据库的owner用户b可以连接数据库：
|
1 2 3 |
postgres9.6@[ local ]:5432 postgres# \c bbb b You are now connected to database "bbb" as user "b" . b@[ local ]:5432 bbb# |
此种情况下超级用户也可以连接该数据库：
|
1 2 3 4 5 6 7 8 9 10 11 |
b@[ local ]:5432 bbb# \c bbb postgres9.6 You are now connected to database "bbb" as user "postgres9.6" . postgres9.6@[ local ]:5432 bbb# postgres9.6@[ local ]:5432 bbb# \du List of roles Role name | Attributes | Member of -------------+------------------------------------------------------------+----------- a | | {} b | | {} c | | {} postgres9.6 | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
另外一种方法：从pg_hba.conf中限定：
|
1 2 3 4 5 6 7 8 |
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust ... # host all all 0.0.0.0/0 md5 |
补充：Postgres限制每个用户只能连接指定数量的session，防止服务器资源紧张
限制每个用户只能连接指定数量的session，防止服务器资源紧张
（1）创建测试用户test:
|
1 2 3 4 5 6 7 8 |
highgo=# create user test; CREATEROLE highgo=#\du List of roles Role name | Attributes | Member of -----------+------------------------------------------------+---------- highgo | Superuser, Create role, Create DB, Replication | {} test | | {} |
（2）设置仅允许用户test使用一个连接
|
1 2 |
highgo=# ALTER ROLE test CONNECTION LIMIT 1; ALTERROLE |
（3）在session 1中使用test用户连接highgo数据库
|
1 2 3 |
highgo=>\c highgo test Youare now connected to database "highgo" as user "test" . highgo=> |
（4）在session 2中也使用test用户连接highgo数据库，会出现如下错误：
|
1 2 3 |
highgo=#\c highgo test 致命错误: 由角色 "test" 发起的连接太多了 Previousconnection kept |
（5）查询用户test链接限制
|
1 2 3 4 5 |
highgo=> SELECT rolconnlimit FROM pg_roles WHERE rolname = 'test' ; rolconnlimit -------------- 1 (1row) |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/lk_db/article/details/88952602
查看更多关于postgresql限制某个用户仅连接某一个数据库的操作的详细内容...