From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:
The DELETE and UPDATE triggers need to be on the child tables. An operation on a child doesn't fire the triggers of the parent.
As such, the following code must also be executed:
CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
FOR EACH ROW EXECUTE PROCEDURE audit_delete();
The audit_delete()
function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp
. Instead, assuming that the table has a primary key field of id
:
CREATE OR REPLACE FUNCTION audit_delete()
RETURNS trigger AS
$BODY$
BEGIN
EXECUTE
'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 1;
Basic Role and User Management
By default, PostgreSQL stores encrypted passwords:
create user u_one with password 'password';
select *
from pg_authid
where rolname = 'u_one';
-[ RECORD 1 ]--+------------------------------------
rolname | u_one
rolsuper | f
rolinherit | t
rolcreaterole | f
rolcreatedb | f
rolcatupdate | f
rolcanlogin | t
rolreplication | f
rolconnlimit | -1
rolpassword | md54e83b97ec0bf59917d9d2689a961e390
rolvaliduntil |
for some testing:
create table tt (
a serial primary key,
v varchar
);
insert into tt (v)
values ('one'), ('two');
the goal is to create one user who has read-only access and one user who has all privileges. It is better to create roles that have the privileges defined and then attach users to each of those roles:
create role readonly;
create role readwrite;
grant select on all tables in schema public to readonly;
grant insert, update, delete on table tt to readwrite;
In my example, I would like u_one to have read-only access. This user does not yet:
select *
from tt;
ERROR: permission denied for relation tt
I'm going to attach u_one to read-only:
grant readonly to u_one;
And now u_one can select from table tt.
Now I'll create a u_two who is readwrite:
create user u_two with password 'password';
grant readwrite to u_two;
you probably meant to allow u_two select privileges as well:
grant readonly to u_two;
When you attempt to insert into tt using u_two:
insert into tt (v) values ('three');
ERROR: permission denied for sequence tt_a_seq
You need to add insert, update, and/or delete privileges to the users who are going to modify this sequence:
grant usage on tt_a_seq to readwrite;
Notice how I only added this to the role, and not the user: u_two is now able to insert a new record.
Using this role / user strategy will give you considerable flexibility by reducing duplicate permissions and allowing you to quickly create and set up new users.
The HBA config file:
By default, all users will be able to login from localhost, and as long as that is true, you won't have to make changes to pg_hba.conf. You will need to make changes if you want a user to be able to access the database from another ip-address (remote computer).
If you are going to grant remote access:
DATABASE: host --
USER: the username
ADDRESS: the ip address the user is connecting from
(I doubt this will work with roles since roles do not have passwords,
though I've never tried either)
(if you want the user to be able to connect from any IP, then use
0.0.0.0/0)
METHOD: md5
PostgreSQL docs on creating roles and user syntax.
http://www.postgresql.org/docs/current/static/sql-grant.html
Granting privileges to sequences:
https://stackoverflow.com/questions/9325017/error-permission-denied-for-sequence-cities-id-seq-using-postgres
PostgreSQL docs on hba:
http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html
Best Answer
Plans are invalidated if any object that they use suffers “cache invalidation”. Compare this source comment:
In the case of tables:
on the PostgreSQL source, you can find which changes involving tables cause plans to be invalidated:
REINDEX
CLUSTER
andVACUUM (FULL)
ALTER TABLE
TRUNCATE
Other actions that lead to any such action will of course also invalidate plans.