Restrict user access by time

Andrey Aksenov

Contents

Overview
Add and drop time-based constraints

Greengage DB allows you to restrict access for the specified roles on certain days or during specific time intervals. For example, you can enforce one of the following restrictions:

Deny access on Saturdays and Sundays.
Deny access from Saturday at 10 p.m. to Sunday at 8 a.m.

To restrict access by time, the DENY and DENY BETWEEN role attributes are used:

DENY sets a day to deny access.
DENY BETWEEN sets an interval during which access is denied.

Time-based constraints set using these attributes are enforced when a user logs in. For example, if a user attempts to connect to a database during a prohibited period, the following error is raised:

FATAL:  authentication failed for user "alice": login not permitted at this time

There are a few specifics related to time-based constraints:

Time-based constraints are not inherited and only apply to the role to which they are assigned.
The SET ROLE and SET SESSION AUTHORIZATION commands are not affected by any time-based constraints.
Time-based authentication relies on the server’s time, ignoring time zones.

To follow the examples below, create a database role with the LOGIN attribute:

CREATE ROLE alice WITH LOGIN;

There are two ways to specify a day when creating a time-based constraint:

Using a day name, for example, Saturday or Sunday.
Using a day number from 0 (Sunday) to 6 (Saturday).

The example below shows how to deny access on weekends:

Day name

ALTER ROLE alice
    DENY DAY 'Saturday'
    DENY DAY 'Sunday';

Day number

ALTER ROLE alice
    DENY DAY 6
    DENY DAY 0;

To remove time-based restrictions, use the DROP DENY FOR clause:

Day name

ALTER ROLE alice
    DROP DENY FOR DAY 'Saturday'
    DROP DENY FOR DAY 'Sunday';

Day number

ALTER ROLE alice
    DROP DENY FOR DAY 6
    DROP DENY FOR DAY 0;

The result should look as follows:

NOTICE:  dropping DENY rule for "alice" between Saturday 00:00:00 and Saturday 24:00:00
NOTICE:  dropping DENY rule for "alice" between Sunday 00:00:00 and Sunday 24:00:00

To specify an interval during which access is denied, use the BETWEEN and AND keywords:

ALTER ROLE alice
    DENY BETWEEN DAY 'Friday' AND DAY 'Saturday';

To remove a time constraint for such an interval, use the DROP DENY FOR clause for any day that falls into this interval:

ALTER ROLE alice
    DROP DENY FOR DAY 'Friday';

The result should look like this:

NOTICE:  dropping DENY rule for "alice" between Friday 00:00:00 and Saturday 24:00:00

Note that day intervals cannot extend beyond Saturday. For example, there is no ability to deny access on weekends using this syntax:

ALTER ROLE alice
    DENY BETWEEN DAY 'Saturday' AND DAY 'Sunday';

To specify a time of day, use the TIME keyword. You can define the time in either 24-hour or 12-hour format, for example, TIME '14:00' or TIME '02:00 PM'.

The example below shows how to specify the intervals of time during which access is denied:

24-hour

ALTER ROLE alice
    DENY BETWEEN DAY 'Saturday' TIME '02:00' AND DAY 'Saturday' TIME '06:00'
    DENY BETWEEN DAY 'Sunday' TIME '02:00' AND DAY 'Sunday' TIME '14:00';

12-hour

ALTER ROLE alice
    DENY BETWEEN DAY 'Saturday' TIME '02:00 AM' AND DAY 'Saturday' TIME '06:00 AM'
    DENY BETWEEN DAY 'Sunday' TIME '02:00 AM' AND DAY 'Sunday' TIME '02:00 PM';

To drop the specified time constraints, use the DROP DENY FOR clause for the corresponding days:

ALTER ROLE alice
    DROP DENY FOR DAY 'Saturday'
    DROP DENY FOR DAY 'Sunday';

The result looks like this:

NOTICE:  dropping DENY rule for "alice" between Saturday 02:00:00 and Saturday 06:00:00
NOTICE:  dropping DENY rule for "alice" between Sunday 02:00:00 and Sunday 14:00:00

Roles and privileges

Password hashing

Restrict user access by time

Overview

Add and drop time-based constraints

Restrict access on specific days

Restrict access for a range of days

Restrict access by time of day