Logins

Each person should have their own login/username, which should not be shared. Most people will have a single login but because it is best-practice to do regular work with only the minimal amount of permission necessary some people will have more than one login; a login for each level of required access.

In addition to regular logins, which are used for regular database work and have either read-only or read-write access to database content, there are the following other kinds of logins.

Administrators

Administrators have near-maximal permissions. The primary purpose of an administrator login is to create or destroy other logins, and that is pretty much the only regular use of an administrator login. Some other administrative tasks, such as killing runaway PostgreSQL queries and monitoring database activity, are also done occasionally.

Caution

Administrative logins have access to the underlying operating system files in the same way as a Unix shell user has access. Administrative users can even run operating system level commands. Administrative accounts should be well-secured and utilize good passwords.

The build system and other supplied tools automatically ensure that tasks performed as an administrator are done by the appropriate group on behalf of the administrator. Administrators are in, and can become, the role_owner group and so can create and destroy roles. And they are in and can become the admin and therefore create and destroy tables, create and destroy user logins, etc.

Only a few people are expected to have administrator privileges.

Those people with administrator privileges will typically have 2 logins, one ordinary login and a second login with administrator privileges. The administrative login should be used only when necessary, as when a new person is given access to SokweDB and a new database login must be created. Ordinary interactions with the database, data entry, data retrieval, etc., should be done with a non-administrator login.

PostgreSQL also has a superuser role.[4] Superusers have permission to do anything with a database. As with the Unix root user, there should usually be only a single PostgreSQL role with superuser privileges.[1]

Developers

Developers, the users who maintain the database structure, etc., are a special kind of administrator and the same care should be taken handing out and securing developer logins as administrative logins.[2]

The difference between developers and administrators is that developers do not have the ability to create or drop database logins, or have any more rights to administer permissions than a regular user has.[3]

Like administrators, developers have the ability to monitor database activity, kill runaway queries, and so forth. They full have access to all database content and also the ability to change the database’s structure, rule validation, and so forth.

Developers are expected to have ordinary logins, for use when interacting with database content.

Creating Logins

Creating (and destroying) logins is best done using the SokweDB supplied tools. They take care of the relatively complex task of controlling permission management to prevent removal of permissions from logins created by another login that is later deleted.

Creating Administrators

When creating administrative users connect to the database using an “administrative user”. This is a role designated as such, with elevated permissions, which will always exist. Don’t use a role belonging to a person but a role such as the bootstrap PostgreSQL superuser. (You must use the bootstrap PostgreSQL superuser when you do not have a PostgreSQL role that can login and create groups.) If you do not use a role that always exists, then when the creating role is dropped the created role will lose part of its administrative privilege and will no longer be able to use the PRIMEDB tools to create users.

Hint: Use Unix privilege escalation techniques to execute the SokweDB command line tool in an environment that gives the tool access to the PostgreSQL superuser.

Creating Non-Administrative Logins

Creating logins that are not administrative logins, logins for developers or regular users, should be done using a regular administrative login. (Don’t use an “administrative user” such as the bootstrap PostgreSQL superuser.)

The logins which should be used, when managing logins or at other times, are those assigned per-person, for each person’s use. This includes the per-person administrative logins and the system’s design assumes that the per-person administrative logins will be used to for login management.

Footnotes

[1]

In environments where the PostgreSQL database is hosted and managed by a cloud service provider (Amazon RDS, Azure PostgreSQL, etc.) the PostgreSQL superuser is not available to the customer.

[2]

Developers can do things like server-side COPY operations or extension installation, and thus, like administrators, have access to the server’s file system and server executables.

[3]

Developers are not in the role_owner group.

[4]

The Azure cloud platform does not allow logins (aka roles) to have the SUPERUSER role attribute. Instead, the CREATEDB and CREATEROLE attributes are most that can be given. The SokweDB admin group (aka role) has CREATEDB and CREATEROLE privileges. This is enough that there is no need for the actual SUPERUSER attribute.

Membership in the admin role and having the role attributes CREATEROLE and CREATEDB is what grants a role (login) superuser privileges.

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