Postgresql – Aurora postgres grant permission to pg_attribute

aurorapostgresqltemporary-tables

In the process of migration from local postgres version to and RDS aurora postgres version. I'm using temporal tables and do for that reason have temporal tables that are inhirited from the origin. For example students and student_history, where the history table store the historical stages of the students.

When deleting a column in students, the corresponding column does not delete in students_history.
Which c
I clever college of mine(who found a better job) found a solution of changing the 'pg_attribute.attislocal'(see script below) for the table which solved the problem.

UPDATE pg_attribute
SET attislocal=false
WHERE attrelid = '{parent_table_name}_history'::regclass
AND NOT attisdropped
AND attnum > 0
AND attinhcount > 0;

So I run the script user the super user account I get:

permission denied for relation pg_attribute

So how do I grant he super user permissions to change the pg_attribute or is there anything else I'm overlooking?

Best Answer

Only a superuser can do that, and you probably won't get a superuser in a hosted database.

You will have to drop the history tables and re-create them new as inheritance children (without specifying the duplicate columns!).

1. Move sequence (my preference)

to the public schema - or any schema with sufficient privileges:

GRANT USAGE ON SCHEMA public TO public; -- or: my_group

Moving the sequence to another schema is easy:

ALTER SEQUENCE schema_a.tbl_tbl_id_seq SET SCHEMA public;

Now you can grant USAGE:

GRANT USAGE ON SEQUENCE public.tbl_tbl_id_seq TO public; -- or: my_group

That preserves all references (incl. column defaults).

2. Function wrapper with `SECURITY DEFINER`

If you cannot move the sequence for some reason (can't think of one), you can alternatively wrap access to it in functions of your own with SECURITY DEFINER. Again, create those functions in the public schema (or any schema with sufficient privileges for your users):

CREATE OR REPLACE FUNCTION public.next_tbl_tbl_id_seq()
  RETURNS bigint AS
$func$
SELECT nextval('schema_a.tbl_tbl_id_seq'::regclass)
$func$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = schema_a, pg_temp;
ALTER FUNCTION shop.f_deswap_name(text) OWNER TO owning_role;

Where owning_role is the owner of the sequence or any role with sufficient privileges. Similar function for currval() ...

Be sure to read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.

PostgreSQL JSON Field for Many-to-Many Relationships

would it be better to use Postgres' JSON functionality to store my data something like this:

No.

Are there any downsides to an approach like this?

GIN / GiST indexes on jsonb are bigger and slower than b-tree indexes. Additionally, whenever you update a field it must be rewritten, you can't just insert an extra element inside the json document in the row.

My concern is that since every move is creating a new entry into the junction table -- and there are potentially hundreds of stages a lead can go through -- it may lead to hundreds of thousands of rows for very few leads and that could probably impact the lookup time when I'm trying to fetch a specific set.

This is what RDBMSes are good at.

Use the conventional relational modelling for this unless you have a really good reason not to.

Best Answer

Related Solutions

PostgreSQL 9.3 – Permission for Sequence in Another Schema

1. Move sequence (my preference)

2. Function wrapper with SECURITY DEFINER

PostgreSQL JSON Field for Many-to-Many Relationships

Related Question

2. Function wrapper with `SECURITY DEFINER`