首页

用户管理

— 数据库原理及应用实验 —

Haifeng Xu

(hfxu@yzu.edu.cn)

References:

创建关系

使用用户 haife 连接数据库 sowya.

PS C:\Users\haife> psql  -d sowya
用户 haife 的口令：

psql (17.6)
输入 "help" 来获取帮助信息.

sowya=>

创建关系 FiniteSumFormula(item varchar(50), var varchar(5), first varchar(10), difference varchar(10), last varchar(10), theSum varchar(100));

CREATE TABLE FiniteSumFormula(
item varchar(50), 
var varchar(5), 
first varchar(10), 
difference varchar(10), 
last varchar(10), 
theSum varchar(100)
);

sowya=> \d
没有找到任何关系.
sowya=> CREATE TABLE FiniteSumFormula(
sowya(> item varchar(50),
sowya(> var varchar(5),
sowya(> first varchar(10),
sowya(> difference varchar(10),
sowya(> last varchar(10),
sowya(> theSum varchar(100)
sowya(> );
错误:  对模式 public 权限不够
第1行CREATE TABLE FiniteSumFormula(
                  ^

使用超级用户 postgres 登录, 并将权限赋予普通用户 haife

查看当前所有用户. 发现 haife 没有建立数据库的权限.

postgres=# \du+ haife
        角色列表
 角色名称 | 属性 | 描述
----------+------+------
 haife    |      |

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA public TO haife;
GRANT

这仅是赋予表的权限给 haife. 事实上是 sowya 这个数据库不归 haife 所有, 因此其无法在其中创建表等对象.

postgres=# \l
                                                    数据库列表
   名称    |  拥有者  | 字元编码 | Locale Provider | 校对规则 | Ctype | Locale | ICU Rules |       存取权限
-----------+----------+----------+-----------------+----------+-------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | zh-CN    | zh-CN |        |           |
 sowya     | postgres | UTF8     | libc            | zh-CN    | zh-CN |        |           | =Tc/postgres         +
           |          |          |                 |          |       |        |           | postgres=CTc/postgres+
           |          |          |                 |          |       |        |           | haife=CTc/postgres
 template0 | postgres | UTF8     | libc            | zh-CN    | zh-CN |        |           | =c/postgres          +
           |          |          |                 |          |       |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | zh-CN    | zh-CN |        |           | =c/postgres          +
           |          |          |                 |          |       |        |           | postgres=CTc/postgres
(4 行记录)

记得之前在创建 sowya 这个数据库时并未指定 OWNER, 因此默认归创建者 postgres 所有. 现在将其归属于 haife.

postgres=# ALTER DATABASE sowya OWNER TO haife;
ALTER DATABASE

退出, 改用 haife 登入数据库sowya. 现在可以创建表了.

sowya=> CREATE TABLE FiniteSumFormula(
sowya(> item varchar(50),
sowya(> var varchar(5),
sowya(> first varchar(10),
sowya(> difference varchar(10),
sowya(> last varchar(10),
sowya(> theSum varchar(100)
sowya(> );
CREATE TABLE

查看表(关系)的结构

\d 命令后面跟关系名, 则会显示该关系的结构. (MySQL 中使用的是 DESC tablename).

mydb=> \d finitesumformula
                数据表 "public.finitesumformula"
    栏位    |          类型          | 校对规则 | 可空的 | 预设
------------+------------------------+----------+--------+------
 item       | character varying(50)  |          |        |
 var        | character varying(5)   |          |        |
 first      | character varying(10)  |          |        |
 difference | character varying(10)  |          |        |
 last       | character varying(10)  |          |        |
 thesum     | character varying(100) |          |        |

插入数据

我们插入如下数据,

sowya=> INSERT INTO FiniteSumFormula VALUES
sowya-> ('1/(i*(i+1))', 'i','1','1','n','n/(n+1)');
INSERT 0 1

这里通项(item)为 $\frac{1}{i\cdot(i+1)}$, 其中 $i$ 为变量(var), 首项(first)为 $1$, 公差(difference)为 $1$, 最后一项为 $n$, 和(theSum)是 $1-\frac{1}{n+1}$ 或 $\frac{n}{n+1}$.

也就是表示了恒等式 \[ \frac{1}{1\cdot 2}+\frac{1}{2\cdot 3}+\cdots+\frac{1}{n\cdot(n+1)}=1-\frac{1}{n+1}. \]

用 SELECT 语句查看一下.

sowya=> SELECT * FROM Finitesumformula;
    item     | var | first | difference | last | thesum
-------------+-----+-------+------------+------+---------
 1/(i*(i+1)) | i   | 1     | 1          | n    | n/(n+1)
(1 行记录)

练习

将下面的恒等式插入到表 FiniteSumFormula 中.

\[ 1^2+2^2+\cdots+n^2=\frac{1}{6}n(n+1)(2n+1). \]

用户角色

createdb

使用当前用户 haife 创建数据库 mydb.

PS C:\Users\haife> createdb mydb
口令:

PS C:\Users\haife>  数据库失败: 閿欒:  鍒涘缓鏁版嵁搴撴潈闄愪笉澶?

发现创建失败. 这里出现乱码一般是由编码不一致导致的. 下面的网站可以将其恢复为 "错误: 创建数据库权限不?" 网站 http://www.mytju.com/classCode/tools/messyCodeRecover.asp

可见, 目前账户 haife 没有权限创建数据库. 下面用超级用户 postgres 登录 PostgreSQL, 并授权给 haife.

postgres=# ALTER USER haife CREATEDB;
ALTER ROLE

退出, 然后 haife 就可以创建数据库了.

PS C:\Users\haife> createdb mydb
口令:

PS C:\Users\haife> psql -d mydb
用户 haife 的口令：

psql (17.6)
输入 "help" 来获取帮助信息.

mydb=>

或者在一开始创建用户时就给予 CREATEROLE 和 CREATEDB 的权限.

CREATE ROLE user_name PASSWORD 'Your_Password' NOSUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

NOSUPERUSER - the user being created does not have superuser rights (like the postgres user, recommended).
CREATEDB - the user being created can create databases which it will own.
CREATEROLE - the user being created can create roles (logins) for objects it owns or has specific access to (like databases it has created).
INHERIT - the user being created inherits some default options, optional.
LOGIN - the user being created has the right to log in.

若对创建的角色设置权限到期日, 则可加上 UNTIL 子句, 例如到期日为 2025年12月31日.

CREATE ROLE user_name LOGIN PASSWORD 'your_passwd' CREATEDB VALID UNTIL '2025-12-31 00:00';

如果创建的角色是超级用户, 并且权限永不过期, 则使用 UNTIL 'infinity'.

CREATE ROLE user_name LOGIN PASSWORD 'your_passwd' SUPERUSER VALID UNTIL 'infinity';

UNTIL 子句是可选的, 如果不写, 则默认是永不过期.

组角色

如果有很多登录用户, 需要批量给其中某部分用户以指定的权限, 则可以将这部分用户放到一个组中. 组角色的作用就是将一组权限汇集成一个集合以便于将这组权限批量授予别的普通角色. 组角色可以看成一个集合, 从属于该集合的普通角色成为成员角色.

使用下面的语句创建组角色.

CREATE ROLE gp_name INHERIT;

这表示组角色 gp_name 的任何一个成员角色都将自动继承其除“超级用户权限”之外的所有权限.

常见错误

haifeng@LAPTOP-Q34L5TP8:~> su postgres
Password:
postgres@LAPTOP-Q34L5TP8:/home/haifeng> psql
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

这表明 postgresql 服务并没有启动. 使用下面的命令启动postgresql服务.

sudo systemctl enable postgresql

postgres@LAPTOP-Q34L5TP8:/home/haifeng> exit
exit
haifeng@LAPTOP-Q34L5TP8:~> sudo systemctl enable postgresql
[sudo] password for root:
Created symlink '/etc/systemd/system/multi-user.target.wants/postgresql.service' → '/usr/lib/systemd/system/postgresql.service'.
haifeng@LAPTOP-Q34L5TP8:~>

sudo systemctl start postgresql.service

查看服务的状态

sudo systemctl status postgresql.service

Linux 下敲命令要三思. 权力越大责任越大.

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

For security reasons, the password you type will not be visible.

service postgresql start 这样的命令已被弃用, 并将在未来版本中删除.

haifeng@LAPTOP-Q34L5TP8:~> sudo service postgresql start
[sudo] password for root:
The service command and the rc* symlinks have been deprecated and
will be removed in a future release.
Please use "systemctl start postgresql.service" the next time.

haifeng@LAPTOP-Q34L5TP8:~> sudo systemctl start postgresql.service
haifeng@LAPTOP-Q34L5TP8:~> psql
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "haifeng" does not exist
haifeng@LAPTOP-Q34L5TP8:~> su postgres
Password:
postgres@LAPTOP-Q34L5TP8:/home/haifeng> psql
psql (17.6)
Type "help" for help.

postgres=# \q

注意 postgres 用户一般无法执行下面的命令.

postgres@LAPTOP-Q34L5TP8:/home/haifeng> systemctl stop postgresql.service
Failed to add a watch for /run/user/1000/systemd/ask-password: No such file or directory
Failed to stop postgresql.service: Interactive authentication required.
See system logs and 'systemctl status postgresql.service' for details.

postgres@LAPTOP-Q34L5TP8:~> createdb mydb -O haifeng
createdb: error: database creation failed: ERROR:  role "haifeng" does not exist
postgres@LAPTOP-Q34L5TP8:~> createuser haifeng
postgres@LAPTOP-Q34L5TP8:~> createdb mydb -O haifeng

权限

权限的缩写

在使用 \dn+ 显示 schema 时会看到用户的权限. 其中有一些字符, 如 CTc 等其实是权限的缩写.

权限	缩写	适用对象类型
SELECT	r("read读")	LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column
INSERT	a("append增")	TABLE, table column
UPDATE	w("write写")	LARGE OBJECT, SEQUENCE, TABLE, table column
DELETE	d("delete删")	TABLE
TRUNCATE	D("清空")	TABLE
REFERENCES	x("引用")	TABLE, table column
TRIGGER	t("触发器")	TABLE
CREATE	C("创建")	DATABASE, SCHEMA, TABLESPACE
CONNECT	c("connect连接")	DATABASE
TEMPORARY	T	DATABASE
EXECUTE	X	FUNCTION, PROCEDURE
USAGE	U	DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE

End

Thanks very much!

首页

Haifeng Xu

(hfxu@yzu.edu.cn)

目录

创建关系

查看表(关系)的结构

插入数据

练习

用户角色

createdb

组角色

常见错误

常见错误

权限

权限的缩写

End