postgresql Role - plsql 6 class notes

The following statement uses the CREATE ROLE statement to create a new role called bob:

These roles are to call from api

CREATE ROLE bob;


SELECT rolname FROM pg_roles;


2) Create superuser roles
The following statement creates a role called john that has the superuser attribute.
CREATE ROLE john 
SUPERUSER 
LOGIN 
PASSWORD 'securePass1';

Notice that you must be a superuser in order to create another superuser role.

3) Create roles that can create databases

If you want to create roles that have the database creation privilege, you use the CREATEDB attribute:

CREATE ROLE dba 
CREATEDB 
LOGIN 
PASSWORD 'Abcd1234';


4) Create roles with validity period
To set a date and time after which the role’s password is no longer valid, you use the valid until attribute:
VALID UNTIL 'timestamp'
Code language: JavaScript (javascript)
For example, the following statement creates a dev_api role with password valid until the end of 2029:
CREATE ROLE dev_api WITH
LOGIN
PASSWORD 'securePass1'
VALID UNTIL '2030-01-01';

5) Create roles with connection limit

To specify the number of concurrent connections a role can make, you use the CONNECTION LIMIT attribute:

CONNECTION LIMIT connection_count
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following creates a new role called api that can make 1000 concurent connections:

CREATE ROLE api
LOGIN
PASSWORD 'securePass1'
CONNECTION LIMIT 1000;

PostgreSQL GRANT statement examples
First, use the postgres user to connect to the PostgreSQL database server using any client tool of your choice.
Second, create a new user role called joe that can login to the PostgreSQL database server:
create role joe 
login 
password 'Abcd1234';
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Third, create a new table called candidates:

To Check Current User
SELECT current_user;  -- user name of current execution context
SELECT session_user;  -- session user name (to all database commonly)
check PostgreSQL user permissions
SELECT * FROM pg_roles;

new user cannot create another role
create role testRole 
login 
password 'Abcd1234';
ERROR:  permission denied to create role
SQL state: 42501

create table candidates (
    candidate_id int generated always as identity,
    first_name varchar(100) not null,
    last_name varchar(100) not null,
    email varchar(255) not null unique,
    phone varchar(25) not null,
    primary key(candidate_id)
);

INSERT INTO candidates(first_name, last_name, email, phone)

VALUES('Joe','Com','joe.com@example.com','408-111-2222');

select * from candidates

ERROR: permission denied for table candidates SQL state: 42501

First change to super user

SET ROLE 'postgres';

Then Grant

GRANT SELECT, INSERT, UPDATE, DELETE
ON candidates 
TO testRole;

SET ROLE 'role_name';

SELECT current_user;

Then try again

INSERT INTO candidates(first_name, last_name, email, phone)
VALUES('Joe','Com','joess.com@example.com','408-111-2222');

select * from candidates

--------------------

SELECT rolname FROM pg_roles;

CREATE ROLE alice 

LOGIN 

PASSWORD 'securePass1';

CREATE ROLE dev_api WITH

LOGIN

PASSWORD 'securePass1'

VALID UNTIL '2030-01-01';

SELECT current_user;

SELECT session_user;

create table candidates (

    candidate_id int generated always as identity,

    first_name varchar(100) not null,

    last_name varchar(100) not null,

    email varchar(255) not null unique,

    phone varchar(25) not null,

    primary key(candidate_id)

);

SET ROLE 'bob';

create role joe 

login 

password 'Abcd1234';

---permission denied to create role

SELECT rolname FROM pg_roles;

SET ROLE 'postgres';

create role joe 

login 

password 'Abcd1234';

SET ROLE 'joe';

SELECT * FROM candidates;

---permission denied for table candidates

SET ROLE 'postgres';

GRANT SELECT 

ON candidates 

TO joe;

SET ROLE 'joe';

SELECT * FROM candidates;

SET ROLE 'postgres';

GRANT INSERT, UPDATE, DELETE

ON candidates 

TO joe;

INSERT INTO candidates(first_name, last_name, email, phone)

values('joe','com','joe.com@example.com','408-111-2222');

SELECT * FROM candidates;

SET ROLE 'joe';

INSERT INTO candidates(first_name, last_name, email, phone)

values('test','com','test.com@example.com','808-111-2222');