# user testdb=# create user kullanici_ismi ; postgres@srv1:~$ psql -U kullanici_ismi ; # role testdb=# create role role_ismi ; postgres@srv1:~$ psql -U role_ismi ; psql: error: FATAL: role “bir_role” is not permitted to log in # This role can be used as a group. # With login parameter, can login to database. postgres=# CREATE ROLE dataera LOGIN PASSWORD 'password'; postgres=# \q postgres@srv1:~$ psql -U dataera -W
A user account and authentication method are required to connect to the database
and perform transactions.
Note: Users in the operating system and users in the database are different. For
example, the postgres user in the Linux operating system on which the postgres
database is running is the operating system user. It is used to manage the operating
system by connecting via ssh. But the postgres user you connect to the database with
psql is the database user.
Role: Used to manage the database access privileges.
The concept of role encompasses the concepts of user and group.
In versions prior to 8.1, user and group were different objects. It can now be used as a
role, user, group, and both.
We can say that it is a user or a group according to the parameters used while creating
it.
“create user” and “create role” do the same job, the only difference is that
“create user” also includes the login privilege.
Roles are global, available to all databases in the cluster.
Roles can have their own database objects (table, index, function) and can grant access
to other roles.
Access rights can be facilitated by giving cross-role memberships.
For example, a role is created for software developers working on the same project,
privileges are given to this role, and software developers are made a member of this
role. Thus, time is not lost by giving privileges to each software developer individually.
# Role (group) is created. testdb=# create role JavaYazGel_Role ; # Sample tables are created. testdb=# CREATE TABLE OrnekTablo1 ( testdb(# id serial4 NOT NULL, testdb(# “name” text NULL testdb(# ); testdb=# CREATE TABLE OrnekTablo2 ( testdb(# id serial4 NOT NULL, testdb(# “name” text NULL testdb(# ); # Sample data entry testdb=# INSERT INTO OrnekTablo1 (name) SELECT ‘Dataera’ FROM generate_series(1, 5); testdb=# INSERT INTO OrnekTablo2 (name) SELECT ‘Dataera’ FROM generate_series(1, 5); # The JavaYazGel_Role group is given the right to select testdb=# grant select on OrnekTablo1 to JavaYazGel_Role ; testdb=# grant select on OrnekTablo2 to JavaYazGel_Role ; # Users are created testdb=# CREATE ROLE kul1 LOGIN PASSWORD 'password'; testdb=# CREATE ROLE kul2 LOGIN PASSWORD 'password'; testdb=# CREATE ROLE kul3 LOGIN PASSWORD 'password'; # Users are granted group membership testdb=# grant JavaYazGel_Role to kul1 ; testdb=# grant JavaYazGel_Role to kul2 ; testdb=# grant JavaYazGel_Role to kul3 ; testdb=# \q # Connect with kul1 postgres@srv1:~$ psql -d testdb -U kul1 -W # Query testdb=> select * from OrnekTablo1 ; id | name ----+--------- 1 | Dataera 2 | Dataera 3 | Dataera 4 | Dataera 5 | Dataera
It gives an access privilege error when the membership is revoked.
postgres@srv1:~$ psql -d testdb testdb=# revoke JavaYazGel_Role from kul1 ; testdb=# \q postgres@srv1:~$ psql -d testdb -U kul1 testdb=> select * from OrnekTablo1 ; ERROR: permission denied for table ornektablo1
Deleting a role
drop role role_ismi; # First, if there are tables owned, it is necessary to transfer their ownership to another role. alter table tablo_ismi owner to baska_bir_role; # Or, the privileges are transferred to another role. REASSIGN OWNED BY silinecek_role TO baska_bir_role; DROP OWNED BY silinecek_role; DROP ROLE silinecek_role;
pg_roles can be queried to list roles
postgres=# SELECT rolname FROM pg_roles;
# or
postgres=# \du
List of roles
Role name | Attributes | Member of
--------------------------------------------------------------
DVDRental_Role | CreateDB,Cannotlogin | {}
Rental | | {DVDRental_Role}
ahmed | | {ik_role,erp_role}
auditor | | {}
dataera | | {}
Privileges of Role
create role rol_ismi login ; create user kullanıcı_ismi ; create role rol_ismi superuser ; alter user kul1 superuser ; create role rol_ismi createdb ; alter user rol_ismi createdb ; create role rol_ismi replication login ; alter user rol_ismi replication login ; create role rol_ismi password 'password' ; alter user rol_ismi password 'password' ;
login: Only roles with login privilege can connect to the database. The role that has
been given ‘login’ privilege can be considered as a user.
superuser: It is the most authorized user. Has all user privileges.
It is a dangerous privilege.
However, a user with superuser privilege can create a superuser or grant superuser
privilege to another user.
createdb: The privilege to create a database.
replication: Required for replication between databases. It must be given with
the role login.
password: It is used where a password is required during the connection. Encryption
methods such as md5, scram-sha-256 can be used. (Scram-sha-256 is recommended
for stronger encryption. It is default from version 12.)
It is recommended to use a role/user with createdb and createrole rights for routine
database administration. Since this user does not have the superuser privilege,
irreversible accidents are prevented.
inherit: If the role is created with the inherit option, its privileges are inherited by
its members, that is, its members will have the same privileges. It is the default value.
(noinherit roles do not pass to members)
Default Roles
The default roles listed below can be given to other roles and users with a grant clause.
| Role | Access Privileges |
| pg_read_all_settings | Ability to read configuration files. |
| pg_read_all_stats | pg_stat* views read access. |
| pg_stat_scan_tables | Execute functions that access share locks. |
| pg_monitor | Ability to execute functions to read monitoring views. |
| pg_signal_backend | Ability to send signals to terminate backend processes. |
| pg_read_server_files | Ability to read server files (with functions such as copy etc.) |
| pg_write_server_files | Ability to write to server files (with functions such as copy etc.) |
| pg_execute_server_program | Ability to run programs (copy etc. functions) |
grant pg_read_all_settings to role_ismi ;#To check the privileges of the roles postgres=# \dg # To check the privileges on the table postgres=# \dp+ Tablo1 testdb=# \dp+ OrnekTablo1 Access privileges Schema |Name |Type| Access privileges |Column priv | Policies --------+-----+-------+-----------------+------------+---------------+ public |tablo1 |table| postgres=arwdDxt/postgres +| | | |javayazgel_role=r/postgres | |
postgres=# SELECT * FROM pg_roles;
#To see the privileges of the roles
postgres=# \dg
Role name | Attributes | Member of
--------------+---------------------------------------------------------
bir_role | Cannot login | {}
bir_user | | {}
dataera | | {}
ik_role | No inheritance, Cannot login | {}
javayazgel_r | Cannot login | {}
kul1 | Superuser,CreateDB,Replication | {pg_read_all_settings}
kul2 | | {javayazgel_role}
postgres | Superuser,Crrole,CrDB,Rep,... | {}