postgresql关于权限的知识点有哪些

开发技术作者：iii 2024-04-05 02:40:01

这篇文章主要介绍“postgresql关于权限的知识点有哪些”，在日常操作中，相信很多人在postgresql关于权限的知识点有哪些问题上存在疑惑，小编查阅了各式资料，整...

这篇文章主要介绍“postgresql关于权限的知识点有哪些”，在日常操作中，相信很多人在postgresql关于权限的知识点有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”postgresql关于权限的知识点有哪些”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

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