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 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