Roles, Privileges, and Templates in PostgreSQL

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 The syntax to create a role is:
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 CREATE ROLE cli example Enter postgresql prompt:
psql -U postgres
Create a role:
postgres=# CREATE ROLE psqluser WITH CREATEDB CREATEROLE NOINHERIT LOGIN PASSWORD 'supersecurepassword'; CREATE ROLE

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)

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:
createuser [connection-option...] [option...] [username]
Pass createuser the the '-?' or '"help' flag for options:
# 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
# psql -U postgres

$ 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
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)

postgres-# \du List of roles Role name | Attributes | Member of -----------+----------------+----------- cpbcd | Create DB | {}
[size=+1]Privileges in PostgreSQL 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:
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. 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:
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:
postgres=# \c cptest_db psql (8.4.20) You are now connected to database "cptest_db".

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:
cptest_db=# GRANT ALL PRIVILEGES ON table1 TO cptest_user; GRANT
The results:
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 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:
cptest_db=# REVOKE TRUNCATE ON table1 FROM cptest_user; REVOKE
The 'D' (TRUNCATE) is removed from the cptest_user's privileges now:
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 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.
# psql -U postgres

postgres=# \du List of roles Role name | Attributes | Member of -----------+-------------+----------- cppg | | {} cppg2 | | {} postgres | Superuser | {} : Create role : Create DB

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.
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.
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.
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:
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 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:
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:
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:
postgres=# UPDATE pg_database SET datistemplate = 'false' WHERE datname = 'template1'; UPDATE 1

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:
postgres=# DROP DATABASE template1; DROP DATABASE

# CREATE DATABASE template1 TEMPLATE template0; CREATE DATABASE
Now pg_database needs to be updated so template1 will be used as a template again:
postgres=# UPDATE pg_database SET datistemplate = 'true' WHERE datname = 'template1'; UPDATE 1

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.