ITPub博客

首页 > 数据库 > PostgreSQL > postgresql关于权限的总结

postgresql关于权限的总结

PostgreSQL 作者:零度的雨1205 时间:2019-12-13 10:46:29 0 删除 编辑

转载自: http://blog.itpub.net/30126024/viewspace-2661690/


1、每个实例可以多个db,每个db有自己的owner,每个db下可以建立多个schema,每个schema有自己的owner,每个schema下可以创建多张表,每张表都有自己的owner
2、db owner不一定能操作其下面的某个schema
3、schema owner不一定能操作其下面的某张表
4、授予某个用户select on all tables in schema XX时,需要先对用户授权usage访问schema XX
grant usage on schema s9 to owner_2;
grant select on all tables in schema s9 to owner_2;
--授权owner_2可以查询s9下面的所有表,这种方式仅对已经存在的表有效。以后建立的表不会自动有只读权限
5、以上4仅用户只能查询该schema下已经存在的表,无法查询该schema下新建的表,如果想对该schema下新建的表也获得权限,需要对该schema的owner授权给用户
alter default privileges for user s9_owner in schema s9 grant select on tables to owner_2;
--以后schema s9的owner s9_owner在schema s9下新建的表,用户owner_2都可以访问
6、pg_hba.conf 的执行顺序是从上到下的,也就是上面的生效。pg_hba.conf是一个客户端的认证的文件,他限制的并不是权限,而是你是只能来自于哪里,必须使用什么认证方式



db owner不一定能操作其下面的某个schema
schema owner不一定能操作其下面的某张表

1、superuser建立3个用户dbuser1、schemauser1、schemauser2,授权用户dbuser1具备create db权限
create user dbuser1 createdb password '123456';
create user schemauser1 password '123456';
create user schemauser2 password '123456';

2、dbuser1创建DB1,superuser授权schemauser1、schemauser2在db1上有创建schema的权限
\c - dbuser1
create database db1;
\c - postgres
grant create on database db1 to schemauser1;
grant create on database db1 to schemauser2;

3、schemauser1、schemauser2分别在db1上创建schema1、schema2,并建立表schema1.table1、schema2.table2
\c db1
\c - schemauser1
create schema schema1;
create table schema1.table1 (hid int);
insert into  schema1.table1 values (1),(2);
select * from schema1.table1;
\c - schemauser2
create schema schema2;
create table schema2.table2 (hid int);
insert into  schema2.table2 values (1),(2);
select * from schema2.table2;

4、superuser在db1.schema1、db1.schema2上建立表supertable1,supertable2
\c - postgres
create table schema1.supertable1 (hid int);
insert into  schema1.supertable1 values (1),(2);
select * from schema1.supertable1;
create table schema2.supertable2 (hid int);
insert into  schema2.supertable2 values (1),(2);
select * from schema2.supertable2;

5、验证
5.1、dbuser1是否可以查询schema1.table1、schema2.table2、schema1.supertable1、schema2.supertable2
不可以
5.2、dbuser1是否可以在schema1、schema2上建立表schema1.dbtable1、schema2.dbtable2     
不可以  
5.3、schemauser1是否可以查询schema1.supertable1、schema2.table2、schema2.supertable2
不可以
5.4、schemauser2是否可以查询schema2.supertable2、schema1.table1、schema1.supertable1
不可以

\c - dbuser1
db1=> select * from  pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;
 schemaname |  tablename  | tableowner  | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-------------+-------------+------------+------------+----------+-------------+-------------
 schema1    | supertable1 | postgre2    |            | f          | f        | f           | f
 schema2    | supertable2 | postgre2    |            | f          | f        | f           | f
 schema1    | table1      | schemauser1 |            | f          | f        | f           | f
 schema2    | table2      | schemauser2 |            | f          | f        | f           | f
(4 rows)

db1=> select * from schema1.table1;
ERROR:  permission denied for schema schema1
LINE 1: select * from schema1.table1;
db1=> select * from schema1.supertable1;
ERROR:  permission denied for schema schema1
LINE 1: select * from schema1.supertable1;

db1=> create table schema1.dbtable1 (hid int);
ERROR:  permission denied for schema schema1
LINE 1: create table schema1.dbtable1 (hid int);
db1=> create table schema2.dbtable2 (hid int);
ERROR:  permission denied for schema schema2
LINE 1: create table schema2.dbtable2 (hid int);














光授权select on all tables in schema,而没有授权usage on schema,用户无法查询schema下的表

postgres=# create user testuser1 password '123456';
CREATE ROLE
postgres=# create user testuser2 password '123456';
CREATE ROLE

db1=# grant select on all tables in schema schema1 to testuser1;
GRANT
db1=# \c - testuser1
You are now connected to database "db1" as user "testuser1".
db1=> select count(*) from schema1.table1;
ERROR:  permission denied for schema schema1
LINE 1: select * from schema1.table1;
db1=> \c - postgres
db1=# grant usage on schema schema1 to testuser1;
GRANT
db1=# \c - testuser1
You are now connected to database "db1" as user "testuser1".
db1=> select count(*) from schema1.table1;
 count
-------
     2
(1 row)


db1=# grant usage on schema schema1 to testuser2;
GRANT
db1=# grant select on all tables in schema schema1 to testuser2;
GRANT
db1=# \c - testuser2
You are now connected to database "db1" as user "testuser2".
db1=> select count(*) from schema1.table1;
 count
-------
     2
(1 row)





schema下新建的表也能被授权用户查询,需要对该schema的owner授权给用户,如下testuser1和testuser2都具备select on all tables in schema schema1,schema1的owner是schemauser1,schemauser1的权限授给了testuser2,所以schemauser1在schema1新建的表,testuser2可以查询,但是testuser1无法查询

db1=> \c - postgres
db1=# alter default privileges for user schemauser1 in schema schema1 grant select on tables to testuser2;
db1=# \c - schemauser1
db1=> select * into schema1.table3 from schema1.table1;
db1=> \c - testuser1
You are now connected to database "db1" as user "testuser1".
db1=> select * from schema1.table3;
ERROR:  permission denied for table table3
db1=> \c - testuser2
You are now connected to database "db1" as user "testuser2".
db1=> select * from schema1.table3;
 hid
-----
   1
   2
(2 rows)



没有createdb权限,则无法创建database,有了createdb权限还可以在自己创建的db下创建schema
postgres=# \c - testuser1
You are now connected to database "postgres" as user "testuser1".
postgres=> create database testdb;
ERROR:  permission denied to create database
postgres=>\c - postgres
postgres=# alter user testuser1 createdb;
postgres=# \c - testuser1
postgres=> create database testdb;
CREATE DATABASE
postgres=> \c testdb
You are now connected to database "testdb" as user "testuser1".
testdb=> create schema tests1;
CREATE SCHEMA


在其他db_ower的db下,没有授权CREATE on database权限的话,用户无法创建schema,有了create权限后,在自己建立的schema下可以创建表
testdb=> \c db1
You are now connected to database "db1" as user "testuser1".
db1=> create schema tests2;
ERROR:  permission denied for database db1
testdb=>\c - postgres
db1=# grant CREATE on database db1 to testuser1;
db1=# \c - testuser1
db1=> create schema tests2;
db1=> create table tests2.table1 (hid int);


在其他schema_owner的schema下,没有CREATE on schema权限的话,用户无法创建表
db1=> \c - postgres
db1=# create schema tests3;
db1=# \c - testuser1
db1=> create table tests3.table (hid int);
ERROR:  permission denied for schema tests3
LINE 1: create table tests3.table (hid int);
db1=> \c - postgres
db1=# grant CREATE on schema tests3 to testuser1;
db1=> create table tests3.table (hid int);
CREATE TABLE




pg_hba.conf 上面的生效
pg_hba.conf 内容如下,则systemctl restart postgresql-11后,本地psql命令需要密码
local   all             all                                     md5
local   all             all                                     trust

pg_hba.conf 内容如下,则systemctl restart postgresql-11后,本地psql命令不需要密码
local   all             all                                     trust
local   all             all                                     md5

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69902769/viewspace-2668421/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
本人的所有文章,仅代表一个学习的过程,其中不乏有错误之处,欢迎指正!

注册时间:2018-12-14

  • 博文量
    34
  • 访问量
    46151