Database Permission Levels

The database associates each login with specific permissions to objects (tables, etc.) within the database.[1]

To access the data in the database permission must be granted. This is done per user login.

There are 2 ordinary levels of permission. Their names are:

reader

Permission to query database content.

writer

All the permissions of reader plus permission to alter the content of the database.

Ordinary permissions are database roles.

Changing A User’s Permissions

Normally, users are given the desired level of access when their login is created using SOKWEDB administration tools. There are various ways permissions can be granted or changed. One way is with SQL[2], e.g.:

GRANT reader TO someuser;

The Administrator Permission Levels

Administrators are in two groups, role_owner and admin. The admin permission level has near-maximal permissions. It owns all database objects. The role_owner group manages all roles and has permission to create new users.[3]

It is the admin role that owns all the SOKWEDB database objects, the tables, views, etc.

As PostgreSQL introduces new pre-defined roles the admin should be added as a member of those roles. This will give all administrators the requisite additional access.

Footnotes

[1]

These are implemented as PostgreSQL roles, and can be thought of as groups in which user logins are placed to grant them permissions.

[2]

SQL is ordinarily only used to change a login’s permission level.

[3]

The role_owner group exists only because roles do not have WITH ADMIN TRUE on themselves. So the admin role cannot grant the ability to become itself to administrators, who need to be able to become admin when creating database objects. A second role is needed to grant the admin group WITH ADMIN TRUE to administrators. This second role must have WITH ADMIN on the admin, which is what a role creator has so this second role may as well create all roles as well as manage role membership.

Page last generated: 2025-04-12 18:47:18 UTC