【数据库】 基础运用和管理

用户的追加/消除/变更

用户的追加

1
2
3
4
5
6
7
$ createuser -P user1
Enter passowrd for new role: # 输入密码
Enter it again: # 再一次输入密码
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
1
2
3
4
5
6
$ psql -U user1 examdb
Password for user user1:
psql (9.0.4)
Type "help" for help.
examdb=>

用户的删除

1
$ dropuser user1

变更用户

1
2
3
4
5
6
7
ALTER USER 用户名 [ [WITH]
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEDB
| LOGIN | NOLOGIN
| PASSWORD '密码'
| VALID UNTIL '日期']
1
ALTER USER 用户名 RENAME TO 新用户名

eg:

1
2
=# ALTER USER user1 WITH PASSWORD 'password';
ALTER ROLE

VACUUM、ANALYZE

VACUUM

VACUUM是在postgresql中用于维护数据库磁盘空间的工具,其作用是删除那些已经标示为删除的数据并释放空间。

1
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]

ANALYZE

analyze的功能是更新统计信息,使得优化器能够选择更好的方案执行sql

1
ANALYZE [ table ]

VACUUM ANALYZE

1
VACUUM ANALYZE [ table ]

自动执行VACUUM

更改postgresql.conf文件

1
autovacuum = on

获取系统相关参数

version()函数

1
2
3
4
5
6
7
8
$ psql
psql (9.0.4)
Type "help" for help.
=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.4 on X86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704(Red Hat 4.1.2-50), 64-bit

current_database()函数

确认当前链接的数据库

1
2
3
4
5
6
examdb=# SELECT current_database();
current_database
-----------------
examdb
(1 row)

current_user 和 user

1
2
3
4
5
6
7
8
9
10
11
=# SELECT current_user;
current_user
-------------
postgres
(1 row)
=# SELECT user;
current_user
--------------
postgres
(1 row)

信息结构视图 & 系统目录

信息结构视图:information_schema

1
2
=# SELECT * FROM information_schema.enabled_roles;
=# SELECT * FROM information_schema.tables;

System Catalog

1
2
3
=# SELECT * FROM pg_roles; # 与 \du 相似
=# SELECT * FROM pg_authid;
=# SELECT * FROM pg_catalog.pg_roles;

其他的系统目录

1
2
3
4
5
6
7
=# \x
Expanded display is on
=# SELECT * FROM pg_settings;
=# SELECT * FROM pg_language;
=# SELECT * FROM pg_database;
=# SELECT * FROM pg_tables;
=# SELECT * FROM pg_tables WHERE schemaname='public';

表格单位的权限管理 GRANT & REVOKE

GRANT

使用 GRANT 对用户赋予权限:

1
2
3
4
GRANT {{SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER}
[, ...] | ALL [PRIVILEGES] }
ON { [TABLE] table_name [, ...] }
TO { user_name | PUBLIC } [, ...]

eg:

1
=> GRANT SELECT ON tab1 TO PUBLIC;
1
2
=> GRANT SELECT, UPDATE ON tab2 TO user3;
GRANT

REVOKE

使用 REVOKE 取消权限:

1
2
3
4
5
REVOKE {{ SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |
TRIGGER } [,...] | ALL [
PRIVILEGES] }
ON { [TABLE] table_name [, ...] }
FROM {user_name | PUBLIC} [, ...]

eg:

1
=> REVOKE ALL ON tab2 FROM user3;

使用 \dp\z 进行表格的权限确认

1
2
3
=> \dp
=> \dp tab1
Yunjie Zhang wechat
扫一扫上面的二维码加我微信