ITPub博客

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

postgresql关于权限的总结

原创 PostgreSQL 作者:lusklusklusk 时间:2019-10-28 17:12:40 0 删除 编辑

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,否则会出现报错Invalid operation: permission denied for 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都可以访问
alter default privileges in schema s9 grant select on tables to owner_2;
--当前用户执行如上语句后,此用户在s9下新建的任何表,owner_2都可以访问(其他用户用户创建的表,owner_2不能访问)
--上述语句不是这个意思:对于任何用户在s9下新建的表,owner_2都可以访问
alter default privileges for user user1,user2 in schema s9 grant select on tables to owner_2;
--以后user1,user2在schema s9下新建的表,用户owner_2都可以访问
备注:目前postgresql没有一种方法,可以使以后任何用户在s9下新建的表,owner_2都可以访问。
6、pg_hba.conf 的执行顺序是从上到下的,也就是上面的生效。pg_hba.conf是一个客户端的认证的文件,他限制的并不是权限,而是你是只能来自于哪里,必须使用什么认证方式
7、有时发现superuser居然没有createdb权限,pg_user或\du可以看到用户是superuser但是没有create db权限
8、对视图的授权方法,和table一样
grant select on table schemaname.viewname to user1
9、以下两种报错的解决思路,得出结论:postgresql只能查到对象权限,无法查询系统权限
Invalid operation: user "user1" cannot be dropped because the user has a privilege on some object;
ERROR:  role "role1" cannot be dropped because some objects depend on it
select * from information_schema.table_privileges where grantee='XX';--查到XX对所有表的对象权限,但是查不到select on all tables这样的系统权限,也查不到对视图的查询权限,所以revoke这些对象权限后,还是会报上面的错误,怎么查用户的所有对象权限和系统权限呢?
SELECT relname,relacl FROM pg_class WHERE relacl::TEXT LIKE '%user1%'
--对象权限,获取自pg_class.relacl,注意它只包含了在pg_class的对象(这里只有表、视图、序列、索引、物化视图、复合类型、TOAST表、外部表)
--系统权限,postgresql没有存放系统权限的系统表或系统视图, 也是说postgresql不像oracle一样有系统权限的概念
那么函数、类型、语言、数据库、表空间等的权限参见pg_proc.proacl , pg_type.typacl , pg_language.lanacl , pg_database.datacl , pg_tablespace.spcacl  
10、pg_user、pg_authid、pg_roles、pg_auth_members的区别
pg_user只存储用户信息
pg_authid、pg_roles没啥区别,pg_roles是建立在pg_authid上的系统视图,存储角色信息
pg_auth_members存储角色的成员关系,即某个角色组包含了哪些其他角色
备注:创建角色,赋予了login权限,则相当于创建了用户,如果没有赋予login权限,则这个角色只能在pg_roles里面看到,而在pg_user里面看不到





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/30126024/viewspace-2661690/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。11G OCM, 8年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    382
  • 访问量
    533090