postgresql 创建用户_PostgreSQL创建用户和修改用户密码

pg创建用户可以在OS使用脚本createuser方式或者在psql客户端使用create user username命令两种方式。两种方式都支持使用help查看命令帮助。

在OS层面，user相关的可执行命令如下：

[postgres@akendb01]$cd $PGHOME/bin[postgres@akendb01]$pwd/aken/dbapp/pgsql/1105/bin[postgres@akendb01]$[postgres@akendb01]$ls -rtl *user-rwxr-xr-x 1 root root 181760 Aug 26 21:26 createuser-rwxr-xr-x 1 root root 161328 Aug 26 21:26 dropuser[postgres@akendb01]$

方式一、通过--help查看createuser的帮助信息：

[postgres@akendb01]$createuser --helpcreateuser creates a new PostgreSQL role.Usage: createuser [OPTION]... [ROLENAME]Options: -c, --connection-limit=N connection limit for role (default: no limit) -d, --createdb role can create new databases -D, --no-createdb role cannot create databases (default) -e, --echo show the commands being sent to the server -g, --role=ROLE new role will be a member of this role -i, --inherit role inherits privileges of roles it is a member of (default) -I, --no-inherit role does not inherit privileges -l, --login role can login (default) -L, --no-login role cannot login -P, --pwprompt assign a password to new role -r, --createrole role can create new roles -R, --no-createrole role cannot create roles (default) -s, --superuser role will be superuser -S, --no-superuser role will not be superuser (default) -V, --version output version information, then exit --interactive prompt for missing role name and attributes rather than using defaults --replication role can initiate replication --no-replication role cannot initiate replication -?, --help show this help, then exitConnection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -U, --username=USERNAME user name to connect as (not the one to create) -w, --no-password never prompt for password -W, --password force password promptReport bugs to .[postgres@akendb01]$

方式二、在psql客户端会话，使用h create user查看命令帮助：

(postgres@[local]:5432)[postgres]#h create userCommand: CREATE USERDescription: define a new database roleSyntax:CREATE USER name [ [ WITH ] option [ ... ] ]where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid(postgres@[local]:5432)[postgres]#

当需要该变用户user的某种属性的时候，psql里面也支持h alter user查看alter相关帮助：

(postgres@[local]:5432)[postgres]#h alter userCommand: ALTER USERDescription: change a database roleSyntax:ALTER USER role_specification [ WITH ] option [ ... ]where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL | VALID UNTIL 'timestamp'ALTER USER name RENAME TO new_nameALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENTALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameterALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALLwhere role_specification can be: role_name | CURRENT_USER | SESSION_USER(postgres@[local]:5432)[postgres]#

postgres创建用户user和修改用户密码示例：

(postgres@[local]:5432)[postgres]#create user aken01 password 'aken1234';CREATE ROLE(postgres@[local]:5432)[postgres]#du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- aken | | {} aken01 | | {} aken02 | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}(postgres@[local]:5432)[postgres]#alter user aken01 password 'aken666';ALTER ROLE(postgres@[local]:5432)[postgres]#drop user aken01;DROP ROLE(postgres@[local]:5432)[postgres]# List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- aken | | {} aken02 | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}(postgres@[local]:5432)[postgres]#

---本文完---

欢迎关注头条号查看Aken-甘植恳更多文章：