postgresql 权限管理

逻辑结构：

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';*