postgresql 权限管理

  1. 云栖社区>
  2. 博客>
  3. 正文

postgresql 权限管理

派大星110 2018-01-30 17:37:58 浏览1614
展开阅读全文

逻辑结构:

instance–》database–》schema–》object

public权限默认赋予,所有人的默认权限。

权限体系:

实例权限–》pg_hba.conf

数据库权限–》grant 赋予是否允许连接或创建schema权限,revoke

schema权限–》grant赋予允许查询schema中的对象,在schema中创建对象,revoke

object权限–》grant,revoke

表空间–》grant赋予允许在对应表空间创建表,物化视图,索引,临时表

数据库级别权限:连接数据库,创建schema

  • 默认情况下,数据库在创建后,允许public角色连接,即允许任何人连接。
  • 默认情况下,数据库在创建后,不允许除了超级用户和owner之外的任何人在数据库中创建schema。
  • 默认情况下,数据库在创建后,会自动创建名为public的schema,这个schema的all权限已经赋予给了public角色,即允许任何人在里面创建对象。

权限赋予步骤:

对象:

gran select,update on all tables in schema schema_name to user_name;

revoke select,update on all tables in schema schema_name from user_name;

schema:

GRANT { { CREATE | USAGE } [, …] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, …]
TO role_specification [, …] [ WITH GRANT OPTION ]

grant usage on schema schema_name to user_name; –允许查看schema权限

database:

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, …] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, …]
TO role_specification [, …] [ WITH GRANT OPTION ]

grant connect on database database_name to user_name;–连接权限

instance:

pg_hba.conf

创建数据库即读写 只读用户(一个用户对数据库拥有所有权限):

postgres:

create role dba_rw with password ‘123’ login;

create role dba_r with password ‘123’ login;

create database dba_db owner djidba_rw;

revoke all on DATABASE dba_db from PUBLIC;

grant CONNECT on DATABASE dba_db to dba_r;

切换至dba_db数据库下

create schema djidba;

grant USAGE on SCHEMA dba_rw to dba_r;

grant SELECT on ALL tables in schema dba_rw to dba_r;

alter user dba_r set default_transaction_read_only to off; –设置只读用户

创建数据库但不把owner赋予某个用户:

postgres用户:

create role dba_rw with password ‘123’ login;

create role dba_r with password ‘123’ login;

create database dba_db;

revoke all all on DATABASE dba_db from PUBLIC;

grant CONNECT on DATABASE dba_db to djidba_rw;

grant CONNECT on DATABASE dba_db to djidba_r;

c dba_db

create schema dba_rw;

create shcema dba_r;

alter schema dba_rw owner to dba_rw;

alter schema dba_r owner to dba_r;

grant USAGE on SCHEMA dba_rw to dba_r;

grant SELECT on ALL tables in schema dba_rw to dba_r;

alter user dba_r set default_transaction_read_only to off;

默认权限设置,上面的权限赋予只能解决已经存在的表权限,当我们需要给用户新建表的权限时,需要设置默认权限。

alter default privileges in schema schma_name grant select ON tables to username;

查询用户权限:
select from information_schema.table_privileges where grantee='user_name';*

网友评论

登录后评论
0/500
评论
派大星110
+ 关注