【postgres】创建用户与数据库

1. 用户及数据库创建

sudo -u postgres psql

# 创建用户
postgres=# create user test with password '123456';
# 创建数据库并指定拥有者
postgres=# create database testdb owner test;
# 给指定数据库的所有权限绑定用户
postgres=# grant all privileges on database testdb to test;
# 查看用户
postgres=# \du
# 查看数据库
postgres=# \l

2. 创建过程

tester@fabu:~$ sudo -u postgres psql
psql (14.1 (Ubuntu 14.1-1.pgdg18.04+1))
Type "help" for help.

postgres=# create user test with password '123456';
CREATE ROLE
postgres=# create database testdb owner test;
CREATE DATABASE
postgres=# grant all privileges on database testdb to test;
GRANT
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test      |                                                            | {}

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 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
 testdb    | test     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/test             +
           |          |          |             |             | test=CTc/test

 3. 删除用户和数据库

postgres=# DROP DATABASE IF EXISTS testdb;
DROP DATABASE
postgres=# DROP USER test;
DROP ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 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
(3 rows)