Roles, Privileges, and Templates in PostgreSQL

cPBrianD · Oct 3, 2014

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:

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

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:

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.

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:

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

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.

Search

Search

Roles, Privileges, and Templates in PostgreSQL

cPBrianD

cPanel Technical Analyst II