PostgreSQL security levels

PostgreSQL has different security levels defined on PostgreSQL objects, including tablespace, database, schema, table, foreign data wrapper, sequence, domain, language, and large object. One can have a peek into different privileges by running the h meta command in psql, as follows:

car_portal=> h GRANT
Command:     GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
...

Database security level

To disallow users from connecting to the database by default, one needs to revoke the default database permissions from public, as follows:

$ psql -h localhost -U postgres -d car_portal
car_portal=# REVOKE ALL ON DATABASE car_portal FROM public;
REVOKE
car_portal=# q

$ psql -h localhost -U web_app -d car_portal
psql: FATAL:  permission denied for database "car_portal"
DETAIL:  User does not have CONNECT privilege.

To allow the user to connect to the database, the connect permissions should be granted explicitly, as follows:

$ psql -h localhost -U postgres -d car_portal
car_portal=# GRANT CONNECT ON DATABASE car_portal TO web_app;
GRANT
car_portal=# q

$ psql -h localhost -U web_app -d car_portal
car_portal-> l car_portal
                                                  List of databases
    Name    |     Owner      | Encoding |          Collate           |           Ctype            |         Access privileges
------------+----------------+----------+----------------------------+----------------------------+-----------------------------------
 car_portal | car_portal_app | UTF8     | English_United States.1252 | English_United States.1252 | car_portal_app=CTc/car_portal_app+
            |                |          |                            |                            | web_app=c/car_portal_app
(1 row)

One also could revoke the default permissions from template databases to ensure that all newly created databases don't allow users to connect by default.

If the database permissions are empty when using the l meta command, this indicates that the database has the default permissions; execute the following query:

$ psql -h localhost -U postgres -d car_portal

car_portal=# CREATE DATABASE test;
CREATE DATABASE
car_portal=# l test
                                             List of databases
 Name |  Owner   | Encoding |           Collate           |            Ctype            | Access privileges
------+----------+----------+-----------------------------+-----------------------------+-------------------
 test | postgres | UTF8     | English_United Kingdom.1252 | English_United Kingdom.1252 |
(1 row)

Schema security level

The Grant synopsis for schema is as follows:

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

To allow a user access to a certain schema, the usage permissions should be granted, as seen in the preceding and following examples:

GRANT USAGE ON SCHEMA car_portal_app TO web_app, public_user;

Table-level security

The table permissions are INSERT, UPDATE, DELETE, TRIGGER, REFERENCE, and TRUNCATE. Also, one could use the keyword ALL to grant all privileges at once, as follows:

GRANT ALL ON <table_name> TO <role>;

Apart from this, one could use a comma-separated list for both tables and roles or even grant permissions on all relations in a schema to a certain role.

Column-level security

PostgresSQL allows permissions to be defined on the column level. To explore this feature, let's create a table and role, as follows:

car_portal=# CREATE TABLE test_column_acl AS SELECT * FROM (values (1,2), (3,4)) as n(f1, f2);
SELECT 2
car_portal=# CREATE ROLE test_column_acl;
CREATE ROLE
car_portal=# GRANT SELECT (f1) ON test_column_acl TO test_column_acl;
GRANT

--We have revoked default permission on public in previous examples
car_portal=# GRANT USAGE ON SCHEMA public TO test_column_acl;
GRANT
car_portal=# SET ROLE test_column_acl;
SET
car_portal=> SELECT * FROM public.test_column_acl;
ERROR:  permission denied for relation test_column_acl
car_portal=> SELECT f1 FROM public.test_column_acl;
 f1
----
  1
  3
(2 rows)
car_portal=> x
Expanded display is on.
car_portal=> dp public.test_column_acl
Access privileges
-[ RECORD 1 ]------------+-----------------------------
Schema                   | public
Name                     | test_column_acl
Type                     | table
Access privileges        |
Column access privileges | f1:
                         |   test_column_acl=r/postgres

Row-level security

Currently, row-level security is not supported by postgres; however, one could use views to define row-level security on data. Row-level security will be supported in the upcoming version of PostgreSQL 9.5.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.128.171.243