postgresql关于权限的总结

引用：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，否则会出现报错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