Shulou(Shulou.com)06/01 Report--

Postgresql user Management:

Default user:

After the postgres installation is complete, a user named postgres and a database also named postgres are automatically created in the operating system and postgres database.

1. Group roles:

A group role can be thought of as a group of database users. Group roles can have database objects, such as tables, and can assign permissions on those objects to other roles to control who has access to which objects.

-- create a role:

Create role role_emp

-- View the roles in the system, such as:

Select rolname from pg_roles

-- modify the syntax of the group role name:

Alter role group role name rename to new group role name

-- Delete group roles

Name of the drop role group role

2. Various permissions of the role

①: login permissions: login

-- create a group role with login permissions, such as:

Create role zhang login

②: superuser (superuser)

The superuser superuser has the highest permissions on the database and can complete all permission checks on the database.

-- create a database superuser, such as:

Create role zhang superuser

Note: only superusers have permission to create superusers.

③: createdb creates a database

-- create a group role with permissions to create a database, such as

Create role zhang createdb

④: createrole creates roles

-- create roles with permission to create roles, such as:

Createrole zhang createrole

⑤: password

Password permission is required when the customer authentication method requires a connection to the database. Common authentication methods include: password,md5,crypt.

-- create roles with password permissions, such as:

Create role zhang password '123456'

3. Account management

Method 1: create using the createuser command on the system command line

Createuser username; such as [pguser@localhost ~] $createuser zhangfeng

Method 2: create using the CREATE ROLE directive on the PostgresSQL command line

CREATE ROLE rolename

Method 3: create using the CREATE USER directive on the PostgresSQL command line

CREATE USER username

Note: the difference between CREATE USER and CREATE ROLE is that users created by the CREATE USER directive have login privileges by default, while CREATE ROLE does not.

Example: create user zhangfeng password 123456, and have permissions to create database and create roles, such as:

Create user zhangfeng password '123456' createdb createrole

-- Delete user

Drop user zhangfeng

-- change the user's password

Alter user zhangfeng password '123456'

3. Group role and user rights management

-Authorization of group roles:

Alter role role name permission 1, permission 2

For example, add permissions to create data tables and roles to the ro_emp role:

Alter role ro_emp createdb createrole

-Authorization to users

Alter user username permissions 1, permissions 2

For example, add permissions to create data tables and create roles for users

Alter user zhangfeng createdb createrole

-- recall group role permissions

Take back the permissions to create data tables and create roles for role_emp roles, such as:

Alter role role_emp nocreatedb nocreaterole

-- revoke user rights

Alter user zhangfeng nocreatedb nocreaterole

4. Database rights management

-- modify the owner of the database

Alter database database name owner to owner name

Such as:

Alter database testdb owner to zhangfeng

-- increase the user's data table permissions

Grant permissions on data sheet to user name

Such as:

Grant update on emp to zhangfeng

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.