Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "what are PostgreSQL users and roles". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

PostgreSQL uses the concept of roles: manage database access. Depending on the settings of the role itself, a role can be thought of as a database user, or a group of database users. 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.

1. Create a role

Syntax:

CREATE ROLE 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 |

1) role attributes: database roles have a series of properties that define their permissions.

Login permissions: only roles with LOGIN attributes can log in (connect to the database). The role with the LOG IN attribute is actually a database user.

CREATE ROLE name LOGIN: equivalent to CREATE USER name

Superuser: the database superuser bypasses all permission checks. With all the authority.

CREATE ROLE name SUPERUSER (only if you are a superuser)

CREATEDB: create a database.

REPLICATION: can only be specified with LOGIN. This permission is required to log in to the database using Replicate mode or to create a delete replicate slots.

CREATE ROLE name REPLICATION LOGIN.

2) role relationship: usually many roles are divided into the same group to manage. For example, grant permissions to a group, or reclaim permissions for a group. Generally, users who join a group do not grant LOGIN permission.

CREATE ROLE name

After you create the ROLE, you can grant,revoke the members.

GRANT group_role TO role1,...

REVOKE group_role FROM role1,...

INHERIT: these clauses determine whether a role "inherits" the privileges of the role to which it belongs. A role with the INHERIT attribute can automatically use any database privilege to grant all roles to which it directly or indirectly belongs. Without inheritance, joining another role only grants the ability to set the role to that other role; the privileged role of the other party is available only after this operation is completed. If not specified, the default is INHERIT.

two。 Other options

BYPASSRLS: bypass all row-level security policies.

CONNECTION LIMIT connlimit: limit the number of logins for users. The default is-1 (no limit).

[ENCRYPTED] PASSWORD 'password': sets the login password. ENCRYPTED (omitted by default, backward compatible), the encryption method is determined by the configuration file.

VALID UNTIL 'timestamp': specifies the password validity period.

IN ROLE: add the currently created role to the specified role group. Note: you cannot join as administer, you can only use grant alone.

ROLE role_name: add role_name as a member to the currently created role (that is, the current role as group).

ADMIN role_name: the permissions granted to role_name grant membership on the basis of ROLE role_name. Cascading permissions similar to oracle.

Test:

[postgres@abc ~] $psql

Password for user postgres:

Psql (11.2)

Type "help" for help.

Postgres=# CREATE ROLE LANSHAN LOGIN; creates a role

CREATE ROLE

Postgres=# CREATE USER lanshan WITH PASSWORD 'admin123'; can see that user names in pg are case-insensitive

ERROR: role "lanshan" already exists

Postgres=# CREATE USER lanshan1 WITH PASSWORD 'admin123'; creates a user and sets the password

CREATE ROLE

Postgres=# CREATE ROLE lanshan2 WITH PASSWORD 'admin123'; creates a role and sets the password

CREATE ROLE

Postgres=#\ Q

[postgres@abc ~] $psql-Ulanshan1-dpostgres user logs in to the database

Password for user lanshan1:

Psql (11.2)

Type "help" for help.

Postgres= >\ Q

[postgres@abc ~] $psql-Ulanshan2-dpostgres lanshan2 is a role and cannot be logged in.

Password for user lanshan2:

Psql: FATAL: role "lanshan2" is not permitted to log in

Postgres=# CREATE USER lanshan2 WITH PASSWORD 'admin123'; users and roles cannot have duplicate names

ERROR: role "lanshan2" already exists

Second, create users

In fact, users and roles are roles, but users are roles with login permissions.

Syntax:

CREATE USER name [[WITH] option [...]]

Where option can be:

SUPERUSER | NOSUPERUSER-dba user / regular user

| | CREATEDB | NOCREATEDB-permission to create db |

| | CREATEROLE | NOCREATEROLE-create role |

| | 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 |

Test creation user:

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

Create user username

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

CREATE ROLE rolename

[postgres@abc ~] $createuser lss

[postgres@abc ~] $psql

Psql (11.2)

Type "help" for help.

Postgres-#\ du

List of roles

Role name | Attributes | Member of

-+

Lss | | {}

Postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Authorization

Postgres=# alter role lss Createrole CREATEDB

ALTER ROLE

Postgres=#\ du

List of roles

Role name | Attributes | Member of

-+

Lss | Superuser, Create role, Create DB | {}

Postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Multiple permissions are granted separated by spaces

Postgres=# alter role lss REPLICATION BYPASSRLS

ALTER ROLE

Postgres=#\ du

List of roles

Role name | Attributes | Member of

-+

Lss | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Modify a user's password

Postgres=# ALTER ROLE lss WITH PASSWORD 'admin123'

ALTER ROLE

Authorize the user

Pgtest=# GRANT ALL PRIVILEGES ON DATABASE "postgres" to lss

GRANT

Login:

[postgres@abc] $psql-U lss-d pgtest-W

Password:

Psql: FATAL: Peer authentication failed for user "lss"

Login failed

[postgres@abc data] $cat pg_hba.conf-this profile specifies the authentication method

# TYPE DATABASE USER ADDRESS METHOD

# login directly to the "local" is for Unix domain socket connections only operating system

Local all all md5

# local all all peer

# IPv4 local connections: log in with a specified IP address

# host all all 127.0.0.1/32 ident

Host all all 127.0.0.1/32 md5

# IPv6 local connections: log in with a specified IP address

Host all all:: 1/128 ident

# Allow replication connections from localhost, by a user with the

# replication privilege. Copy permission, authentication method

Local replication all peer

Host replication all 127.0.0.1/32 ident

Host replication all:: 1/128 ident

Ident: get the client's operating system name by contacting the client's ident server and check to see if it matches the requested database user name. Ident authentication can only be used on TCIP/IP connections.

Peer: get the operating system user of the client from the operating system and check to see if it matches the requested database user name. This is only available for local connections.

Md5: requires the client to provide a double MD5 encrypted password for authentication.

Trust: no authentication required

Change the authentication mode of operating system authentication login to MD5

Local all all md5

[postgres@abc data] $pg_ctl reload-reload configuration file

[postgres@abc data] $psql-Ulss-dpgtest

Password for user lss:

Psql (11.2)

Type "help" for help.

Pgtest=#

What if the superuser forgets his password?

Vi pg_hba.conf

Local all all trust

[postgres@abc data] $pg_ctl reload

[postgres@abc data] $psql

Psql (11.2)

Type "help" for help.

Postgres=# alter user postgres WITH PASSWORD 'admin123'

ALTER ROLE

Postgres=# exit

After changing the password, change the authentication method to md5, and reload the configuration.

[postgres@abc data] $psql

Password for user postgres:

Psql: FATAL: password authentication failed for user "postgres"

[postgres@abc data] $psql

Password for user postgres:

Psql (11.2)

Type "help" for help.

Postgres=#

III. SCHEMA

CREATE SCHEMA creates a new SCHEMA in the current database. The SCHEMA name must be different from the name of the existing SCHEMA in the current database.

A SCHEMA is essentially a namespace: it contains named objects (tables, data types, functions, and operators) whose names can copy the names of other objects that exist in other SCHEMA. Access named objects by "qualifying" their names with SCHEA names as a prefix, or by setting a search path that contains the desired SCHEMA. The CREATE command that specifies unqualified object names creates objects in the current mode (objects in front of the search path can be determined using the function current_schema).

CREATE SCHEMA can also contain subcommands to create objects in the new SCHEMA. The subcommand is essentially the same as the separate command issued after creating the SCHEMA, but if an authorization clause is used, all objects created will be owned by that user.

1. Create schema

Syntax:

CREATE SCHEMA schema_name [AUTHORIZATION role_specification]

[schema_element [...]]

CREATE SCHEMA AUTHORIZATION role_specification [schema_element

[...]]

CREATE SCHEMA IF NOT EXISTS schema_name

[AUTHORIZATION role_specification]

CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

Where role_specification can be:

User_name

| | CURRENT_USER |

| | SESSION_USER |

two。 Parameters.

The schema_name:schema name cannot start with pg_. Pg_ is reserved for system users.

User_name: the newly created user will belong to the new SCHEMA. If omitted, the default is the user who executed the command. To create a SCHEMA owned by another role, you must be a direct or indirect member of that role, or a superuser.

Schema_element: the SQL statement that defines the object to be created in the schema. Currently, only creating tables, creating views, creating indexes, creating sequences, creating triggers, and GRANT are accepted as clauses in CREATE SCHEMA. Other types of objects can be created in separate commands after the schema is created.

IF NOT EXISTS: if a schema with the same name already exists, it is not created. When using this option, you cannot include the schema_ element subcommand.

Test:

Create a schema:

Postgres=# CREATE SCHEMA myschema

CREATE SCHEMA

Create a schema for a user:

Postgres=# CREATE SCHEMA AUTHORIZATION joe; (this user or role must exist)

ERROR: role "joe" does not exist

Postgres=# CREATE SCHEMA AUTHORIZATION lss

CREATE SCHEMA

Create a SCHEMA named test, which will be owned by the user lss unless there is already a schema named test. (it doesn't matter whether lss has a SCHEMA that already exists.)

Postgres=# CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION lss

CREATE SCHEMA

Create a schema that does not end with a semicolon and defaults to a subcommand:

Postgres=# CREATE SCHEMA hollywood

Postgres-# CREATE TABLE films (title text, release date, awards text [])

Postgres-# CREATE VIEW winners AS

Postgres-# SELECT title, release FROM films WHERE awards IS NOT NULL

CREATE SCHEMA

Equivalent to:

Delete the created object first:

ERROR: cannot drop table hollywood.films because other objects depend on it

DETAIL: view hollywood.winners depends on table hollywood.films

HINT: Use DROP... CASCADE to drop the dependent objects too.

Postgres=# DROP TABLE hollywood.films cascade

NOTICE: drop cascades to view hollywood.winners

DROP TABLE

Delete schema

Postgres=# drop schema hollywood cascade

DROP SCHEMA

We can see that it is very similar to oracle's command.

Postgres=# CREATE SCHEMA hollywood

CREATE SCHEMA

Postgres=# CREATE TABLE hollywood.films (title text, release date, awards text [])

CREATE TABLE

Postgres=# CREATE VIEW hollywood.winners AS SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL

CREATE VIEW

After the semicolon is added, it is not a subcommand, but the schema name must be specified.

That's all for "what are PostgreSQL users and roles?" Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.