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源码安装及初始化
参考文章
下载源码包, 解压.
安装依赖包
$ 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 = '用户名'; 可以查看指定用户拥有的所有数据库权限
1030
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
