Postgres The operation to create a Role and grant permissions

Create a Role

CREATE USER <role_name> PASSWORD '<role_password>';

Grant permissions

Grant database all permissions

GRANT ALL ON DATABASE <db_name> TO <role_name>;

Grant read-only permissions (cannot directly grant SELECT permissions at the db level)

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <role_name>;

View permissions

SELECT * FROM information_schema.role_table_grants;

Supplement: PostgreSql role, user creation

1. Database Role

The database role is completely different from the concept of operating system users. It can easily maintain the database, but it is not necessary.

Create a database role

CREATE ROLE name;

Delete existing roles

DROP ROLE name;

Create and delete users

CREATE USER name;
dropuser name;

Check for existing database roles

SELECT rolname FROM pg_roles;

\du # Use this command to view it

Database default user

After the database is installed, a default user postgres will be established in the operating system and in the database. This role is "super user". If you want to use more functions of the database, you must first use this user to connect to the database.

Database connection command

psql -U name;

2. Database role attributes

A database role has a large number of attributes that define the role's database login and operation permissions.

CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid

The LOGIN attribute is used to give roles permission to connect to the database. Choose one of the commands.

CREATE ROLE name LOGIN;
CREATE USER name;

CREATE USER and CREATE ROLE are almost the same, except that CREATE USER has login permission by default, CREATE ROLE does not.

Super User

The super user has all permissions in the database. Be careful to give the permissions of the super user. The command is as follows

CREATE ROLE name SUPERUSER;

Create database permissions

CREATE ROLE name CREATEDB;

Create role permissions

CREATE ROLE name CREATEROLE;

Initialize copy

CREATE ROLE name REPLICATION LOGIN;

password

Password is the client authentication method for users to log in to the database. After the password is created, the md5 encryption algorithm is used to encrypt it.

CREATE ROLE name PASSWORD 'string';

Change permissions

ALTER ROLE name SET enable_indexscan TO off;

3. Role Group

Role groups are similar to group permissions of the operating system, and can easily manage the permissions of members in the group.

GRANT and REVOKE operations can be used to assign and recycle permissions.

GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.