如何查看 PostgreSQL 默认权限
当我们对Postgresql的某个用户授予默认权限时， pg_default_acl表存储要被分配给新创建对象的初始权限。你可能通过查询该表获取默认权限，先了解下官方文档的解释：
表 50-17. pg_default_acl的列：
| oid | oid | 行标识符（隐藏属性，必须被显式选择才会显示） | |
| defaclrole | oid | pg_authid.oid | 与此项相关的角色的OID |
| defaclnamespace | oid | pg_namespace.oid | 与此项相关的名字空间的OID，如果没有则为0 |
| defaclobjtype | char | 此项适合的对象类型： r = 关系（表、视图）， S = 序列， f = 函数， T = 类型 | |
| defaclacl | aclitem[] | 此类对象在创建时应用有的访问权限 |
一个pg_default_acl项展示了要分配给属于一个指定用户的对象的初始权限。当前有两类项：defaclnamespace = 0的"全局"项和引用一个特殊模式的"每模式"项。如果一个全局项存在，则它重载该对象类型的普通hard-wired默认权限。一个每模式项如果存在，表示权限将被加入到全局或hard-wired默认权限中。
添加默认权限
演示数据库版本：PostgreSQL 9.6.8
这里我们测试给test用户添加[abc]模式的默认权限，先查看下当前schema的所有者：
|
1 2 3 4 5 6 |
// 查看当前 schema 的所有者，相当于\du元命令 SELECT n.nspname AS "Name" , pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner" FROM pg_catalog.pg_namespace n WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ORDER BY 1; |
查询结果如图所示，模式[abc]的所有者为postgresql用户
针对模式[abc]， 使用超级管理员postgresql给普通用户test授权，命令如下：
|
1 2 3 4 |
// 最后一条命令就是授予初始权限 grant select on all tables in schema abc to test; grant usage on schema abc to test; alter default privileges in schema abc grant select on tables to test; |
查看默认权限
授权完成，通过pg_default_acl表查看默认权限
|
1 2 |
// 查看初始权限 select * from pg_catalog.pg_default_acl; |
这样看其实不是很直观，可以使用下面的SQL来查看：
|
1 2 3 4 5 6 7 8 |
// 查看初始权限 SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS "Granter" , n.nspname AS "Schema" , CASE d.defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'function' WHEN 'T' THEN 'type' END AS "Type" , pg_catalog.array_to_string(d.defaclacl, E ', ' ) AS "Access privileges" FROM pg_catalog.pg_default_acl d LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace ORDER BY 1, 2, 3; |
输出结果如下： 表示通过Postgresql超级用户授予test普通用户对模式[abc]有读的权限。
在此，我们再把模式[abc]的拥有者(owner)修改为dbadmin用户（可以事先创建好），执行以下命令：
|
1 2 3 4 5 6 7 8 |
// 修改模式[abc]拥有者为：dbadmin ALTER SCHEMA abc OWNER TO "dbadmin" ; // 查看模式的拥有者，相当于\du元命令 SELECT n.nspname AS "Name" , pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner" FROM pg_catalog.pg_namespace n WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ORDER BY 1; |
现在我们使用dbadmin用户登录连接到数据库，给普通用户test添加默认权限，其实上面已经通过postgresql超级用户给test用户加过默认权限了。 这里演示使用dbadmin用户再添加一次，目的是演示pg_default_acl表会出现2条记录，使用dbadmin用户执行以下命令：
|
1 |
alter default privileges in schema abc grant select on tables to test; |
再次查看pg_default_acl表会出现两行记录：
一行是dbadmin拥有者授予的读权限。 一行是postgresql超级用户授权的读权限。
补充：PostgreSQL基本配置与权限控制
修改PgSQL基本配置：
重要文件和目录：
|
1 2 3 4 5 |
配置文件: postgresql.conf 登录权限控制文件: pg_hba.conf 数 据 目 录 ： base 运行日志目录： log 归档日志目录： pg_wal |
修改PgSQL侦听地址：
在单引号内写入IP地址，如果要侦听多个IP地址，以逗号分隔
|
1 2 |
[root@postgrssql ~]# vim /var/lib/pgsql/11/data/postgresql.conf listen_addresses = 'localhost,172.18.1.76' |
修改PgSQL侦听端口：
默认端口 5432
|
1 2 |
[root@postgrssql ~]# vim /var/lib/pgsql/11/data/postgresql.conf port = 5432 |
修改PgSQL最大连接数：
修改连接数需要重启，对比MySQL一般设置 512 ~ 1024
|
1 2 |
[root@postgrssql ~]# vim /var/lib/pgsql/11/data/postgresql.conf max_connections = 100 |
打开PgSQL复制日志：
类似于MySQL binlog，复制到从库并应用一遍日志操作就达到数据同步了
|
1 2 3 4 5 6 7 8 |
# 是否开启 archive_log日志功能 archive_mode = on # 归档 archive_log日志要执行的命令，当前不准备归档就执行 cd命令 archive_command = 'cd .' [root@postgrssql ~]# ll -sh /var/lib/pgsql/11/data/pg_wal total 16M M -rw ------- 1 postgres postgres 16M Nov 23 20:47 000000010000000000000001 0 drwx ------ 2 postgres postgres 6 Nov 22 15:21 archive_status |
PgSQL用户权限控制：
在数据库中创建对象时，都会为其分配所有者。 所有者通常是执行创建语句的用户。 对于大多数类型的对象，初始状态是只有所有者(或超级用户)可以修改或删除对象。 要允许其他角色或用户使用它，必须授予权限或权限。
配置超管用户安全：
默认是 all 所有用户都拥有peer对等权限，只修改为postgrs用户拥有peer权限
|
1 2 |
[root@postgrssql ~]# sed -i '80d' /var/lib/pgsql/11/data/pg_hba.conf [root@postgrssql ~]# sed -i '79a local all postgres peer' /var/lib/pgsql/11/data/pg_hba.conf |
创建用户：
|
1 2 3 4 5 6 7 8 9 10 |
# 创建 olda 用户 postgres=# create user olda with password '123456' ; CREATE ROLE # 查看所有用户 postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- olda | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
用户授权权限：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# 切换到 course 数据库 postgres=# \c course You are now connected to database "course" as user "postgres" . # 在 course 数据库授权所有表的所有权限到 olda用户 course=# grant all on all tables in schema public to olda; GRANT # 查看 course 数据库所有表的权限表信息 course=# \dp+ Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+----------+-------+---------------------------+-------------------+---------- public | students | table | postgres=arwdDxt/postgres+| | | | | olda=arwdDxt/postgres | | (1 row) |
配置用户连接：
在PgSQL内创建用户后也是不可以登录的，需要在pg_hba.conf文件中配置连接源信息，否则不允许远程登录。
加入 olda用户，local本地可以登录所有库，登录密码验证方式为md5
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[root@postgrssql ~]# vim /var/lib/pgsql/11/data/pg_hba.conf # "local" is for Unix domain socket connections only local all postgres peer local all olda 0.0.0.0/0 md5 # 重载 PostgreSQL systemctl reload postgresql-11.service # 测试 olda用户连接 PgSQL的 course库 -bash-4.2$ psql -U olda -W course Password : psql (11.6) Type "help" for help. course=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- course | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows ) |
配置默认权限：
意思为 postgres用户创建的所有表都默认对 olda用户重新授予权限
|
1 2 |
postgres=# alter default privileges for role postgres grant all on tables to olda; ALTER DEFAULT PRIVILEGES |
配置用户远程连接：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
默认在pg_hba.conf文件中加入密码验证方式还是不可以远程访问，因为是 local 方式。 加入 olda用户，host方式，所有来源IP都可以登录所有库，登录密码验证方式为md5 -bash-4.2$ psql -U olda -W -h 172.18.1.76 -p 5432 course Password : psql: FATAL: no pg_hba.conf entry for host "172.18.1.76" , user "olda" , database "course" , SSL off [root@postgrssql ~]# vim /var/lib/pgsql/11/data/pg_hba.conf # "local" is for Unix domain socket connections only local all postgres peer local all olda md5 host all olda 0.0.0.0/0 md5 # 重载 PostgreSQL [root@postgrssql ~]# systemctl reload postgresql-11.service # 测试是否可以远程连接到 PostgreSQL -bash-4.2$ psql -U olda -W -h 172.18.1.76 -p 5432 course Password : psql (11.6) Type "help" for help. course=> |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://modestchen.blog.csdn.net/article/details/86571928
查看更多关于PostgreSQL 默认权限查看方式的详细内容...