postgresql限制某个用户仅连接某一个数据库的操作_PostgreSQL_

 postgres9.6@[local]:5432 postgres# create database bbb owner b; CREATE DATABASE Time: 259.099 ms

 postgres9.6@[local]:5432 postgres# \c bbb c You are now connected to database "bbb" as user "c".

 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

 postgres9.6@[local]:5432 postgres# \c bbb c FATAL: permission denied for database "bbb" DETAIL: User does not have CONNECT privilege. Previous connection kept

 postgres9.6@[local]:5432 postgres# \c bbb b You are now connected to database "bbb" as user "b". b@[local]:5432 bbb# 

 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 | {} 

 # 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 

 highgo=#create user test; CREATEROLE highgo=#\du List of roles Role name | Attributes | Member of -----------+------------------------------------------------+---------- highgo | Superuser, Create role, Create DB, Replication | {} test | | {} 

 highgo=#ALTER ROLE test CONNECTION LIMIT 1; ALTERROLE 

 highgo=>\c highgo test Youare now connected to database "highgo" as user "test". highgo=>

 highgo=#\c highgo test 致命错误: 由角色"test"发起的连接太多了 Previousconnection kept 

 highgo=>SELECT rolconnlimit FROM pg_roles WHERE rolname = 'test'; rolconnlimit -------------- 1 (1row)