By default, PostgreSQL users—also known as roles with login option—can access the public schema. Also, note that the default PostgreSQL authentication policy allows users to access all databases from the localhost using peer authentication on a Linux system. Also, a user can create objects in the public schema of any database he/she can access; for example, the user can create a function and execute it in the public schema. In addition to this, the user can alter some settings.
The user cannot access other user objects in the public schema or create databases and schemas. However, the user can sniff data about the database objects by querying the system catalog. Unprivileged users can get information about other users, table structure, table owner, some table statistics, and so on. The following example shows how the user test is able to get information about table a, which is owned by a postgres user:
test=> SELECT * FROM a; ERROR: permission denied for relation a test=> SELECT current_user; current_user -------------- test (1 row) test=> SELECT tableowner FROM pg_tables WHERE tablename ='a'; tableowner ------------ postgres (1 row) test=> d a Table "public.a" Column | Type | Modifiers --------+---------+----------- num | integer | test=> x Expanded display is on. test=> test=> select * from pg_stat_user_tables ; -[ RECORD 1 ]-----+------------------------------ relid | 16386 schemaname | public relname | a seq_scan | 0 seq_tup_read | 0 idx_scan | idx_tup_fetch | n_tup_ins | 201 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 201 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | 2015-06-07 22:48:59.311121+02 last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 3 autoanalyze_count | 0 test=> x Expanded display is off. test=> SELECT * FROM pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+---------+----------+----------+----------- postgres | 10 | t | t | t | t | ******** | | test | 16385 | f | f | f | f | ******** | | (2 rows)
Also, the user can access functions that are created in the public schema by other users as long as this function does not access objects that the user cannot access:
postgres=# SELECT current_user; current_user -------------- postgres (1 row) postgres=# CREATE OR REPLACE FUNCTION b () RETURNS text AS $$ SELECT 'Hello'::text $$ LANGUAGE sql ; CREATE FUNCTION postgres=# SET ROLE test; SET postgres=> SELECT * FROM b(); b ------- Hello (1 row)
For mistrusted languages, such as plpythonu, the user cannot create functions unless he/she is a superuser. If anyone who is not a superuser tries to create a function using C language or plpythonu, he/she will get the following error:
postgres=> CREATE FUNCTION min (a integer, b integer) RETURNS integer AS $$ if a < b: return a else: return b $$ LANGUAGE plpythonu; ERROR: permission denied for language plpythonu
To prevent the user from accessing the public schema, the public schema privileges should be revoked, as follows:
test=# SELECT session_user; session_user -------------- postgres (1 row) test=# REVOKE ALL PRIVILEGES ON SCHEMA PUBLIC FROM public; REVOKE test=# set role test; SET test=> create table b(); ERROR: no schema has been selected to create in
Note that the user test has explicit privileges on the public schema; the user inherits these privileges from the public role.
For views, the view owner cannot execute a view unless he/she has permission to access the base tables used in the view, as shown in the following example:
car_portal=# CREATE TABLE table1 AS SELECT 1 As f1; SELECT * FROM view_table1; SELECT 1 car_portal=# CREATE VIEW view_table1 AS SELECT f1 FROM table1; CREATE VIEW car_portal=# CREATE ROLE test_view_role; CREATE ROLE car_portal=# ALTER TABLE view_table1 OWNER TO test_view_role; ALTER TABLE car_portal=# SET ROLE test_view_role; SET car_portal=> SELECT * FROM view_table1; ERROR: permission denied for relation table1
Often, when designing an application, a user is used to configure database connections and connection tools. Another level of security needs to be implemented to ensure that the user who uses the application is authorized to perform a certain task. This logic is often implemented in application business logic. Also, the database's role system can be used to partially implement this logic by delegating the authentication to another role after the connection is established or reused using the SET SESSION AUTHORIZATION user_name statement or SET ROLE command in a transaction block.
The SET role requires a role membership, while SET SESSION AUTHORISATION requires superuser privileges. Allowing an application to connect as a superuser is dangerous because the SET SESSION and SET ROLE commands can be reset using the RESET ROLE and RESET SESSION commands, respectively, allowing the application to gain superuser privileges.
To understand how the PostgreSQL role system can be used to implement authentication and authorization, we will use the role system in the car portal app. In the car portal application, several groups of users can be classified as web_app, public_user, registered_user , seller, and admin. The
web_app user is used to configure business logic connection tools; the public user, registered_user, and seller are used to distinguish users. The
public_user group can access only public information, such as advertisements, but cannot add ratings as registered users nor create advertisements as seller. Admin is a superrole to manage all of the application's content, such as filtering out spams and deleting the users that do not adhere to the website's policies. When the car web portal application connects to the database, the web_app user is used. After this, car_portal invokes the SET ROLE command based on the user class. This authentication method is known as proxy authentication.
The following examples demonstrate how a role system can be used to implement proxy authentication. The first step is to create roles and assign role memberships and privileges:
CREATE ROLE web_app LOGIN NOINHERIT; CREATE ROLE public_user NOLOGIN; GRANT SELECT ON car_portal_app.advertisement_picture, car_portal_app.advertisement_rating , car_portal_app.advertisement TO public_user; GRANT public_user TO web_app; GRANT USAGE ON SCHEMA car_portal_app TO web_app, public_user;
The NOINHERIT option for the web_app user does not allow the user to inherit the permissions of role membership; however, web_app can change the role to public user, as in the following example:
$ psql -h localhost -U web_app -d car_portal car_portal=> SELECT session_user, current_user; session_user | current_user --------------+-------------- web_app | web_app (1 row) car_portal=> SELECT * FROM car_portal_app.advertisement; ERROR: permission denied for relation advertisement car_portal=> SET ROLE public_user; SET car_portal=> SELECT * FROM car_portal_app.advertisement; advertisement_id | advertisement_date | car_id | seller_account_id ------------------+--------------------+--------+------------------- (0 rows) car_portal=> SELECT session_user, current_user; session_user | current_user --------------+-------------- web_app | public_user (1 row)