Let's start with a role and a database. For the role to be able to connect, it must has:
First let's look at the pg_hba.conf file (commented out lines are removed for brevity):
postgres$ egrep '^[^#]' /etc/postgresql/11/alpha/pg_hba.conf
local all postgres trust local all all trust host all all 127.0.0.1/32 md5 host all all ::1/128 md5 local replication all trust host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5
(Depending on the distribution package the contents may differ.)
We will be using TCP/IP connection (host) and password authentication, so no changes are required.
Let's create a role (Alice) and a database. The role executing the commands is very important in this lesson, so the name of the current role is shown in the prompt instead of a server name.
student=# CREATE ROLE alice LOGIN PASSWORD 'alicepass';
CREATE ROLE
student=# CREATE DATABASE access_overview;
CREATE DATABASE
What about the CONNECT privilege? We can check it with the following function:
student=# SELECT has_database_privilege('alice','access_overview','connect');
has_database_privilege ------------------------ t (1 row)
The privilege is already granted, but why? We have just created Alice and the database and haven't granted any privileges.
Let's check access privileged for the database:
student=# \l access_overview
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+---------+----------+-------------+-------------+-------------------
access_overview | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
The corresponding field is empty. This is a bit inconvenient: empty value denotes the default privileges, but we can only guess what these privileges actually are.
We'll cheat a little: let's grant and revoke some database privilege which Alice didn't have:
student=# GRANT CONNECT ON DATABASE access_overview TO alice;
GRANT
student=# REVOKE CONNECT ON DATABASE access_overview FROM alice;
REVOKE
And now we can see what was previously hidden:
student=# \l access_overview
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+---------+----------+-------------+-------------+---------------------
access_overview | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/student +
| | | | | student=CTc/student
(1 row)
The privileges are shown in the format: grantee=privileges/grantor. Each privilege is abbreviated to a single character like this:
When grantee is empty, the public pseudorole is assumed.
As we can see above, the database owner has full access to it. And also the public role can connect to the database and can create temporary objects in it. These are the privileges that are granted automatically to the public role upon creation of a new database.
Now it's clear why Alice have the CONNECT privilege: it was inherited from the public group role. Let's connect to the database:
student=# \c "host=localhost user=alice dbname=access_overview password=alicepass"
You are now connected to database "access_overview" as user "alice" on host "localhost" at port "5432".
Now let Alice create a schema of the same name, along with several objects in it.
alice=> CREATE SCHEMA alice;
ERROR: permission denied for database access_overview
What happened?
Alice has no privilege to create schemas in the database. Let's grant it:
alice=> \c "dbname=access_overview"
You are now connected to database "access_overview" as user "student" via socket in "/var/run/postgresql" at port "5432".
student=# GRANT CREATE ON DATABASE access_overview TO alice;
GRANT
Let's try again:
student=# \c "host=localhost user=alice dbname=access_overview password=alicepass"
You are now connected to database "access_overview" as user "alice" on host "localhost" at port "5432".
alice=> CREATE SCHEMA alice;
CREATE SCHEMA
Now, as Alice is the owner of the schema, she has all the privileges for it and can create any objects in it. By default this schema will be used for new objects, as specified in the search path:
alice=> SELECT current_schemas(true);
current_schemas
---------------------------
{pg_catalog,alice,public}
(1 row)
Now Alice creates two tables.
alice=> CREATE TABLE t1(n numeric);
CREATE TABLE
alice=> INSERT INTO t1 VALUES (1);
INSERT 0 1
alice=> CREATE TABLE t2(n numeric, who text DEFAULT current_user);
CREATE TABLE
alice=> INSERT INTO t2(n) VALUES (1);
INSERT 0 1
Now let's create another role for Bob. He'll be trying to get access to the Alice's objects.
alice=> \c "dbname=access_overview"
You are now connected to database "access_overview" as user "student" via socket in "/var/run/postgresql" at port "5432".
student=# CREATE ROLE bob LOGIN PASSWORD 'bobpass';
CREATE ROLE
student=# \c "host=localhost user=bob dbname=access_overview password=bobpass"
You are now connected to database "access_overview" as user "bob" on host "localhost" at port "5432".
Bob tries to query the t1 table.
bob=> SELECT * FROM alice.t1;
ERROR: permission denied for schema alice
LINE 1: SELECT * FROM alice.t1;
^
What's the matter?
Bob has no access to the schema as he is neither a superuser nor the owner.
bob=> \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
alice | alice | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(2 rows)
The format of the access privileges is familiar. The only new privilege here is:
By the way we can see that the public pseudorole has access to the public schema.
Alice, as the owner of the schema, cat grant access to it to Bob.
student=# \c "host=localhost user=alice dbname=access_overview password=alicepass"
You are now connected to database "access_overview" as user "alice" on host "localhost" at port "5432".
alice=> GRANT CREATE, USAGE ON SCHEMA alice TO bob;
GRANT
Bob tries to query the table again:
bob=> SELECT * FROM alice.t1;
ERROR: permission denied for table t1
What's up now?
Bob has access to the schema but has no privilege for the table.
bob=> \dp alice.t1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
alice | t1 | table | | |
(1 row)
Again we can see the empty field which denotes the default privileges. In this case this means that the owner has all available privileges and no one else has any privileges.
Alice can grant some privileges to Bob for the first table:
alice=> GRANT SELECT,UPDATE ON alice.t1 TO bob;
GRANT
And some other for the second table. Note that SELECT privilege is granted for a single column only:
alice=> GRANT SELECT(n),INSERT ON alice.t2 TO bob;
GRANT
Let's see how the privileges have changed:
alice=> \dp alice.*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
alice | t1 | table | alice=arwdDxt/alice+| |
| | | bob=rw/alice | |
alice | t2 | table | alice=arwdDxt/alice+| n: +|
| | | bob=a/alice | bob=r/alice |
(2 rows)
New abbreviations (not so obvious) are as follows:
Column privileges are shown separately from the table privileges.
And finally Bob has managed to access the tables.
bob=> UPDATE alice.t1 SET n = n + 1;
UPDATE 1
bob=> SELECT * FROM alice.t1;
n --- 2 (1 row)
Not permitted operations are still not possible:
bob=> DELETE FROM alice.t1;
ERROR: permission denied for table t1
And for the second table:
bob=> INSERT INTO alice.t2(n) VALUES (100);
INSERT 0 1
bob=> SELECT n FROM alice.t2;
n ----- 1 100 (2 rows)
Bob still cannot access another column:
bob=> SELECT * FROM alice.t2;
ERROR: permission denied for table t2
The only privilege for functions is the EXECUTE. Let's create a function:
alice=> CREATE FUNCTION f() RETURNS integer AS $$ SELECT count(*)::integer FROM t2; $$ VOLATILE LANGUAGE SQL;
CREATE FUNCTION
Note that the public pseudorole is automatically granted the privilege for any newly created function. That's why Bob can execute the function recently created by Alice.
This looks like a security hole. It is compensated by the fact that the function executes with the privileges by its invoker, using the invoker's search path. This is the default behavior (SECURITY INVOKER).
bob=> SELECT alice.f();
ERROR: relation "t2" does not exist
LINE 2: SELECT count(*)::integer FROM t2;
^
QUERY:
SELECT count(*)::integer FROM t2;
CONTEXT: SQL function "f" during inlining
That's why Bob cannot access objects he hasn't privileges for.
This makes it possible to write kind of a "generic" function. If Bob creates his own t2 table, the function will work with the Bob's table for Bob, and with the Alice's table for Alice:
bob=> CREATE TABLE t2(n numeric);
CREATE TABLE
bob=> SELECT alice.f();
f --- 0 (1 row)
alice=> SELECT alice.f();
f --- 2 (1 row)
Another possibility is to create function as SECURITY DEFINER, which will work with its creator's privileges:
alice=> CREATE OR REPLACE FUNCTION f() RETURNS integer AS $$ SELECT count(*)::integer FROM t2; $$ SECURITY DEFINER VOLATILE LANGUAGE SQL;
CREATE FUNCTION
In this case the function works using the search path of Alice despite who invokes it:
bob=> SELECT alice.f();
f --- 2 (1 row)
alice=> SELECT alice.f();
f --- 2 (1 row)
When using SECURITY DEFINER you have to watch carefully for granted privileges. Probably the EXECUTE privilege must be revoked from the public, and should be granted to the required roles only.
alice=> REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA alice FROM public;
REVOKE
bob=> SELECT alice.f();
ERROR: permission denied for function f
It is all complicated by the fact that the EXECUTE privilege is granted automatically to the public for each newly created function, and this cannot be changed.
alice=> CREATE FUNCTION f_new() RETURNS integer AS $$ SELECT 1; $$ LANGUAGE SQL;
CREATE FUNCTION
bob=> SELECT alice.f_new();
f_new
-------
1
(1 row)
Nevertheless you can automatically revoke this privilege by the "default privileges" system:
alice=> ALTER DEFAULT PRIVILEGES FOR ROLE alice REVOKE EXECUTE ON FUNCTIONS FROM public;
ALTER DEFAULT PRIVILEGES
alice=> \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+----------+-------------------
alice | | function | alice=X/alice
(1 row)
alice=> DROP FUNCTION f_new();
DROP FUNCTION
alice=> CREATE FUNCTION f_new() RETURNS integer AS $$ SELECT 1; $$ LANGUAGE SQL;
CREATE FUNCTION
bob=> SELECT alice.f_new();
ERROR: permission denied for function f_new
Row security policies allows to restrict access on per-row basis depending on the current role.
alice=> SELECT * FROM alice.t2;
n | who -----+------- 1 | alice 100 | bob (2 rows)
For an example we'll arrange so that a role may see only its own rows, that is the rows with the "who" field equals to the name of the role.
alice=> CREATE POLICY who_policy ON alice.t2 USING (who = current_user);
CREATE POLICY
The policy must be enabled to take effect:
alice=> ALTER TABLE alice.t2 ENABLE ROW LEVEL SECURITY;
ALTER TABLE
Now Bob can see only his own rows. Basically each row of the SQL statement result set is checked for the predicate defined by the policy.
bob=> SELECT n FROM alice.t2;
n ----- 100 (1 row)
bob=> INSERT INTO alice.t2(n) VALUES (101);
INSERT 0 1
bob=> SELECT n FROM alice.t2;
n ----- 100 101 (2 rows)
Table's owner is not affected by the policies by default:
alice=> SELECT * FROM alice.t2;
n | who -----+------- 1 | alice 100 | bob 101 | bob (3 rows)
Although Alice can restrict herself:
alice=> ALTER TABLE alice.t2 FORCE ROW LEVEL SECURITY;
ALTER TABLE
alice=> SELECT * FROM alice.t2;
n | who ---+------- 1 | alice (1 row)
The end of demonstration.