基于PostgreSQL 权限解读

1 public权限解读

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

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

1.1 创建数据库完成后

									--回收schema的public权限

									--revoke all on schema public from public; --正常情况使用这个

									revoke all on schema public,tiger,tiger_data,topology from public; --使用postgis扩展使用这个

									--从core数据库收回public的所有权限

									revoke all on database core from public;

									revoke all on database postgres from public;

									--重新将schema授予数据库所有者

									grant all on schema public,tiger,tiger_data,topology to 数据库所有者;

1.2 创建schema完成后

									create schema history;

									revoke all on schema history from public;

									--重新将schema history授予数据库所有者

									grant all on schema history to 数据库所有者;

2 测试

假设现在有一数据库core.

2.1 postgrese用户连接至core数据库

1	`psql -h localhost -p 5432 -U postgres -d core`

									--创建一个名称sel的用户

									create user sel with nosuperuser nocreatedb nocreaterole noinherit login noreplication nobypassrls password '123';

									--允许连接至数据库core

									grant connect on database core to sel;

									--查看角色使用\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 -- ALL PRIVILEGES （对于表，对其他对象会变化）

									* -- 用于前述特权的授权选项

2.2 在另一个进程中运行

1	`psql -h localhost -p 5432 -U sel -d core`

									drop table aaa;

									create table aaa(

									 objectid integer

									);

									select * from features;

上面的sql报

									ERROR: table "aaa" does not exist

									ERROR: no schema has been selected to create in

									ERROR: relation "features" does not exist

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

1	`psql -h localhost -p 5432 -U postgres -d core`

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

1 2	`grant` `usage` `on` `schema` `public` `to` `sel;` `grant` `select` `on` `all` `tables` `in` `schema` `public` `to` `sel;`

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

1	`psql -h localhost -p 5432 -U sel -d core`

1 2	`select` `*` `from` `features;` `delete` `from` `features;`

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

3 删除用户

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

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

1	`psql -h localhost -p 5432 -U postgres -d core`

									--回收分配的权限并删除用户

									revoke select on all tables in schema public from sel;

									revoke usage on schema public from sel;

									revoke connect on database core from sel;

									drop role sel;

补充：PostgreSQL 用户和权限管理

默认用户

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

方式1:指定参数登录

1	`psql -U username -d database_name -h host -W`

参数含义: -U指定用户 -d要连接的数据库 -h要连接的主机 -W提示输入密码。

方式2:切换到postgres同名用户后登录

1 2	`su username` `psql`

当不指定参数时psql使用操作系统当前用户的用户名作为postgres的登录用户名和要连接的数据库名。所以在PostgreSQL安装完成后可以通过以上方式登录。

创建用户

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

1	`createuser username`

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

1	`CREATE` `ROLE rolename;`

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

1	`CREATE` `USER` `username;`

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

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

创建用户时设定用户属性

基本语法格式

1	`CREATE` `ROLE role_name` `WITH` `optional_permissions;`

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

1	`CREATE` `ROLE username` `WITH` `LOGIN;`

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

修改用户属性

修改权限的命令格式

1	`ALTER` `ROLE username` `WITH` `attribute_options;`

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

1	`ALTER` `ROLE username` `WITH` `NOLOGIN;`

设置访问权限

语法格式如下:

1	`GRANT` `permission_type` `ON` `table_name` `TO` `role_name;`

实例:

1 2	`GRANT` `UPDATE` `ON` `demo` `TO` `demo_role;` `--赋予demo_role demo表的update权限` `GRANT` `SELECT` `ON` `ALL` `TABLES` `IN` `SCHEMA` `PUBLIC` `to` `demo_role;` `--赋予demo_role所有表的SELECT权限`

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

1 2	`GRANT` `ALL` `ON` `demo` `TO` `demo_role;` `--赋给用户所有权限` `GRANT` `SELECT` `ON` `demo` `TO` `PUBLIC;` `--将SELECT权限赋给所有用户`

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

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

撤销用户访问权限

语法格式如下:

1	`REVOKE` `permission_type` `ON` `table_name` `FROM` `user_name;`

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

用户组

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

创建组示例:

									CREATE ROLE temporary_users;

									GRANT temporary_users TO demo_role;

									GRANT temporary_users TO test_user;

切换ROLE

1 2	`SET` `ROLE role_name;` `--切换到role_name用户` `RESET ROLE;` `--切换回最初的role`

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

1	`ALTER` `ROLE test_user INHERIT;`

删除用户和组

删除用户和组很简单:

1 2	`DROP` `ROLE role_name;` `DROP` `ROLE IF EXISTS role_name;`

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

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

原文链接：https://blog.csdn.net/kmblack1/article/details/106411623

基于PostgreSQL 权限解读

相关文章

热门资讯