PostgreSQL 默认权限查看方式

如何查看PostgreSQL默认权限 当我们对Postgresql的某个用户授予默认权限时, pg_default_acl表存储要被分配给新创建对象的初始权限。

如何查看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的所有者:

// 查看当前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授权,命令如下:

// 最后一条命令就是授予初始权限
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表查看默认权限

// 查看初始权限
select * from pg_catalog.pg_default_acl;

这样看其实不是很直观,可以使用下面的SQL来查看:

// 查看初始权限
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用户(可以事先创建好),执行以下命令:

// 修改模式“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用户执行以下命令:

alter default privileges in schema abc grant select on tables to test;

再次查看pg_default_acl表会出现两行记录:

一行是dbadmin拥有者授予的读权限。 一行是postgresql超级用户授权的读权限。

补充:PostgreSQL基本配置与权限控制

修改PgSQL基本配置:

重要文件和目录:

配置文件:  postgresql.conf
登录权限控制文件: pg_hba.conf
数 据 目 录 : base
运行日志目录: log
归档日志目录: pg_wal

修改PgSQL侦听地址:

在单引号内写入IP地址,如果要侦听多个IP地址,以逗号分隔

[root@postgrssql ~]# vim /var/lib/pgsql/11/data/postgresql.conf
listen_addresses = 'localhost,172.18.1.76'

修改PgSQL侦听端口:

默认端口 5432

[root@postgrssql ~]# vim /var/lib/pgsql/11/data/postgresql.conf
port = 5432

修改PgSQL最大连接数:

修改连接数需要重启,对比MySQL一般设置 512 ~ 1024

[root@postgrssql ~]# vim /var/lib/pgsql/11/data/postgresql.conf
max_connections = 100

打开PgSQL复制日志:

类似于MySQL binlog,复制到从库并应用一遍日志操作就达到数据同步了

# 是否开启 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权限

[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

创建用户:

# 创建 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 | {}

用户授权权限:

# 切换到 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

[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用户重新授予权限

postgres=# alter default privileges for role postgres grant all on tables to olda;
ALTER DEFAULT PRIVILEGES

配置用户远程连接:

默认在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=>

以上为个人经验,希望能给大家一个参考,也希望大家多多支持好代码网。如有错误或未考虑完全的地方,望不吝赐教。

您可能有感兴趣的文章
PostgreSQL数据库管理系统快速入门

PostgreSQL安装、配置及简单如何使用方法

PostgreSQL入门简介

PostgreSQL与MySQL优劣势比较浅谈

Windows 系统 PostgreSQL 手工安装配置方法