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 ] ...
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)
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;
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.
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