2

I have postgres 9.3 being installed with my windows desktop app on computers all over the place. I don't want Joe Script-kiddy looking at the pgpass.conf, getting the postgres password a running amuck in my Database.

I was able to to make a backup role using this article but I ran out of luck with reindexdb and Vacuum Analyze.

So far my solution is to write an application that will take an encrypted vbscript file and use ado to connect and run.

I've read over the PG help on grant and role and I just can't see how to do this. I'd appreciate any ideas.

Neil McGuigan suggestion worked for reindex and analyze. I need to reindex each table in a loop though. If I try just to reindex database myDb I get EINDEX DATABASE cannot be executed from a function or multi-command string. I get the same error with vacuum [table]

I'm still playing around with dblink as a possible solution. .. Well I can't use dblink because it wants the password so I'd have to code the password into the function and since my backup role has select it can view the contents of the function.

So I've got a low privileged role, backup, that can backup, analyze and reindex but cannot vacuum. seems strange to me.

Community
  • 1
  • 1
Ron H
  • 248
  • 4
  • 15

1 Answers1

3

You can always wrap the command in a function. Run the function with its definer's privileges. Give execute privileges to someone else. Example:

set role roleThatOwnsTable1;

/*
 SECURITY DEFINER tells Postgres to run the function with the privileges of the role that 
 defined the function, as opposed to the privileges of the role that invoked the function.
*/
create or replace function reindexTable1() returns void SECURITY DEFINER language plpgsql as $$
begin
  reindex table table1;
end $$;

grant execute on function reindexTable1() to someGuy;

set role someGuy;

select reindexTable1();

This does, of course, open that functionality to someone that should normally not have it, so be careful.

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
  • Thanks, This works for reindex and Analyze. I put it in a loop to get all the tables since I cannot use reindex database myDb. I get REINDEX DATABASE cannot be executed from a function or multi-command string. I get the same issue with vacuum. I cannot figure out how to run vacuum – Ron H Jun 09 '15 at 15:42
  • Does this method work for deleting rows from a table? Again I don't want to give role backup anything more than it needs. I've got a function owned by postgres and granted execute to backup but I'm getting permission denied when the function tries to delete rows from a table backup only has select on. I thought that by wrapping the delete in a function owned by postgres, backup would have been able to run it – Ron H Jun 11 '15 at 16:24
  • @RonH should work the same yes. Row Security is coming in 9.5 which might change things a little though – Neil McGuigan Jun 12 '15 at 03:01
  • I missed the significance of SECURITY DEFINER, vs SECURITY INVOKER. I didn't define it with SECURITY DEFINER and that's why it wasn't working for me. – Ron H Jun 12 '15 at 15:47
  • This is a really interesting example of technical debt that has accumulated in Postgres. Very helpful answer. – ngreen Mar 30 '18 at 13:58
  • @ngreen how so? SECURITY INVOKER|DEFINER are part of the SQL Standard – Neil McGuigan Mar 30 '18 at 19:17
  • @NeilMcGuigan I mean the workarounds needed to do things like maintenance, backups, and monitoring. Just managing normal operations tasks without a superuser account is pretty much impossible, so these types of workarounds are necessary in order to enforce minimal privileges. – ngreen Apr 02 '18 at 16:09