PostgreSQL Tutorial: Assigning Privileges

Summary: in this tutorial, you will learn how to assign privileges in PostgreSQL.

Table of Contents

Introduction

When an object is created, it is assigned an owner. The owner is normally the role that executed the creation statement.

For most kinds of objects, the initial state is that only the owner (or a superuser) can do anything with the object. To allow other roles to use it, privileges must be granted.

There are several different kinds of privilege: SELECT, INSERT, UPDATE, DELETE,REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

Privileges:

• SELECT - Ability to perform SELECT statements on the table.
• INSERT - Ability to perform INSERT statements on the table.
• UPDATE - Ability to perform UPDATE statements on the table.
• DELETE - Ability to perform DELETE statements on the table.
• TRUNCATE - Ability to perform TRUNCATE statements on the table.
• REFERENCES - Ability to create foreign keys (requires privileges on both parent and child tables).
• TRIGGER - Ability to create triggers on the table.
• CREATE - Ability to perform CREATE TABLE statements.
• ALL - Grants all permissions.
• EXECUTE - Grant file Execute permission.

Assigning Privileges to:

1. Groups

2. Users

3. Roles

4. Schema & Search Path

• Able to create USERS, SCHEMAS, ROLES, Alter SEARCH_PATH,
• GRANT and REVOKE privileges

5. Object ownership

Groups

A group is a group of database users to whom to grant privileges.

Granting privileges to a group:

grant all privileges on database <db_name> to <group_name>;

Users

When the user create a table, those table belong to the PUBLIC schema. A database can be split up between multiple users using schemas.

Granting privileges to a user:

grant all privileges on database <db_name> to <user_name>;
grant all privileges on <table_name> to <user_name>;

Revoking privileges from a user:

revoke all privileges on database <db_name> from <user_name>
Grant insert privilege to all users on table tab1:
GRANT INSERT ON tab1 TO PUBLIC;

Grant all available privileges on a table to user nijam:

GRANT ALL PRIVILEGES ON emp TO nijam;

Note that while the above will indeed grant all privileges if executed by a superuser or the owner of “emp”, when executed by someone else it will only grant those permissions for which the someone else has grant options.

Grant membership in role admins to user nijam:

GRANT admins TO nijam;

Roles

Database roles are conceptually completely separate from operating system users. In practice it might be convenient to maintain a correspondence, but this is not required. Database roles are global across a database cluster installation (and not per individual database).

To assign privileges, the GRANT command is used. So, if “nijam” is an existing role, and “emp” is an existing table, the privilege to update the table can be granted with:

GRANT UPDATE ON emp TO nijam;

The special name PUBLIC can be used to grant a privilege to every role on the system. Writing ALL in place of a specific privilege specifies that all privileges that apply to the object will be granted.

GRANT all ON emp TO public;

Schema

Each user have their own schema and multiple schemas, the schema search path can be altered so that each user find their tables first and can also access the other user tables. Schema as a namespace for tables that also provides security. By creating a schema for users and granting them rights on that schema, the user will create tables on that schema instead of public. schema search path is set which users objects set in PostgreSQL database, the default value for this parameter is $user, public.

search_path can be set on a users basis, to look thorough any random list of schemas.

Use psql’s \dp command to obtain information about existing privileges for tables and columns:

=> \dp emp
                                  Access privileges
 Schema |  Name   | Type  |   Access privileges   |   Column privileges   | Policies
--------+---------+-------+-----------------------+-----------------------+----------
 public | emp     | table | u1=arwdDxt/u1        +| col1:                +|
        |         |       | =r/u1                +|   u1_rw=rw/u1         |
        |         |       | admin=arw/u1          |                       |
(1 row)

The entries shown by \dp are interpreted thus:

rolename=xxxx -- privileges granted to a role
     xxxx -- privileges granted to PUBLIC
            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege
        /yyyy -- role that granted this privilege   =

Object ownership

Grant connect privilege to user for database connect:

GRANT CONNECT ON DATABASE database_name TO user_name;

Grant connect privilege to ALL user for database connect:

GRANT CONNECT ON DATABASE database_name TO Public;
GRANT CONNECT ON DATABASE database_name TO user_name;
REVOKE ALL ON ALL TABLES IN SCHEMA schema_name FROM PUBLIC;
GRANT CONNECT ON DATABASE database_name TO user_name;

Assign permission to access all objects in database to a particular user:

grant all privileges on database dbname to dbuser;
GRANT CONNECT ON DATABASE database_name TO user_name;

Create role with login privilege:

CREATE ROLE demo_role WITH LOGIN;
GRANT CONNECT ON DATABASE database_name TO user_name;

Revoke login permission:

ALTER ROLE demo_role WITH NOLOGIN;
GRANT CONNECT ON DATABASE database_name TO user_name;
GRANT UPDATE ON demo TO demo_role;
GRANT INSERT ON demo TO PUBLIC;

To view the grant table:

=> \z
                                    Access privileges
 Schema |    Name     |   Type   |     Access privileges      | Column access privileges
--------+-------------+----------+----------------------------+------------------------  
 public | demo        | table    | postgres=arwdDxt/postgres +|
        |             |          | demo_role=w/postgres      +|
        |             |          | test_user=arwdDxt/postgres+|
        |             |          | =a/postgres                |
 public | demo_id_seq | sequence |                            |
(2 rows)

CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;