Import:PostgreSQL

Creating per user request

Access Control

Postgresql has a relatively simple but powerful access control system. The central concept of the system is roles. A role is more generally used as a way of managing access control for groups of users; when configured properly, however, a role is a user account. The second important concept is that of permissions. Permissions are settings on objects in postgres, i.e. databases and tables.

Roles

A given role has a number of attributes.

• Login privilege

Roles with this can log in to postgresql with pgsql as this role and execute commands for administration or updating tables as their permissions allow.

• Superuser status

A role with superuser status bypasses all permission checks completely. Be careful. You probably shouldn't have more than one of these.

• Database creation

This is not given to roles by default. Without this role, the role cannot create new databases.

• Role creation

This is also not given to roles by default. This restricts the creation of more roles. Be very careful with this attribute.

• Password

The value of the password for the role. This is relevant for pg_hba.conf auth-method settings password, md5, and crypt.

Permissions

You'll primarily be concerned with permissions on datables and tables. SELECT, UPDATE, INSERT, and DELETE are self explanatory.

• REFERENCES

This permissions allows the role to create foreign keys against this table. This requires permission on both ends of the relation.

• TRIGGER

This allows the creation of trigger on the table.

• CREATE

This allows the creation of the database.

• CONNECT

This allows a user role to connect to the database.

• TEMPORARY
• TEMP

This allows temporary tables to be created within the database.

• USAGE

Allows usage of specific procedural external scripting langauges.

• ALL PRIVILEGES

This grants all available privileges.

• Groups

Roles and permissions are tied together with the group_role attribute.

Using the GRANT command, you can grant a role and all the attributes and permissions associated with it to another role. Using this, you can organize your user accounts, which ones are allowed to access which range of databases, and overall increase the maintainability and safety of your system.

Examples

Creating a role

CREATE ROLE john;

• Alternatively, you can specify a role attribute on creation.

CREATE ROLE john with superuser;

• Deleting roles is simple.

DROP ROLE john;

• If you need to manage your roles, you can use the pg_roles table.

Privileges for roles are managed using GRANT and REVOKE. Names can refer to tables or databases, and there is one special name, "PUBLIC" which can be used to affect every role in the system.

GRANT INSERT on dev_database to john;

REVOKE is also useful, particularly for stripping a role of all permissions before granting specific accesses to a user account.

REVOKE ALL ON prod_database on john;

Role membership is a powerful tool for organizing your access control. By assigning permissions and attributes to one role, that role can then be applied to other roles, allowing you to layer your permissions, similar to Unix user groups. Postgres will not allow you to set up circular role memberships or allow you to grant roles to the PUBLIC name. By using role membership, you can isolate and protect your data.

CREATE ROLE users_group; ALTER ROLE users_group SET LOGIN;

CREATE ROLE john; GRANT users_role to john; REVOKE users_role from john;