The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Roles, Privileges, and Templates in PostgreSQL

Discussion in 'Workarounds and Optimization' started by cPBrianD, Oct 3, 2014.

  1. cPBrianD

    cPBrianD cPanel Technical Analyst II
    Staff Member

    Joined:
    Apr 2, 2013
    Messages:
    1
    Likes Received:
    1
    Trophy Points:
    3
    cPanel Access Level:
    Root Administrator
    As of PostgreSQL 8.1, users and groups were essentially merged into a single category called roles. A role can be a user, a group, or both. A role can can have privileges on databases, and can own objects within databases, such as tables.

    The primary difference between a PostgreSQL user and a role is that a user has the LOGIN privilege assumed when the user is created, whereas a role has NOLOGIN assumed.

    [size=+1]Creating a PostgreSQL role[/size]

    The syntax to create a role is:
    Code:
    CREATE ROLE name [ [ WITH ] option [ ... ] ]
    Below are available option clauses and their descriptions:

    [table="width: 500, class: full_grid, align: center"]

    Option
    Description

    SUPERUSER
    NOSUPERUSER
    Sets whether or not the role is a superuser. A superuser has the ability to override all restrictions on a database, and this option should be used with caution.
    Default: NOSUPERUSER
    The 'postgres' user is a superuser.

    ROLE
    Existing roles listed after ROLE will be added as members to the new role

    REPLICATION
    NOREPLICATION
    Defines the role's ability to initiate streaming replication or place PostgreSQL in or out of backup mode.
    Default: NOREPLICATION

    PASSWORD
    Sets the role's password.
    Only useful for roles with LOGIN.

    LOGIN
    NOLOGIN
    Sets the role's ability to log in. A role with the LOGIN option enabled is basically a user.
    NOLOGIN is the default unless the role was created with 'CREATE USER' instead of 'CREATE ROLE'. 'CREATE USER' assumes LOGIN.

    INHERIT
    NOINHERIT
    Determines whether or not the role will inherit the privileges of roles of which it is a member.
    Default: INHERIT

    IN ROLE
    The new role will be added to existing roles listed after IN ROLE

    ENCRYPTED
    UNENCRYPTED
    Determines if the system catalogue stores the role's password encrypted.

    CREATEUSER
    NOCREATEUSER
    Obsolete but still accepted syntax for SUPERUSER and NOSUPERUSER.

    CREATEROLE
    NOCREATEROLE
    Defines the role's ability to create roles.
    Default: NOCREATEROLE

    CREATEDB
    NOCREATEDB
    Determines whether or not the role can create new databases.
    Default: NOCREATEDB

    ADMIN
    Like ROLE, but also allows listed roles to add new members
    [/table]

    CREATE ROLE cli example

    Enter postgresql prompt:
    Code:
    psql -U postgres
    Create a role:
    Code:
    postgres=# CREATE ROLE psqluser WITH CREATEDB CREATEROLE NOINHERIT LOGIN PASSWORD 'supersecurepassword';
    CREATE ROLE
    Code:
    postgres=# SELECT * FROM pg_roles WHERE rolname = 'psqluser';
     rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | rolvaliduntil | rolconfig |  oid
      
    ----------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------------+---------------+-----------+------
    -
     psqluser | f        | f          | t             | t           | f            | t           |           -1 | ********    |               |           | 16384
    (1 row)
    Code:
    postgres=# \du
                 List of roles
     Role name |   Attributes   | Member of
    -----------+----------------+-----------
     flipflap  | No inheritance | {}
               : Create role     
               : Create DB
    createuser

    PostgreSQL also provides the 'createuser' command, which creates PostgreSQL roles. 'createuser' must be run as a superuser or a user with the CREATEROLE privilege.

    Syntax:
    Code:
    createuser [connection-option...] [option...] [username]
    Pass createuser the the '-?' or '–help' flag for options:
    Code:
    # createuser --help
    createuser creates a new PostgreSQL role.
    Usage:
      createuser [OPTION]... [ROLENAME]
    Options:
      -c, --connection-limit=N  connection limit for role (default: no limit)
      -d, --createdb            role can create new databases
      -D, --no-createdb         role cannot create databases
      -e, --echo                show the commands being sent to the server
      -E, --encrypted           encrypt stored password
      -i, --inherit             role inherits privileges of roles it is a
                                member of (default)
      -I, --no-inherit          role does not inherit privileges
      -l, --login               role can login (default)
      -L, --no-login            role cannot login
      -N, --unencrypted         do not encrypt stored password
      -P, --pwprompt            assign a password to new role
      -r, --createrole          role can create new roles
      -R, --no-createrole       role cannot create roles
      -s, --superuser           role will be superuser
      -S, --no-superuser        role will not be superuser
      --help                    show this help, then exit
      --version                 output version information, then exit
    Connection options:
      -h, --host=HOSTNAME       database server host or socket directory
      -p, --port=PORT           database server port
      -U, --username=USERNAME   user name to connect as (not the one to create)
      -w, --no-password         never prompt for password
      -W, --password            force password prompt
    If one of -d, -D, -r, -R, -s, -S, and ROLENAME is not specified, you will
    be prompted interactively.
    createuser cli example
    Code:
    # psql -U postgres
    Code:
    $ createuser -P
    Enter name of role to add: cpbcd
    Enter password for new role:
    Enter it again:
    Shall the new role be a superuser? (y/n) n
    Shall the new role be allowed to create databases? (y/n) y
    Shall the new role be allowed to create more new roles? (y/n) n
    Password:
    Note: The second password requested is the 'postgres' user's password, which is stored in /root/.pgpass

    Code:
    postgres=# select * from pg_roles where rolname = 'cpbcd';
     rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | rolvaliduntil | rolconfig |  oid 
    ---------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------------+---------------+-----------+-------
     cpbcd   | f        | t          | f             | t           | f            | t           |           -1 | ********    |               |           | 16385
    (1 row)
    Code:
    postgres-# \du
                 List of roles
     Role name |   Attributes   | Member of
    -----------+----------------+-----------
     cpbcd     | Create DB      | {}
    [size=+1]Privileges in PostgreSQL[/size]

    When an object such as a database or a table is created in PostgreSQL, the object has an owner. An object's owner is generally the role which created the object. This means that the object's creator and superusers are the only roles which will be able to modify or interact with that object.
    If other roles need to manipulate an object, privileges must be granted to those roles. This is done via GRANT statements. There should be no need to grant privileges to an object's creator because the creator should have all privileges by default.

    The syntax to grant privileges is:
    Code:
    GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
        [,...] | ALL [ PRIVILEGES ] }
        ON [ TABLE ] tablename [, ...]
        TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
     
    GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
        [,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
        ON [ TABLE ] tablename [, ...]
        TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
     
    GRANT { { USAGE | SELECT | UPDATE }
        [,...] | ALL [ PRIVILEGES ] }
        ON SEQUENCE sequencename [, ...]
        TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
     
    GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
        ON DATABASE dbname [, ...]
        TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
     
    GRANT { USAGE | ALL [ PRIVILEGES ] }
        ON FOREIGN DATA WRAPPER fdwname [, ...]
        TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
     
    GRANT { USAGE | ALL [ PRIVILEGES ] }
        ON FOREIGN SERVER servername [, ...]
        TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
     
    GRANT { EXECUTE | ALL [ PRIVILEGES ] }
        ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
        TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
     
    GRANT { USAGE | ALL [ PRIVILEGES ] }
        ON LANGUAGE langname [, ...]
        TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
     
    GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
        ON SCHEMA schemaname [, ...]
        TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
     
    GRANT { CREATE | ALL [ PRIVILEGES ] }
        ON TABLESPACE tablespacename [, ...]
        TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ]
     
    GRANT role [, ...] TO rolename [, ...] [ WITH ADMIN OPTION ]
    Below is a list of privileges which can be granted and their descriptions:

    [table="width: 500, class: grid, align: center"]

    Privilege
    Description

    ALL PRIVILEGES
    Grants all privileges simultaneously

    CONNECT
    Allows the role the ability to connect to a database.

    CREATE
    If CREATE is granted on a database, the role will be allowed to create new schemas within the specified database.
    If CREATE is granted on a schema, the role will be permitted to create new objects within the schema.
    If CREATE is granted on a tablespace, the role will be able to create tables, indexes, and temporary files within the tablespace. The role will also be able to create databases which use the specified tablespace.

    DELETE
    Grants the role the ability to DELETE a row from the specified table.

    INSERT
    Permits the role the ability to INSERT new rows into the specified table(s). Specific columns may also be listed.

    REFERENCES
    Allows the role the ability to create foreign key constraints. This privilege must be granted on referenced as well as referencing columns.

    SELECT
    Grants the specified role the ability to SELECT on listed (or any if none are listed) columns of the specified table, view, or sequence.

    TEMPORARY
    Permits the role the ability to create temporary tables within the specified database.

    TRIGGER
    Grants role the ability to create a trigger.

    TRUNCATE
    Permits the role the ability to run TRUNCATE on the specified table.

    UPDATE
    Allows the role the ability to UPDATE on listed (or any if none are listed) columns of the specified table.
    [/table]

    GRANT privileges cli example

    In this example I created a database 'cptest_db', a user 'cptest_user' and granted cptest_user access to cptest_db via the PostgreSQL cPanel UI:

    Code:
    postgres=# \du
                   List of roles
      Role name  |   Attributes   |  Member of 
    -------------+----------------+-------------
     cptest      |                | {cptest_db}
     cptest_db   | Cannot login   | {}
     cptest_user |                | {cptest_db}
    Connect to cptest_db:

    Code:
    postgres=# \c cptest_db
    psql (8.4.20)
    You are now connected to database "cptest_db".
    Code:
    postgres-# \l
                                       List of databases
       Name    |   Owner   | Encoding |  Collation  |    Ctype    |    Access privileges   
    -----------+-----------+----------+-------------+-------------+-------------------------
     cptest_db | cptest_db | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/cptest_db
                                                                  : cptest_db=CTc/cptest_db
    Grant ALL privileges to cptest_user on table1:

    Code:
    cptest_db=# GRANT ALL PRIVILEGES ON table1 TO cptest_user;
    GRANT
    The results:

    Code:
    cptest_db=# \dp table1
                                    Access privileges
     Schema |  Name  | Type  |     Access privileges      | Column access privileges
    --------+--------+-------+----------------------------+--------------------------
     public | table1 | table | cptest=arwdDxt/cptest      |
                             : cptest_db=arwdDxt/cptest    
                             : cptest_user=arwdDxt/cptest  
    (1 row)
    The output of '\dp $table' (list table, view, and sequence access privileges) can be parsed as follows:

    [table="width: 500, class: full_grid, align: center"]

    Parsing \dp output

    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
    [/table]

    Revoking privileges

    Granted privileges can be revoked using REVOKE statements. Below is an example revoking the TRUNCATE privilege from cptest_user on table1 in cptest_db:

    Code:
    cptest_db=# REVOKE TRUNCATE ON table1 FROM cptest_user;
    REVOKE
    The 'D' (TRUNCATE) is removed from the cptest_user's privileges now:

    Code:
    cptest_db=# \dp
                                           Access privileges
     Schema |        Name        |   Type   |     Access privileges     | Column access privileges
    --------+--------------------+----------+---------------------------+--------------------------
     public | table1             | table    | cptest=arwdDxt/cptest     |
                                            : cptest_db=arwdDxt/cptest   
                                            : cptest_user=arwdxt/cptest
    [size=+1]PostgreSQL and cPanel[/size]

    On cPanel servers, PostgreSQL can be installed by running /scripts/installpostgres. Running the script will install PostgreSQL RPMs (as of this writing version 8.4.20) and install default configurations to /var/lib/pgsql/. When PostgreSQL is installed the 'postgres' superuser is created. After installing PostgreSQL, it must be configured via WHM -> Configure PostgreSQL. Also within WHM -> Configure PostgreSQL you can create the PostgreSQL roles for cPanel users which existed prior to installing PostgreSQL. After installing PostgreSQL, newly created cPanel users will have an associated PostgreSQL user created when the cPanel account is created. These PostgreSQL users only have the ability to log into PostgreSQL, and have no other privileges by default.

    In the example below, PostgreSQL has been installed via /scripts/installpostgres , the configuration has been implemented via WHM -> Configure PostgreSQL-> Install Config, and two pre-existing cPanel users (cppg and cppg2) had associated PostgreSQL roles created via WHM -> Configure PostgreSQL -> Create Users.

    Code:
    # psql -U postgres
    Code:
    postgres=# \du
                List of roles
     Role name | Attributes  | Member of
    -----------+-------------+-----------
     cppg     |             | {}
     cppg2    |             | {}
     postgres  | Superuser   | {}
               : Create role  
               : Create DB
    Code:
    postgres=# select * from pg_user;
     usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
    ----------+----------+-------------+----------+-----------+----------+----------+-----------
     postgres |       10 | t           | t        | t         | ******** |          |
     cppg    |    16384 | f           | f        | f         | ******** |          |
     cppg2   |    16385 | f           | f        | f         | ******** |          |
    (3 rows)
    Default database and user privileges

    When a PostgreSQL database is created in cPanel, the database object and group are created. The database group has CREATE, TEMPORARY, and CONNECT privileges to the database. In the example below, a database 'cppg1_db1' has been created via cPanel.

    Code:
    postgres=# \l
                                       List of databases
       Name    |   Owner   | Encoding |  Collation  |    Ctype    |    Access privileges   
    -----------+-----------+----------+-------------+-------------+-------------------------
     cppg1_db1 | cppg1_db1 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/cppg1_db1
                                                                  : cppg1_db1=CTc/cppg1_db1
    C = CREATE
    T= TEMPORARY
    c = CONNECT

    This means that by default, a new PostgreSQL database created via cPanel has privileges to

    C) Create new schemas in the database
    T) Create temporary tables in the database
    c) Connect to the database

    When a PostgreSQL database is created via the cPanel interface, the cPanel user becomes a member of the database group. In the above example, when the 'cppg1_db1' database was created via the cppg1 user's cPanel interface, the 'cppg1' user became a member of the 'cppg1_db1' group and inherited the group's privileges. This means that the 'cppg1' user can create new schemas, create temporary tables, and connect to the cppg1_db1 database.

    Code:
    postgres=# \dg cppg1
                List of roles
     Role name | Attributes |  Member of 
    -----------+------------+-------------
     cppg1     |            | {cppg1_db1}
    When a new PostgreSQL user is created via the cPanel interface, the role is not a member of anything and has no privileges. In the example below, PostgreSQL user 'cppg1_user1' has just been created.

    Code:
    postgres=# \dg cppg1_user1
                List of roles
      Role name  | Attributes | Member of
    -------------+------------+-----------
     cppg1_user1 |            | {}
    In order to make cppg1_user1 a member of database cppg1_db1, the user must be added to the database. This is accomplished via the 'Add User To Database' function of cPanel -> PostgreSQL Databases. After adding the cppg1_user1 to the cppg1_db1 via cPanel -> PostgreSQL Databases , below is what you should see:

    Code:
    postgres=# \dg cppg1_user1
                 List of roles
      Role name  | Attributes |  Member of 
    -------------+------------+-------------
     cppg1_user1 |            | {cppg1_db1}
    Now that cppg1_user1 is a member of cppg1_db1, cppg1_user1 has CREATE, TEMPORARY, and CONNECT privileges to the cppg1_db1 database object. These are the only privileges that the cPanel user's PostgreSQL user as well as user created PostgreSQL users have by default.

    [size=+1]PostgreSQL Templates[/size]

    PostgreSQL uses template databases to create new databases. There are two template databases on a fresh PostgreSQL installation, template0 and template1.

    When a new PostgreSQL database is created it is created from template1 by default. template1 can be modified with new database objects. Adding objects to template1 will result in new PostgreSQL databases being created with those same additional objects.

    On a fresh PostgreSQL install, template0 and template1 are identical, meaning that both templates initially contain only the objects defined by the running PostgreSQL version. template0 should never be modified after PostgreSQL has been initialized on a system. Leaving template0 unmodified will ensure that the administrator will have a standard unmodified template from which to create new databases regardless of the state of the default template, template1.

    To create a database using a template other than template1, the TEMPLATE option needs to be passed to the CREATE DATABASE statement:

    Code:
    postgres=# CREATE DATABASE fresh_db TEMPLATE template0;
    CREATE DATABASE
    Recreating template1 from template0

    If template1 becomes corrupted or if the administrator simply wants to start over with a fresh template1, it can be recreated from template0.

    First we need to tell PostgreSQL that template1 shouldn't be a template anymore. Let's look at the default properties of the template1 database to see what we're working with:

    Code:
    postgres=# select * from pg_database where datname = 'template1';
      datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | dat
    config |               datacl               
    -----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+----
    -------+-------------------------------------
     template1 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         11563 |          648 |          1663 |   
           | {=c/postgres,postgres=CTc/postgres}
    (1 row)
    The 'datistemplate' property is what we are concerned with. This set to true, meaning PostgreSQL is using the template1 database as a template from which to create other databases. We need to set 'datistemplate' to false so template1 will no longer be used as a template:

    Code:
    postgres=# UPDATE pg_database SET datistemplate = 'false' WHERE datname = 'template1';
    UPDATE 1
    Code:
    postgres=# select datistemplate from pg_database where datname = 'template1';
     datistemplate
    ---------------
     f
    (1 row)
    PostgreSQL is no longer using template1 as a template. Now let's drop template1 and recreate it from template0:

    Code:
    postgres=# DROP DATABASE template1;
    DROP DATABASE
    Code:
    # CREATE DATABASE template1 TEMPLATE template0;
    CREATE DATABASE
    Now pg_database needs to be updated so template1 will be used as a template again:

    Code:
    postgres=# UPDATE pg_database SET datistemplate = 'true' WHERE datname = 'template1';
    UPDATE 1
    Code:
    postgres=# select * from pg_database where datname = 'template1';
      datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | dat
    config | datacl
    -----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+----
    -------+--------
     template1 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         11563 |          648 |          1663 |   
           |
    (1 row)
    template1 has now been recreated fresh from template0 and is all configured to be used as a template database again.
     
    cPanelJesse likes this.

Share This Page