Postgresql安装、初始化及用户, 角色与权限管理

du: 可以查看所有已存在的用户/角色, 以及它们拥有的权限属性\l: 可以查看所有数据库及其属主和所有拥有权限的用户配置选择一个数据库后,\dt可以查看所有表的属主和相应用户权限select * from information_schema.role_table_grants where grantee = '用户名';可以查看指定用户拥有的所有数据库权限。

wang11876

5451人浏览 · 2023-06-21 09:12:18

wang11876 · 2023-06-21 09:12:18 发布

Postgresql-yum安装及初始化

与mysql一样, 使用yum安装的pg也有客户端与服务端两个包, 分别是postgresql与postgresql-server.

初次安装, 需要进行初始化.

切换到postgres用户, 其home目录默认在/var/lib/pgsql. 执行initdb.

$ initdb -D data

-D参数指定数据库文件存放路径, 默认在/var/lib/pgsql/data. 这一步是必须的.

然后启动postgresql服务.

Success. You can now start the database server using:

    postgres -D /var/lib/pgsql/data
or
    pg_ctl -D /var/lib/pgsql/data -l logfile start

当然, yum装的最好通过service或systemctl命令启动.

start子命令是一个前端进程, 日志会在终端直接输出, 你需要使用-l指定一个日志文件, 就可以以服务的形式运行postgres了.

配置

默认pg只允许本机访问, 如果需要打开外网监听, 需要修改pg_hba.conf, 通过yum安装的pg, 这个文件在/var/lib/pgsql/data目录下.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "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
host    all             all             0.0.0.0/0                 md5

本机信任(trust), 其他所有机器可以MD5验证连接, 其实就是普通用户名密码形式.

哦, 还有, postgresql.conf的listen_addresses默认是localhost, 记得改成’*'表示监听所有连接.

pg_hba.conf修改后, 使用pg_ctl reload重新读取pg_hba.conf文件, 如果pg_ctl找不到数据库, 则用-D /.../pgsql/data/指定数据库目录, 或export PGDATA=/.../pgsql/data/导入环境变量.

Postgresql源码安装及初始化

参考文章

Linux CentOS 7源码编译安装PostgreSQL9.5

下载源码包, 解压.

安装依赖包

$ yum install gcc readline-devel zlib-devel

配置选项

$ ./configure --prefix=/opt/pgsql9.5.9

编译安装

$ make && make install

编译安装成功后, 接下来要做的就是创建一个普通用户, 因为默认超级用户（root）不能启动postgresql, 所以需要创建一个普通用户来启动数据库, 执行以下命令创建用户

$ useradd postgres

我们要用这个用户初始化数据目录, 配置文件等. 首先创建环境变量配置, 初始化数据目录要用的.

## 这个是源码安装pg的目标目录, 即configure的`--prefix`参数
export PGHOME=/opt/pgsql9.5.9
## 数据, 配置文件存储目录
export PGDATA=/opt/pgdata
export PGHOST=$PGDATA
export LANG=en_US.utf8
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH

环境变量生效后就可以执行initdb命令了, 初始化完成后会提示启动命令的. 一般执行pg_ctl start就可以了. 不过暂时先不要启动, 有点配置还需要修改.

/opt/pgdata/postgres.conf文件

listen_addresses默认是localhost, 记得改成’*'表示监听所有连接.

port默认为5432, 可自定义.

unix_socket_directory或unix_socket_directories这个是定义本地连接.sock文件的路径的, 默认是/tmp, 源码安装时需要将其修改成’.', 否则psql连接时会报如下错误.

$ psql 
psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/opt/pgdata/.s.PGSQL.5432"?

然后是/opt/pgdata/hba.conf, 这是身份验证的配置文件, 默认只允许本地连接, 如下.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "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

要开启远程连接的话需要添加一行

host    all             all             0.0.0.0/0                 md5

其中trust表示直连不需要密码, md5则是正常的用户名密码的连接方式.

Postgresql-用户, 角色与权限管理

pg通过用户与角色两个概念完成权限控制. 角色相当于用户组的概念. 比如我们可以设置admin与user2个角色, 用户有a, b, c 3个人. 其中a是admin, b与c只是普通的user, 这样就实现了部分的权限控制.

1. 用户与角色基本操作

在psql命令行中可以使用如下命令.

create user 用户名;    ## 创建用户
drop user 用户名;      ## 删除用户

create role 角色名;    ## 创建角色
drop role 角色名;      ## 删除角色

对应的, pg在shell命令行里提供了能实现相同功能的createuser与dropuser命令, 很可惜, 没有createrole与droprole命令.

操作多个角色/用户时, 用逗号隔开

我们可以先创建一个角色A和一个用户a, 然后把A角色赋给a, 于是a就可以拥有A所表示的权限.

grant 角色名 to 用户名;       ## 为用户添加角色权限
revoke 角色名 from 用户名;    ## 从用户中移除指定角色所表示的权限.

postgres=# create role role_general;
CREATE ROLE
postgres=# create user user_general;            ## mmp, 创建用户结果也显示创建的是角色
CREATE ROLE
postgres=# grant role_general to user_general;
GRANT ROLE

在psql命令行中使用\du快捷命令可以查看当前数据库存在的所有角色. 默认只有一个作用Superuser的postgres角色.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 role_general | Cannot login                                   | {}
 user_general |                                                | {role_general}

…role_general与user_general都在??? 0.0

因为role与user本质上是同一种对象, 唯一的区别是, 角色对象role没有登录权限. 不信你把一个用户a当成一个角色赋给另一个用户b?

另外, role与user是多对多关系, 一个用户可以拥有多个角色.

2. 角色属性

假设我们创建了普通用户角色test_user, 我们需要为这个角色赋予一些指定权限. 这种权限分为两种.

一种是类似于登录, 增删角色/用户/数据库这种的系统级别权限.

一种是针对普通数据库的对象的操作权限, 如增删表, 只允许查询, 不允许删除这种粒度的权限.

2.1 系统级属性

第一种权限可用列表, 可用\h create user;命令查询, 如下

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
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    ...省略的应该不算了, 剩下的是指定sysid和密码的

赋权操作包括在创建角色时指定, 也可以在后期追加或修改.

2.1.1 创建时指定

CREATE ROLE 角色名 WITH 可选权限;
CREATE USER 用户名 WITH 可选权限;

示例

postgres=# create user user_1 with superuser password '123456';
CREATE ROLE

2.1.2 后期修改

alter role 角色名/用户名 with 目标权限属性列表

目标权限列表不同属性用空格分隔.

示例

alter role user_1 with nologin createdb;
ALTER ROLE

需要注意的是, 这些属性是成对存在的, 如果一个角色/用户拥有了superuser属性, 当你想要移除它时, 就需要alter...with nosuperuser. 否则, 目标权限属性列表中的值总会与原有值合并. 如下

postgres=# \du
                                   List of roles
  Role name   |                   Attributes                   |     Member of      
--------------+------------------------------------------------+--------------------
 postgres     | Superuser, Create role, Create DB, Replication | {}
 user_1       | Superuser, Create DB, Cannot login             | {}

superuser属性在alter语句执行后一直存在.

2.2 数据级属性

简单来说, 就是指定用户到某数据库的完全控制, 只读, 只写等权限. 同样是用grant语句, 其语法为

GRANT   权限类型 ON [database 库名 | table 表名] TO   角色名/用户名;
REVOKE  权限类型 ON [database 库名 | table 表名] FROM 角色名/用户名;

\h grant可以查看所有可用的权限类型.

示例:

postgres=# create database db_1;
CREATE DATABASE
postgres=# create user user_1 password '123456';
CREATE ROLE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 db_1      | 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)

赋予用户user_1操作库db_1的所有权限, 可以通过\l的Access privileges字段确认.

postgres=# grant all on database db_1 to user_1;
GRANT

总结

\du: 可以查看所有已存在的用户/角色, 以及它们拥有的权限属性

\l: 可以查看所有数据库及其属主和所有拥有权限的用户配置

选择一个数据库后, \dt可以查看所有表的属主和相应用户权限

select * from information_schema.role_table_grants where grantee = '用户名'; 可以查看指定用户拥有的所有数据库权限

开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

开放原子开发者工作坊

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

开放原子开发者工作坊

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

开放原子开发者工作坊

所有评论(0)

查看更多评论

wang11876

@wang11876

已为社区贡献3条内容