Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1er91n-0003gf-48 for pgsql-admin@arkaria.postgresql.org; Wed, 28 Feb 2018 21:12:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1er91l-0006zP-Px for pgsql-admin@arkaria.postgresql.org; Wed, 28 Feb 2018 21:12:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1er91l-0006zG-El for pgsql-admin@lists.postgresql.org; Wed, 28 Feb 2018 21:12:41 +0000 Received: from mail-ot0-x229.google.com ([2607:f8b0:4003:c0f::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1er91i-0002JA-Fq for pgsql-admin@lists.postgresql.org; Wed, 28 Feb 2018 21:12:40 +0000 Received: by mail-ot0-x229.google.com with SMTP id t2so3604269otj.4 for ; Wed, 28 Feb 2018 13:12:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgsqlrocket-com.20150623.gappssmtp.com; s=20150623; h=to:from:subject:message-id:date:user-agent:mime-version :content-transfer-encoding:content-language; bh=f0x8/sukL9e3Fd81HI+DVw4uNWP2mWSJ0NxriiFVzNk=; b=xTYCxebMcBiEQBoNp4nRuFydxe/GPE3LT7sYwymR3tlMX5HNrp2eFMxvka/tBJK1sw tAvx+6iUbpNnnz94yjltP0M3bl0jH1wsa5qfl2oa+dhNtcdpHOxFUXrNVCcArXNlDNXE JK9kEhTUTkQFjWZsGAGHhhD4zCHYYS61aRoFu8rKEsh7XEWC/Pt6LTmEW8vePGTa4C0p zWM5u71QucOHQYwcj41pGvDVVMuo/lHQ4PiE1IfHRLE+OIpL6dFYmmapLR8AkmmecuZ2 8OimbvytNnTvDm5oo3gz3hR5OlXBjbIGRsMOEWbJvBSMNBaSiRt3aC2FmZnSBNPT93ZX E9Lg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:to:from:subject:message-id:date:user-agent :mime-version:content-transfer-encoding:content-language; bh=f0x8/sukL9e3Fd81HI+DVw4uNWP2mWSJ0NxriiFVzNk=; b=n/2vlPTWmQGkPL2xd8A0JXSt33D/RuOXh1i1TmiezdLCrqBD2+fUzGfG9Y1AbFS9ts KDn/YfVYH6MmTF2ynK6upJOiBUdnEL/q0xhtMJyB3/nzf43rkAjxzmtTZBX8jhKdcxR4 or43DKppjqDlOqUNV4tCo7AwHnyLrRBiQ6eOjYO74xjvN2FY7Xk/O1bbz7xjFKCkzug6 V4JknHLV2aBRQhANvjIQ8HqThBDSqVc9PhU6X7wqetCGfKoAwEC9G1G60fcn8vF+Z5k6 N4O/GOC853ElE9TUnb20uGSeMFgNBfFDADBU0KY5VnqbIzRtOJuBx+y/u0Q41CemQe3w K5Vg== X-Gm-Message-State: APf1xPA0sG/tS8eQOH2IGnf0KeCcZ0+qbp0x7lLxOYOyPE41B8bMumy+ 8nMw+TSny7VQKjexde8mgdjP9da4RCw= X-Google-Smtp-Source: AG47ELsHo+qX7h9Hu/piq/lrxtQg8pHuubQreCVjAMLRRlite3SbL5ZT7dFhiN/pZ5OP6rqETCx3cQ== X-Received: by 10.157.43.107 with SMTP id f40mr13855011otd.162.1519852356113; Wed, 28 Feb 2018 13:12:36 -0800 (PST) Received: from localhost.localdomain ([65.132.155.214]) by smtp.gmail.com with ESMTPSA id q8sm787732oig.30.2018.02.28.13.12.35 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 28 Feb 2018 13:12:35 -0800 (PST) To: "pgsql-admin@lists.postgresql.org" From: chris Subject: audit table with permissions Message-ID: Date: Wed, 28 Feb 2018 14:12:34 -0700 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.6.0 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk I created functions and a trigger to audit tables on my database but I have a problem with permissions on the functions and function schema I created. I can grant permissions to the current user of the original table being audited but I need a way to automate grants and revokes made to the original table so that any time those change in the future I don't have to worry about updating permissions or leaving a hole in security by granting all users full permissions to the functions and function schema. From my understanding event triggers can't be set the grant/revoke only create, alter or drop. Any input would really help me, I'm very fresh to writing functions and triggers. Thanks, Chris