PostgreSQL user and permissions management

Default user

After the Postgresql installation is complete, it will automatically create a Postgres user named and a Postgres database with the same name in the operating system and PostgreSQL database.

Log in

Method 1: Log in with specified parameters

psql -U username -d database_name -h host -W

Definition: -USpecifies the user -ddatabase to connect the -hhost to connect to -Wthe password prompt.

Method 2: Log in after switching to the postgres user with the same name

su username
psql

When no parameters are specified, psql the user name of the current user of the operating system is used as the login user name of Postgres and the database name to be connected. So after the PostgreSQL installation is complete, you can log in through the above methods.

Create user

Method 1: Use create user command to create in the system command line

create user username

Method 2: Use the CREATE ROLEcommand to create in the PostgresSQL command line

CREATE ROLE rolename;

Method 3: Use CREATE USERcommand creation in the PostgresSQL command line

CREATE USER username;

CREATE USERCREATE ROLEThe difference between and is that CREATE USERthe user created by the command has the login authority by default, but CREATE ROLEnot.

\du Instruction to display user and user’s user attributes

Set user attributes when creating users

Basic syntax format
CREATE ROLE role_name WITH optional_permissions;
Example: Set the login authority when creating a user.
CREATE ROLE username WITH LOGIN;

You can \h CREATE ROLEview all the settable management rights through instructions

Modify user attributes

Command format for modifying permissions

ALTER ROLE username WITH attribute_options;

For example: users can be prohibited from logging in by
ALTER ROLE username WITH NOLOGIN;

Set access permissions

The syntax is as follows:

GRANT permission_type ON table_name TO role_name;
Examples:
GRANT UPDATE ON demo TO demo_role;
-Give update permission to demo_role demo table
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to demo_role;
-Give demo_role SELECT permission on all tables

Special symbol: ALLRepresents the access authority, and PUBLICrepresents all users

GRANT ALL ON demo TO demo_role;-Give the user all permissions
GRANT SELECT ON demo TO PUBLIC;-grant SELECT permissions to all users
\z Or \dp instructions to display user access rights.
\h GRANT Show all configurable access rights

Revoke user access

The syntax is as follows:

REVOKE permission_type ON table_name FROM user_name;

Wherein permission_typeand table_namemeaning GRANTcommand same.

user group

In Postgres, the user is actually the role the same as the group role. It contains other roles of the role that groups.

Example of creating a group:

CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;

Switch ROLE

SET ROLE role_name; -  switch to role_name user
RESET ROLE;  -  switch back to the original role

INHERITPermissions: This attribute allows group members to have all the permissions of the group

ALTER ROLE test_user INHERIT;

Delete users and groups

Deleting users and groups is simple:

DROP ROLE role_name;
DROP ROLE IF EXISTS role_name;

Deleting a group role only deletes the group role itself, and theme

Deleting a group role only deletes the group role itself, and the members of the group are not deleted.

Thanks for reading this post.