I am a relatively new user of Postgres 13. Let me first tell you, that the Postgres database is hosted on AWS aurora. I have a user that owns a schema and I have a specific table that this user should only be able to SELECT and INSERT rows to this table and execute TRIGGERS.
I have REVOKED ALL on this table for this user and GRANTED SELECT, INSERT, TRIGGER ON TABLE TO USER. The INSERT, SELECT, and TRIGGER work as expected. However, when I execute a SQL UPDATE on that table it still lets me update a row in that table! I also forgot to tell you I REVOKED ALL and performed the same GRANTS to rds_superuser on this table since this user is referenced to rds_superuser.
Any help would be greatly appreciated!
Following are the results of \d:
Column | Type | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+------------------------
id | uuid | | not null | uuid_generate_v4()
patient_medication_id | bigint | | not null |
raw_xml | character varying | | not null |
digital_signature | character varying | | not null |
create_date | timestamp with time zone | | not null | CURRENT_TIMESTAMP
created_by | character varying | | |
update_date | timestamp with time zone | | |
updated_by | character varying | | |
deleted_at | timestamp with time zone | | |
deleted_by | character varying | | |
status | character varying(1) | | | 'A'::character varying
message_type | character varying | | not null |
Indexes:
"rx_cryptographic_signature_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"rx_cryptographic_signature_fk" FOREIGN KEY (patient_medication_id) REFERENCES tryon.patient_medication(id)
Triggers:
audit_trigger_row AFTER INSERT ON tryon.rx_cryptographic_signature FOR EACH ROW EXECUTE FUNCTION td_audit.if_changed_fn('id', '{}')
audit_trigger_stm AFTER TRUNCATE ON tryon.rx_cryptographic_signature FOR EACH STATEMENT EXECUTE FUNCTION td_audit.if_changed_fn() tr_log_delete_attempt BEFORE DELETE ON tryon.rx_cryptographic_signature FOR EACH STATEMENT EXECUTE FUNCTION tryon.fn_log_update_delete_attempt()
tr_log_update_attempt BEFORE UPDATE ON tryon.rx_cryptographic_signature FOR EACH STATEMENT EXECUTE FUNCTION tryon.fn_log_update_delete_attempt()
Following are the results of \z:
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+----------------------------+-------+---------------------------------------+-------------------+----------
tryon | rx_cryptographic_signature | table | TD_Administrator=art/TD_Administrator+| |
| | | td_administrator=art/TD_Administrator+| |
| | | rds_pgaudit=art/TD_Administrator +| |
| | | rds_superuser=art/TD_Administrator | |
(1 row)
Thanks so much for your help!!