跳转至

PostgreSQL用户与权限1

我们之前在部署PostgreSQL时，使用的是psql免密直接连接数据库，在此我们来介绍一下如何创建用户即赋予权限

一. 核心概念

在PostgreSQL中，用户和角色在概念上是统一的。。从8.1版本开始，CREATE USER 只是 CREATE ROLE 的一个别名，唯一的区别是CREATE USER默认带有LOGIN权限，而CREATE ROLE默认没有该权限

  • 角色: 是一个实体。可以拥有数据库对象(如表、视图)和被授予权限。角色可以是一个用户，也可以是一个组。
  • 用户: 本质上就是一个具有LOGIN权限的角色。
  • 组角色: 一个可以被其他角色"加入"的角色，用于批量管理权限。现在通常直接用角色进程来实现。

二. 权限层次结构

PostgreSQL的权限是分层的，理解这个层次至关重要:

实例级别(Cluster Level) -> 数据库级别(Database Level) -> 模式级别(Schema Level) -> 对象级别(Table/View/Function etc.)。其中模式是PostgreSQL的特有概念，其他概念和MySQL是一样的

1. 角色/用户管理

创建用户(可登录角色)

CREATE USER username WITH PASSWORD 'your_strong_password';
-- 或者等价的
CREATE ROLE username WITH LOGIN PASSWORD 'your_strong_password';

如:

postgres=# CREATE USER setsuna WITH PASSWORD 'abcd1234';
CREATE ROLE

创建组角色(不可登录角色)

CREATE ROLE group_name NOLOGIN;

如:

postgres=# CREATE ROLE AIO169 NOLOGIN;
CREATE ROLE

修改用户/角色

 -- 修改密码
ALTER USER username WITH PASSWORD 'new_password';
 -- 重命名
ALTER USER old_username RENAME TO new_username;

 -- 给角色添加/删除属性
ALTER ROLE role_name WITH CREATEDB;     -- 授予创建数据库的权限
ALTER ROLE role_name WITH SUPERUSER;    -- 授予超级用于权限(慎用！)
ALTER ROLE role_name WITH NOLOGIN;      -- 禁止登录

除了被授予超级用于权限，其他情况其实都无法被正常登录,因为PostgreSQL连接时候需要显式或隐式指定数据库

  • 显式指定: 通过-d参数(如pgsql -U 用户名 -d 数据库名)
  • 隐式指定: 如果不指定，PostgreSQL会按照以下顺序尝试连接: 与用户名同名的数据库(如用户myuser会先尝试连接myuser库)。若同名库不存在，尝试连接默认的postgres库(安装时创建的管理员库)
bash-4.2$ psql -U username -h127.0.0.1
Password for user username:
psql: error: connection to server at "127.0.0.1",port 5432 failed:
FaTAL: database "username" does not exist

删除用户/角色

DROP ROLE role_name;
-- 或者
DROP USER username;

注意: 如果角色拥有数据库对象，则不能直接删除，需要先转移所有权或使用DROP OWNED BY role_name; 和REASSIGN OWNED BY role_name TO new_owner;。

2. 权限管理

数据库权限

连接到一个数据库(如postgres)后，可以授予其他数据库的权限。

-- 允许角色连接到数据库
GRANT CONNECT ON DATABASE databae_name TO role_name;

-- 允许角色在数据库中创建模式
GRANT CREATE ON DATABASE database_name TO role_name;

postgres=# GRANT CONNECT ON DATABASE klm TO setsuna;
GRANT
postgres=# GRANT CREATE ON DATABASE klm TO setsuna;
GRANT

授予连接(CONNECT)权限后，我们才能登录到psql里面，可以查看到对应库所有表的权限，但是却不能对表进行操作。

[root@5 docs]# docker exec -it my-pg18-learn psql -U setsuna klm
psql (18.4)
Type "help" for help.

klm=> \dt;
          List of tables
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

klm=> SELECT * FROM users;
ERROR:  permission denied for table users
klm=> 
-- 没有给查找的权限

表/视图权限

这是最常用的权限控制

    -- 允许访问public模式(前提)
    GRANT USAGE ON SCHEMA public TO users;

    -- 授予public模式下所有现有表的SELECT权限
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO users;

    -- 授予public模式下所有表的增删改查权限
    GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABKES IN SCHEMA public TO user;

    -- 授予public模式下所有表的所有权限
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

    -- 假设users表在public模式(默认模式)
    GRANT ALL PRIVILEGES ON TABLE public.users TO myuser;