postgres权限管理

权限分类

实例权限

pg_hba.conf

实例级别的权限由pg_hba.conf来控制

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
host    all     		all     		0.0.0.0/0      			md5

TYPE：

• local：表示是unix-domain的socket连接

• host：TCP/IP socket

  注意：要使用该选项你要在postgresql.conf文件里设置listen_address选项，不在listen_address里的IP地址是无法匹配到的。因为默认的行为是只在localhost上监听本地连接。

• hostssl：SSL加密的TCP/IP socket

• hostnossl：非SSL

DATABASE：

• 数据库名，可以是"all", "sameuser", "samerole", "replication"。all表示所有，但不包括replication。多个数据库用“,”隔开。

USER：

• 用户名，可以为"all"，表示所有，也可以具体指定一个用户。多个用户用“,”隔开。和DATABASE一样，也可以将配置放到文件中，文件名加上前缀@

ADDRESS：

• 可以是为一个主机名，或者由IP地址和CIDR掩码组成。掩码可以为0-32（IPv4）或者0-128（IPv6）间的一个整数，32表示子网掩码为255.255.255.255，24表示子网掩码为255.255.255.0。主机名以“.”开头。samehost可以匹配所有主机、samenet可以匹配同一个掩码内的所有主机。

METHOD：

• trust：无条件的允许连接，不需要任何口令，即使口令是错误的
• reject：无条件拒绝连接，常用于拒绝某些用户访问，常见的拒绝用户连接template1
• md5：使用md5加密的口令进行认证
• password：和md5一样，但是口令是以明文形式在网络上传递，比较危险
• scram-sha-256：pg10新增，这是当前提供的方法中最安全的方法，但是较旧的客户端库不支持此方法
• md5和scram-sha-256会以对应的方式加密再发送密码
• ident：映射关系pg_ident.conf文件中
• perr：该模式使用连接发起端的操作系统名进行身份验证。仅限于Linux、BSD、Mac OS X和Solaris，并且仅可用于本地服务器发起的连接。

pg_hba.conf中可以配置那些用户，以及那些ip地址可以通过何种方式来访问那些数据库

• 参数配置如下

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
  host    all     		all     		0.0.0.0/0      			md5
  host表示tcp/ip的方式，第一个all表示该主机上的所有数据库实例，第二个all表示所有的用户，ip地址是所有的ip地址，md5表示密码验证方式
  
  

使用方法

允许所有的用户，能够以tcp/ip方式访问所有的数据库，密码认证方式md5

host      all     all     0.0.0.0/0       md5

不允许所有的用户以tcp/ip方式访问template1模板库

host    template1       all             0.0.0.0/0               reject  

[postgres@localhost ~]$ psql -Usa template1 
psql (11.1)
Type "help" for help.

template1=# q
[postgres@localhost ~]$ psql -Usa template1 -h 192.168.1.2
psql: 致命错误:  pg_hba.conf 记录拒绝来自主机"192.168.1.2", 用户"sa", 数据库"template1", SSL 关闭的复制连接

允许本地所有用户可以无密码访问任何数据库

local   all             all                                     trust

数据库权限

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

赋予用户在指定数据库下connect或者创建schema权限

默认情况下数据库在创建后，允许public角色连接，既允许任何人连接

只能是超级用户和owner才能在数据库中创建schema

数据库创建后会默认创建public的schema，这个schema的all权限已经赋予给public角色，即任何人在里面创建对象

postgres=# create database test;
CREATE DATABASE
postgres=# create user ceshi;
CREATE ROLE
postgres=# c test ceshi
You are now connected to database "test" as user "ceshi".
test=> dn
List of schemas
  Name  | Owner 
--------+-------
 public | sa
(1 row)

test=> create table public.test(id int);
CREATE TABLE
test=> 
test=> select * from INFORMATION_SCHEMA.role_table_grants where table_catalog = 'test' and grantee = 'ceshi'
test-> ;
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 ceshi   | ceshi   | test          | public       | test       | INSERT         | YES          | NO
 ceshi   | ceshi   | test          | public       | test       | SELECT         | YES          | YES
 ceshi   | ceshi   | test          | public       | test       | UPDATE         | YES          | NO
 ceshi   | ceshi   | test          | public       | test       | DELETE         | YES          | NO
 ceshi   | ceshi   | test          | public       | test       | TRUNCATE       | YES          | NO
 ceshi   | ceshi   | test          | public       | test       | REFERENCES     | YES          | NO
 ceshi   | ceshi   | test          | public       | test       | TRIGGER        | YES          | NO
(7 rows)

schema权限

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

包括允许查看schema中的对象，允许在schema中创建对象

新建的schema只有超级用户和owner有权限在该shcmea下查看或者新建对象

注意

According to the SQL standard, the owner of a schema always owns all objects within it. PostgreSQL allows schemas to contain objects owned by users other than the schema owner. This can happen only if the schema owner grants the CREATE privilege on his schema to someone else, or a superuser chooses to create objects in it.

schema的owner默认是该schema下的所有对象的owner。
同时PostgreSQL还允许用户在别人的schema下创建对象（赋权后），所以一个对象可能属于”两个”owner。 更”糟糕”的是schema
的owner有 drop该schema下面的所有对象的权限。或者直接drop schema cascade来删除整个schema.

test=# create role r1 login;
CREATE ROLE
test=# create role r2 login;
CREATE ROLE
test=# grant all on database test to r1;
GRANT
test=# grant all on database test to r2;
GRANT
test=# c test r1
You are now connected to database "test" as user "r1".
test=> create schema r1;
CREATE SCHEMA
test=> grant all on schema r1 to r2;
GRANT
test=> c test r2;
You are now connected to database "test" as user "r2".
test=> create table r1.t(id int);
CREATE TABLE
test=> c test sa
You are now connected to database "test" as user "sa".
test=# create table r1.t1(id int);
CREATE TABLE
postgres=> c test r1
You are now connected to database "test" as user "r1".
test=> drop table r1.t;
DROP TABLE
test=> drop table r1.t1;
DROP TABLE

上面的例子就是schema的owner删除了其他账号建的对象

对于database的owner也存在这个问题，它同样具有删除database中任何其他用户创建的对象的权力。

postgres=# alter user r1 createdb;
ALTER ROLE
postgres=# c postgres r1;
You are now connected to database "postgres" as user "r1".
postgres=> create database db1;
CREATE DATABASE
postgres=> grant all on database db1 to r2;
GRANT
postgres=> c db1 r2
You are now connected to database "db1" as user "r2".
db1=> create schema r2;
CREATE SCHEMA
db1=> create table r2.t(id int);
CREATE TABLE
db1=> insert into t select generate_series(1,100);
INSERT 0 100
db1=> c db1 sa
You are now connected to database "db1" as user "sa".
db1=# create table t(id int);
CREATE TABLE
db1=# insert into t select generate_series(1,100);
INSERT 0 100
db1=# c db1 r1
You are now connected to database "db1" as user "r1".
db1=> drop table r2.t;
错误:  对模式 r2 权限不够
db1=> drop table public.t;
错误:  must be owner of table t
db1=> drop schema r2;
错误:  必须是模式 r2 的属主
db1=> drop schema public;
错误:  必须是模式 public 的属主
db1=> c postgres r1
You are now connected to database "postgres" as user "r1".
postgres=> drop database db1;
DROP DATABASE

owner虽然不能删除database下面其他用户创建的schema和表，但是可以直接删除database

所以要么开始就各个数据库各自管理，不要将混合起来管理。另外一个办法就是database和schema有创建等权限，但是普通用户只有对象的读写等权限

owner能否访问其他用户创建的表数据呢？

postgres=> c test r2;
You are now connected to database "test" as user "r2".
test=> create table r1.t(id int);
CREATE TABLE
test=> insert into r1.t select generate_series(1,100);
INSERT 0 100
test=> c test r1
You are now connected to database "test" as user "r1".
test=> select * from r1.t;
错误:  permission denied for table t

用户r1虽然是r1的owner但是却没有访问其他用户在该schema下面创建的对象的权限

public权限

public权限单独处理，巡检过程中为什么要回收public权限？

新创建的数据库都有一个public的schema，正常情况下，普通用户创建的表都在public下面。默认情况下普通用户是有在public下创建表的权限。

--创建角色role_b
postgres=# create user role_b with password '123456';
CREATE ROLE
--赋予connect权限database
postgres=# grant connect on database postgres to role_b;
GRANT
postgres=# c postgres role_b
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "role_b".

--没有创建schema权限
postgres=> create schema tbb;
ERROR:  permission denied for database postgres
postgres=> select current_schema;
 current_schema 
----------------
 public
(1 row)
--但是有创建表和删除表的权限
postgres=> create table test1(id int);
CREATE TABLE
postgres=> drop table test1;
DROP TABLE

--回收schmea的public权限
postgres=> c postgres sa
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "sa".
postgres=# revoke all on schema public from public;
REVOKE
postgres=# c postgres role_b
psql (9.6.4, server 9.5.3)
You are now connected to database "postgres" as user "role_b".
postgres=> 
postgres=> create table test1(id int);
ERROR:  no schema has been selected to create in
LINE 1: create table test1(id int);

用户权限管理

理解PostgreSQL中的权限

object权限

用户（user），角色（role），组（group）在pg中都是一个概念

create user是create role的一个别名，唯一的区别是create user中的login权限被作为默认值，而create role默认为nologin

public角色，代表所有人的意思

在数据库权限新建的test库，可以看到dual表的grantee为PUBLIC
test=> c test sa
You are now connected to database "test" as user "sa".
test=# select * from INFORMATION_SCHEMA.role_table_grants where table_catalog = 'test' and table_name = 'dual' and grantee = 'PUBLIC';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 sa      | PUBLIC  | test          | public       | dual       | INSERT         | NO           | NO
 sa      | PUBLIC  | test          | public       | dual       | SELECT         | NO           | YES
 sa      | PUBLIC  | test          | public       | dual       | UPDATE         | NO           | NO
 sa      | PUBLIC  | test          | public       | dual       | DELETE         | NO           | NO
 sa      | PUBLIC  | test          | public       | dual       | TRUNCATE       | NO           | NO
 sa      | PUBLIC  | test          | public       | dual       | REFERENCES     | NO           | NO
 sa      | PUBLIC  | test          | public       | dual       | TRIGGER        | NO           | NO
(7 rows)

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

新建用户然后给表赋权限

test=> c postgres sa
You are now connected to database "postgres" as user "sa".
postgres=# create table test(id int);
CREATE TABLE
postgres=# drop table test;
DROP TABLE
postgres=# create schema schema1;
CREATE SCHEMA
postgres=# set search_path=schema1;
SET
postgres=# create table schema1.test(id int);
CREATE TABLE
postgres=# insert into schema1.test select generate_series(1,10);
INSERT 0 10
postgres=# 
postgres=# create role role_a  with password '123456' login;
CREATE ROLE
--直接grant database后是没有权限访问下面的表的
postgres=# grant all on database postgres to role_a;
GRANT
postgres=# c postgres role_a
You are now connected to database "postgres" as user "role_a".
postgres=> select * from schema1.test;
错误:  对模式 schema1 权限不够
LINE 1: select * from schema1.test;
                      ^
--all tables in schema还是不行
postgres=> c postgres sa
You are now connected to database "postgres" as user "sa".
postgres=# grant select on all tables in schema schema1 to role_a;
GRANT
postgres=# c postgres role_a
You are now connected to database "postgres" as user "role_a".

postgres=> select * from schema1.test;
错误:  对模式 schema1 权限不够
LINE 1: select * from schema1.test;
                      ^
--on schema后能正常访问
postgres=> c postgres sa 
You are now connected to database "postgres" as user "sa".
postgres=# grant all on schema schema1 to role_a;
GRANT
postgres=# c postgres role_a
You are now connected to database "postgres" as user "role_a".
postgres=> select * from schema1.test limit 1;
 id 
----
  1
(1 row)

回收掉all tables in schema权限后，role_a便不能查询该表

postgres=> c postgres sa
You are now connected to database "postgres" as user "sa".
postgres=# revoke all on all tables in schema schema1 from role_a;
REVOKE
postgres=# c postgres role_a;
You are now connected to database "postgres" as user "role_a".
postgres=> select * from schema1.test limit 1;
错误:  permission denied for table test

如果用普通账号回收权限会报下面的警告

postgres=> select current_user;
 current_user 
--------------
 role_a
(1 row)

postgres=> revoke  select on all tables in schema schema1 from role_a cascade;
警告:  不能为"test"撤销权限
警告:  不能从关系"test"的列"tableoid"上撤销权限
警告:  不能从关系"test"的列"cmax"上撤销权限
警告:  不能从关系"test"的列"xmax"上撤销权限
警告:  不能从关系"test"的列"cmin"上撤销权限
警告:  不能从关系"test"的列"xmin"上撤销权限
警告:  不能从关系"test"的列"ctid"上撤销权限
警告:  不能从关系"test"的列"id"上撤销权限
REVOKE

参考资料