postgres=#

— 查看所有角色
\du
List of roles
Role name | Attributes | Member of
———–+————————————————————+———–
pgsql | Superfgedu, Create role, Create DB, Replication, Bypass RLS | {}

— 查看特定角色的属性
\du pgsql List of roles
Role name | Attributes | Member of
———–+————————————————————+———–
pgsql | Superfgedu, Create role, Create DB, Replication, Bypass RLS | {}

初始的postgres角色是超级用户，拥有所有权限，包括创建数据库、创建角色、复制数据等。学习交流加群风哥微信: itpux-com

3.2 PostgreSQL角色管理基础

PostgreSQL提供了创建、修改和删除角色的命令，用于角色管理。

CREATE ROLE

— 创建可创建数据库的角色
CREATE ROLE fgedu_dev WITH LOGIN PASSWORD ‘fgedu@456’ NOSUPERUSER CREATEDB NOCREATEROLE INHERIT NOREPLICATION;

CREATE ROLE

— 修改角色
ALTER ROLE fgedu_read WITH CONNECTION LIMIT 20;

ALTER ROLE

— 删除角色
— DROP ROLE IF EXISTS fgedu_read;

— 查看所有角色
\du
List of roles
Role name | Attributes | Member of
———–+————————————————————+———–
fgedu_dev | Create DB | {}
fgedu_read | | {}
pgsql | Superfgedu, Create role, Create DB, Replication, Bypass RLS | {}

CREATE DATABASE

— 连接到测试数据库
\c fgedudb
You are now connected to fgedudb “fgedudb” as fgedu “postgres”.

— 创建测试表
CREATE TABLE fgedu_products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price NUMERIC(10, 2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE fgedu_– 插入测试数据
INSERT INTO fgedu_products (product_name, price)
VALUES (‘PostgreSQL教程’, 99.99),
(‘Python编程’, 89.99),
(‘Java开发’, 109.99);

INSERT 0 3

— 授予SELECT权限
GRANT SELECT ON fgedu_products TO fgedu_read;

GRANT

— 授予INSERT、UPDATE、DELETE权限
GRANT INSERT, UPDATE, DELETE ON fgedu_products TO fgedu_dev;

GRANT

— 授予表的所有权限
— GRANT ALL PRIVILEGES ON fgedu_products TO fgedu_dev;

— 查看表的权限
\dp fgedu_products
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
——–+————–+——-+—————————+——————-+———-
public | fgedu_products | table | postgres=arwdDxt/postgres+| |
| | | fgedu_read=r/pgsql +| |
| | | fgedu_dev=awd/pgsql | |
(1 row)

CREATE ROLE

— 创建应用读写角色
CREATE ROLE fgedu_fgfgapp_rw WITH LOGIN PASSWORD ‘fgedu@fgapprw’ NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION;

CREATE ROLE

— 创建应用只读角色
CREATE ROLE fgedu_fgfgapp_ro WITH LOGIN PASSWORD ‘fgedu@fgappro’ NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION;

CREATE ROLE

— 查看所有角色
\du
List of roles
Role name | Attributes | Member of
————–+————————————————————+———–
fgedu_admin | Create DB, Create role | {}
fgedu_fgfgapp_ro | | {}
fgedu_fgfgapp_rw | | {}
fgedu_dev | Create DB | {}
fgedu_read | | {}
pgsql | Superfgedu, Create role, Create DB, Replication, Bypass RLS | {}

GRANT

— 授予模式使用权限
GRANT USAGE ON SCHEMA public TO fgedu_fgfgapp_rw, fgedu_fgfgapp_ro;

GRANT

— 授予表的读写权限给应用读写角色
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_products TO fgedu_fgfgapp_rw;

GRANT

— 授予表的只读权限给应用只读角色
GRANT SELECT ON fgedu_products TO fgedu_fgfgapp_ro;

GRANT

— 授予序列权限（如果表使用了SERIAL类型）
GRANT USAGE, SELECT ON SEQUENCE fgedu_products_product_id_seq TO fgedu_fgfgapp_rw;

GRANT

— 测试应用只读角色的权限
\c fgedudb fgedu_fgfgapp_ro
You are now connected to fgedudb “fgedudb” as fgedu “fgedu_fgfgapp_ro”.

fgedudb=> SELECT * FROM fgedu_products;
product_id | product_name | price | created_at
————+————–+——-+—————————–+08
1 | PostgreSQL教程 | 99.99 | 2026-04-02 11:15:30.123456+08
2 | Python编程 | 89.99 | 2026-04-02 11:15:30.123456+08
3 | Java开发 | 109.99 | 2026-04-02 11:15:30.123456+08
(3 rows)

— 尝试插入数据（应该失败）
fgedudb=> INSERT INTO fgedu_products (product_name, price) VALUES (‘Linux运维’, 119.99);
ERROR: permission denied for table fgedu_products

— 切换回超级用户
\c fgedudb pgsql You are now connected to fgedudb “fgedudb” as fgedu “postgres”.

REVOKE

— 查看表的权限
\dp fgedu_products
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
——–+————–+——-+—————————+——————-+———-
public | fgedu_products | table | postgres=arwdDxt/postgres+| |
| | | fgedu_read=r/pgsql +| |
| | | fgedu_dev=aw/pgsql +| |
| | | fgedu_fgfgapp_rw=arwd/postgres+| |
| | | fgedu_fgfgapp_ro=r/pgsql | |
(1 row)

— 测试fgedu_dev角色的权限
\c fgedudb fgedu_dev
You are now connected to fgedudb “fgedudb” as fgedu “fgedu_dev”.

— 尝试删除数据（应该失败）
fgedudb=> DELETE FROM fgedu_products WHERE product_id = 1;
ERROR: permission denied for table fgedu_products

— 切换回超级用户
\c fgedudb pgsql You are now connected to fgedudb “fgedudb” as fgedu “postgres”.