Not everyone knows, but at since PostgreSQL 9.6, we have some built-in roles.
Of course, there is always superuser (usually called postgres), but I'm not talking about it. I'm talking about magical roles that have names starting with pg_.
Tag: security
Waiting for PostgreSQL 15 – Add support for security invoker views.
On 22nd of March 2022, Dean Rasheed committed patch:
Add support for security invoker views. A security invoker view checks permissions for accessing its underlying base relations using the privileges of the user of the view, rather than the privileges of the view owner. Additionally, if any of the base relations are tables with RLS enabled, the policies of the user of the view are applied, rather than those of the view owner. This allows views to be defined without giving away additional privileges on the underlying base relations, and matches a similar feature available in other database systems. It also allows views to operate more naturally with RLS, without affecting the assignments of policies to users. Christoph Heiss, with some additional hacking by me. Reviewed by Laurenz Albe and Wolfgang Walther. Discussion: https://postgr.es/m/b66dd6d6-ad3e-c6f2-8b90-47be773da240%40cybertec.at
Continue reading Waiting for PostgreSQL 15 – Add support for security invoker views.
Waiting for PostgreSQL 15 – Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.
On 10th of September 2021, Noah Misch committed patch:
Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner. This switches the default ACL to what the documentation has recommended since CVE-2018-1058. Upgrades will carry forward any old ownership and ACL. Sites that declined the 2018 recommendation should take a fresh look. Recipes for commissioning a new database cluster from scratch may need to create a schema, grant more privileges, etc. Out-of-tree test suites may require such updates. Reviewed by Peter Eisentraut. Discussion: https://postgr.es/m/20201031163518.GB4039133@rfd.leadboat.com
How to setup SSL connections and authentication?
I had to setup it relatively recently, and hit some roadblocks, so figured I'll write about my experiences – for myself in the future, or for anyone else that might want to set it up.
Continue reading How to setup SSL connections and authentication?
Waiting for 9.5 – Row-Level Security Policies (RLS)
On 19th of September, Stephen Frost committed patch:
Row-Level Security Policies (RLS) Building on the updatable security-barrier views work, add the ability to define policies on tables to limit the set of rows which are returned from a query and which are allowed to be added to a table. Expressions defined by the policy for filtering are added to the security barrier quals of the query, while expressions defined to check records being added to a table are added to the with-check options of the query. New top-level commands are CREATE/ALTER/DROP POLICY and are controlled by the table owner. Row Security is able to be enabled and disabled by the owner on a per-table basis using ALTER TABLE .. ENABLE/DISABLE ROW SECURITY. Per discussion, ROW SECURITY is disabled on tables by default and must be enabled for policies on the table to be used. If no policies exist on a table with ROW SECURITY enabled, a default-deny policy is used and no records will be visible. By default, row security is applied at all times except for the table owner and the superuser. A new GUC, row_security, is added which can be set to ON, OFF, or FORCE. When set to FORCE, row security will be applied even for the table owner and superusers. When set to OFF, row security will be disabled when allowed and an error will be thrown if the user does not have rights to bypass row security. Per discussion, pg_dump sets row_security = OFF by default to ensure that exports and backups will have all data in the table or will error if there are insufficient privileges to bypass row security. A new option has been added to pg_dump, --enable-row-security, to ask pg_dump to export with row security enabled. A new role capability, BYPASSRLS, which can only be set by the superuser, is added to allow other users to be able to bypass row security using row_security = OFF. Many thanks to the various individuals who have helped with the design, particularly Robert Haas for his feedback. Authors include Craig Ringer, KaiGai Kohei, Adam Brightwell, Dean Rasheed, with additional changes and rework by me. Reviewers have included all of the above, Greg Smith, Jeff McCormick, and Robert Haas.
Continue reading Waiting for 9.5 – Row-Level Security Policies (RLS)
Waiting for 9.1 – sepgsql
On 24th of January, Robert Haas committed very important patch:
sepgsql, an SE-Linux integration for PostgreSQL This is still pretty rough - among other things, the documentation needs work, and the messages need a visit from the style police - but this gets the basic framework in place. KaiGai Kohei
Now, In this place I usually show you what it is all about, but it's not going to happen with this patch. Reason is very simple – I know that it's important, and that it integrates PostgreSQL with SE/Linux, but since I never played with SE/Linux – I cannot really show you anything in here.
But – if you know anything about SE/Linux and sepgsql, I would like to ask you to describe this extension so that even I could understand it.
Sorry – I know I should put more in here, but it simply is so far above my knowledge limits, it would take me too long time to get through documentation for SE/Linux and sepgsql.
For now – if you want to know more – simply check the docs.
Waiting for 9.1 – auth_delay
On 27th of November, Robert Haas committed patch which adds new, interesting contrib module:
New contrib module, auth_delay. KaiGai Kohei, with a few changes by me.