基于PostgreSQL 权限解读

2023-07-07 05:36:03 62

1public权限解读

用户默认情况下具有public权限，public默认具有创建和使用schema的权限，因此意味着可以在schema中创建对象（包括表）、列出schema中的对象，并在其权限允许时访问它们。

所以创建数据库或schema完成后做的第一件事是

1.1创建数据库完成后

--回收schema的public权限
--revokeallonschemapublicfrompublic;--正常情况使用这个
revokeallonschemapublic,tiger,tiger_data,topologyfrompublic;--使用postgis扩展使用这个
--从core数据库收回public的所有权限
revokeallondatabasecorefrompublic;
revokeallondatabasepostgresfrompublic;
--重新将schema授予数据库所有者
grantallonschemapublic,tiger,tiger_data,topologyto数据库所有者;

1.2创建schema完成后

createschemahistory;
revokeallonschemahistoryfrompublic;
--重新将schemahistory授予数据库所有者
grantallonschemahistoryto数据库所有者;

2测试

假设现在有一数据库core.

2.1postgrese用户连接至core数据库

psql-hlocalhost-p5432-Upostgres-dcore

--创建一个名称sel的用户
createuserselwithnosuperusernocreatedbnocreaterolenoinheritloginnoreplicationnobypassrlspassword'123';
--允许连接至数据库core
grantconnectondatabasecoretosel;

--查看角色使用\duS+
\duS+
--或
\duS+sel

--查看用户在schema的权限
\dp

--查看数据库的用户
\l+

权限解释：

r--SELECT("读")
w--UPDATE("写")
a--INSERT("追加")
d--DELETE
D--TRUNCATE
x--REFERENCES
t--TRIGGER
X--EXECUTE
U--USAGE
C--CREATE
c--CONNECT
T--TEMPORARY
arwdDxt--ALLPRIVILEGES（对于表，对其他对象会变化）
*--用于前述特权的授权选项

2.2在另一个进程中运行

psql-hlocalhost-p5432-Usel-dcore

droptableaaa;
createtableaaa(
objectidinteger
);
select*fromfeatures;

上面的sql报

ERROR:table"aaa"doesnotexist
ERROR:noschemahasbeenselectedtocreatein
ERROR:relation"features"doesnotexist

2.3用postgrese用户登录core数据库为sel用户授权

psql-hlocalhost-p5432-Upostgres-dcore

您需要什么权限就授予什么权限，这里授予sel用户可以selectpublicschema下的所有对象。

grantusageonschemapublictosel;
grantselectonalltablesinschemapublictosel;

2.4再次转到在另一个进程中运行

psql-hlocalhost-p5432-Usel-dcore

select*fromfeatures;
deletefromfeatures;

现在已经可以正常查询数据了，但是此用户只有查询权限。

3删除用户

3.1使用postgrese用户连接至core数据库

注意你授予用户什么权限必须在drop用户前先revoke，然后再删除，否则报依赖错。所以授权文件保存好以便将来删除用户，不然查询关联性非常麻烦。

psql-hlocalhost-p5432-Upostgres-dcore

--回收分配的权限并删除用户
revokeselectonalltablesinschemapublicfromsel;
revokeusageonschemapublicfromsel;
revokeconnectondatabasecorefromsel;
droprolesel;

补充：PostgreSQL用户和权限管理

默认用户

postgres安装完成后，会自动在操作系统和postgres数据库中分别创建一个名为postgres的用户以及一个同样名为postgres的数据库。

创建用户

方式1:在系统命令行中使用createuser命令中创建

createuserusername

方式2:在PostgresSQL命令行中使用CREATEROLE指令创建

CREATEROLErolename;

方式3:在PostgresSQL命令行中使用CREATEUSER指令创建

CREATEUSERusername;

CREATEUSER和CREATEROLE的区别在于，CREATEUSER指令创建的用户默认是有登录权限的，而CREATEROLE没有。

\du指令显示用户和用户的用户属性

创建用户时设定用户属性

基本语法格式

CREATEROLErole_nameWITHoptional_permissions;

示例:在创建用户时设定登录权限。

CREATEROLEusernameWITHLOGIN;

可以通过\hCREATEROLE指令查看全部可设置的管理权限

修改用户属性

修改权限的命令格式

ALTERROLEusernameWITHattribute_options;

例如:可通过以下方式禁止用户登录

ALTERROLEusernameWITHNOLOGIN;

设置访问权限

语法格式如下:

GRANTpermission_typeONtable_nameTOrole_name;

实例:

GRANTUPDATEONdemoTOdemo_role;--赋予demo_roledemo表的update权限
GRANTSELECTONALLTABLESINSCHEMAPUBLICtodemo_role;--赋予demo_role所有表的SELECT权限

特殊符号:ALL代表所访问权限，PUBLIC代表所有用户

GRANTALLONdemoTOdemo_role;--赋给用户所有权限
GRANTSELECTONdemoTOPUBLIC;--将SELECT权限赋给所有用户

\z或\dp指令显示用户访问权限。

\hGRANT显示所有可设置的访问权限

撤销用户访问权限

语法格式如下:

REVOKEpermission_typeONtable_nameFROMuser_name;

其中permission_type和table_name含义与GRANT指令中相同。

用户组

在postgres中用户实际上是role，同时组也是role。包含其他role的role就是组。

创建组示例:

CREATEROLEtemporary_users;
GRANTtemporary_usersTOdemo_role;
GRANTtemporary_usersTOtest_user;

切换ROLE

SETROLErole_name;--切换到role_name用户
RESETROLE;--切换回最初的role

INHERIT权限：该属性使组成员拥有组的所有权限

ALTERROLEtest_userINHERIT;

删除用户和组

删除用户和组很简单:

DROPROLErole_name;
DROPROLEIFEXISTSrole_name;

删除组role只会删除组的role本身，组的成员并不会被删除。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。