pgsql用户和权限管理
1.用户基本操作
1.1创建用户
创建用户命令结构
postgres=# \h create user
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
URL: https://www.postgresql.org/docs/16/sql-createuser.htmlpgsql的用户可以等同于角色
区别：
1.创建用户默认带有登录权限，而角色没有。
创建用户
postgres=# create user test_user1 createdb password '123456';查看创建的用户(这里是角色和用户混合)
postgres=# \du
List of roles
Role name | Attributes
------------+------------------------------------------------------------
hwz |
hwz2 | Cannot login
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
test_user1 | Create DB还有种用系统内置视图查看用户方法（只能看到用户）
postgres=# SELECT * FROM pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
------------+----------+-------------+----------+---------+--------------+----------+----------+-----------
hwz | 16393 | f | f | f | f | ******** | |
postgres | 10 | t | t | t | t | ******** | |
test_user1 | 16395 | t | f | f | f | ******** | |
(3 rows)1.2修改用户
修改用户名字
postgres=# alter user test_user1 rename to test_u1;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
hwz |
hwz2 | Cannot login
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
test_u1 | Create DB添加权限
postgres=# alter user test_u1 superuser;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
hwz |
hwz2 | Cannot login
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
test_u1 | Superuser, Create DB修改密码
postgres=# alter user test_u1 password '123';
ALTER ROLE1.3用户本地登录
注意登录时如果不知道数据库会默认进入和用户名一样的数据库，如果不存在该数据库会报错。
解决办法：1.创建和用户名一样的数据库。2.登录时指定数据库
-d 指定数据库
postgres@hwz-VMware-Virtual-Platform:~$ psql -U lisa1 -d test
Password for user lisa1:
psql (16.6)
Type "help" for help.2.角色
PostgreSQL 使用角色的概念管理数据库访问权限。可以将角色视为数据库用户或一组数据库用户。
创建角色
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
postgres=# create role c_role1;
CREATE ROLE
查看所有角色
postgres=# select rolname from pg_roles;
rolname
-----------------------------
pg_database_owner
pg_read_all_data
pg_write_all_data
pg_monitor
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_read_server_files
pg_write_server_files
pg_execute_server_program
pg_signal_backend
pg_checkpoint
pg_use_reserved_connections
pg_create_subscription
hwz
hwz2
postgres
test_u1
c_role1
(19 rows)
查看能登录的用户
postgres=# select rolname from pg_roles where rolcanlogin;
rolname
----------
hwz
postgres
test_u1
(3 rows)postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
c_role1 | Cannot login
hwz |
hwz2 | Cannot login
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
test_u1 | Superuser, Create DB2.1角色属性
角色属性在客户端连接时验证身份权限。
login:创建具有登录login属性角色，有两种方式
postgres=# create role c_role2 login;
CREATE ROLE
postgres=# create user c_role3;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
c_role1 | Cannot login
c_role2 |
c_role3 |
hwz |
hwz2 | Cannot login
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
test_u1 | Superuser, Create DBsuperuser:超级用户，数据库超级用户绕过所有权限检查，但登录权限除外。这是一项危险的特权，不应随意使用;
postgres=# create role c_role6 superuser;
CREATE ROLE
createdb:创建数据库
postgres=# create role c_role5 createdb;
CREATE ROLE
createrole: 创建角色
postgres=# create role c_role4 login createrole;
CREATE ROLE
replication: 流复制权限
postgres=# create role c_role7 replication;
CREATE ROLE
password: 密码权限
postgres=# create role c_role10 login password '123456';
CREATE ROLEintherit: 继承权限，继承除了superuser的其他属性权限
postgres=# create role c_role11 with inherit;
CREATE ROLEbypassrls：必须明确授予角色绕过每个行级安全性 （RLS） 策略的权限（超级用户除外，因为超级用户会绕过所有权限检查）。
connection limit connlimit：限制用户并发数
postgres=# create role lisa1 login connection limit 1 password '123456';
CREATE ROLE超过1会话连接不上
postgres@hwz-VMware-Virtual-Platform:~$ psql -U lisa1 -d test
Password for user lisa1:
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: too many connections for role "lisa1"
2.2 授予权限
上面的角色属性可以看做系统权限
grant：有两种作用
- 用于授予对数据库对象（表、列、视图、外部表、序列、数据库、外部数据包装器、外部服务器、函数、过程、过程、过程语言、大型对象、配置参数、模式、表空间或类型）的权限。
- 用于授予角色中的成员资格
命令语法：
postgres=# \h grant
Command: GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type
[, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { SET | ALTER SYSTEM } [, ... ] | ALL [ PRIVILEGES ] }
ON PARAMETER configuration_parameter [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT role_name [, ...] TO role_specification [, ...]
[ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ]
[ GRANTED BY role_specification ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER2.2.1授予数据库对象权限
官方文档：PostgreSQL: Documentation: 16: 5.7. Privileges
对表对象授权
现在lisa1用户是没有查看test库下的user1表的权限
postgres@hwz-VMware-Virtual-Platform:~$ psql -U lisa1 -d test
Password for user lisa1:
psql (16.6)
Type "help" for help.
test=> \d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | user1 | table | postgres
(1 row)
test=> select * from user1;
ERROR: permission denied for table user1
test=>
想要授权查看test库下的user1表的权限
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# grant select on user1 to lisa1;
GRANT
test=# select * from information_schema.table_privileges where table_name='user1';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
----------+----------+---------------+--------------+------------+----------------+--------------+----------------
postgres | postgres | test | public | user1 | INSERT | YES | NO
postgres | postgres | test | public | user1 | SELECT | YES | YES
postgres | postgres | test | public | user1 | UPDATE | YES | NO
postgres | postgres | test | public | user1 | DELETE | YES | NO
postgres | postgres | test | public | user1 | TRUNCATE | YES | NO
postgres | postgres | test | public | user1 | REFERENCES | YES | NO
postgres | postgres | test | public | user1 | TRIGGER | YES | NO
postgres | lisa1 | test | public | user1 | SELECT | NO | YES
(8 rows)
现在再次使用lisa1查询user1
test=> select * from user1;
id | name
----+------
1 | hwz
2 | www
(2 rows)对数据库对象授权
对模式对象授权
让zhangsan用户可以在test数据库创建表权限需要开放public schema的权限
test=> create table user2(uid int,name varchar(64));
ERROR: permission denied for schema public
LINE 1: create table user2(uid int,name varchar(64));
test=# grant create on schema public to zhangsan;
GRANT
test=> create table user2(uid int,name varchar(64));
CREATE TABLE
对列对象授权
test=# grant select(name) on teacher to zhangsan;
GRANT
test=> select * from teacher;
ERROR: permission denied for table teacher
test=> select name from teacher;
name
------
(0 rows)
2.3 查看权限
查看数据库对象权限
test=> \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
hwz2 | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | |
postgres | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | |
template0 | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
test | postgres | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | | =Tc/postgres +
| | | | | | | | postgres=CTc/postgres+
| | | | | | | | lisa1=C/postgres
testdb1 | zhangsan | UTF8 | libc | zh_CN.UTF-8 | zh_CN.UTF-8 | | | =Tc/zhangsan +
| | | | | | | | zhangsan=CTc/zhangsan+
| | | | | | | | lisa=C/zhangsan
查看表对象权限
test=> select * from information_schema.table_privileges where table_name='student';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+----------+---------------+--------------+------------+----------------+--------------+----------------
lisa1 | zhangsan | test | public | student | INSERT | NO | NO
lisa1 | zhangsan | test | public | student | SELECT | NO | YES
lisa1 | zhangsan | test | public | student | UPDATE | NO | NO
lisa1 | zhangsan | test | public | student | DELETE | NO | NO
lisa1 | zhangsan | test | public | student | TRUNCATE | NO | NO
lisa1 | zhangsan | test | public | student | REFERENCES | NO | NO
lisa1 | zhangsan | test | public | student | TRIGGER | NO | NO
还可以用\dp看表权限,也可以看到列权限
test=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------+---------------------------+------------------------+----------
public | student | table | lisa1=arwdDxt/lisa1 +| |
| | | lisa=arwdDxt/lisa1 +| |
| | | zhangsan=arwdDxt/lisa1 | |
public | teacher | table | | name: +|
| | | | zhangsan=r/lisa |
public | user1 | table | postgres=arwdDxt/postgres+| id: +|
| | | lisa1=a*rw*/postgres +| zhangsan=r/postgres +|
| | | lisa=arwdDxt/postgres | name: +|
| | | | zhangsan=rw/postgres |
public | user2 | table | | |
(4 rows)
格式：被授予人=权限.../授予人
权限说明
| 对象类型 | 所有特权 | 默认 PUBLIC 权限 | psql命令 |
|---|---|---|---|
DATABASE | CTc | Tc | \l |
DOMAIN | U | U | \dD+ |
FUNCTION or PROCEDURE | X | X | \df+ |
FOREIGN DATA WRAPPER | U | none | \dew+ |
FOREIGN SERVER | U | none | \des+ |
LANGUAGE | U | U | \dL+ |
LARGE OBJECT | rw 乌尔曼 | none | \dl+ |
PARAMETER | sA 他 | none | \dconfig+ |
SCHEMA | UC | none | \dn+ |
SEQUENCE | rwU | none | \dp |
TABLE (and table-like objects) | arwdDxt | none | \dp |
| Table column | arwx | none | \dp |
TABLESPACE | C | none | \db+ |
TYPE | U | U | \dT+ |
ACL（Access Control List）权限缩写
对数据库对象授予权限的缩写
| 权限 | 缩写 | 应用对象类型 |
|---|---|---|
SELECT | r (“read”) | LARGE OBJECT, , (and table-like objects), table columnSEQUENCETABLE |
INSERT | a (“append”) | TABLE, table column |
UPDATE | w (“write”) | LARGE OBJECT, , , table columnSEQUENCETABLE |
DELETE | d | TABLE |
TRUNCATE | D | TABLE |
REFERENCES | x | TABLE, table column |
TRIGGER | t | TABLE |
CREATE | C | DATABASE, , SCHEMATABLESPACE |
CONNECT | c | DATABASE |
TEMPORARY | T | DATABASE |
EXECUTE | X | FUNCTION, PROCEDURE |
USAGE | U | DOMAIN, , , , , , FOREIGN DATA WRAPPERFOREIGN SERVERLANGUAGESCHEMASEQUENCETYPE |
SET | s | PARAMETER |
ALTER SYSTEM | A | PARAMETER |
举例子
test=# grant all on user2 to zhangsan;
GRANT
test=# grant select(name) on user2 to zhangsan;
GRANT
test=# grant select on user2 to lisa;
GRANTtest=> \dp user2
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------+-------+---------------------------+-----------------------+----------
public | user2 | table | zhangsan=arwdDxt/zhangsan+| name: +|
| | | lisa=r/zhangsan | zhangsan=r/zhangsan |
(1 row)
3.pg_hba.conf配置文件
官方文档：PostgreSQL：文档：16：21.1。pg_hba.conf 文件
pg_hba.conf配置文件用来控制客户端连接认证的。相当于mysql创建用户时设置的ip白名单。
默认配置是对本地用户不需要认证的
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust1.连接方式
local: 本地socket连接
host: 使用tcp/ip连接
hostssl：使用 TCP/IP 建立的连接尝试匹配，但前提是使用 SSL 加密建立连接。
2.允许数据库
all: 全部数据库
3.允许用户
all: 全部用户
4.允许ip
local不用使用这个字段
ip/掩码：192.168.0.0/24
5.认证方式
trust：无条件允许连接。
scram-sha-256：SCRAM-SHA-256 身份验证以验证用户的密码。
md5：执行 SCRAM-SHA-256 或 MD5 身份验证以验证用户的密码。
4.远程登录
vim /pgdata/16/data/postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;vim /pgdata/16/data/pg_hba.conf
开放客户端地址
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 172.20.0.0/16 md5
host all all 192.168.0.0/16 md5
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust重启服务端
pg_ctl -D /pgdata/16/data/ restart