CODEWITHSUNDEEP
postgresqlrolesplpython
azmeuk
azmeuk

Reputation: 4506

PostgreSQL trigger with a given role

I made a little PostgreSQL trigger with Plpython. This triggers plays a bit with the file system, creates and delete some files of mine. Created files are owned by the "postgres" unix user, but I would like them to be owned by another user, let's say foobar. Triggers are installed with user "foobar" and executed with user "foobar" too.

Is there a way to execute the SQL trigger with the unix user 'foobar' with PostgreSQL or Plpython? Should I use SET ROLE foobar ?

Playing with SECURITY INVOKER and SECURITY DEFINER does not seem to be good enough.

Upvotes: 0

Views: 1789

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324475

You're confusing operating system users and PostgreSQL users.

SECURITY DEFINER lets you run a function as the defining postgresql user. But no matter what PostgreSQL user is running the operating system user the back-end server runs as is always the same - usually the operating system user postgres.

By design, the PostgreSQL server cannot run operating system commands or system calls as other operating system users. That would be a nasty security hole.

However, if you want to permit that, you can. You could:

  • Grant the postgres user sudo rights to run some or all commands as other users; or
  • Write a program to run with setuid rights to do what you want and grant the postgres user the right to execute it.

In either case, the only way to run these programs is by launching them from an untrusted procedural language like plpython or plperl, or from a C extension.'

It isn't clear why you want to set the file ownership like this in the first place, but I suspect it's probably not a great idea. What if the PostgreSQL client and server aren't even on the same computer? What if there's no operating system user for that PostgreSQL user, or the usernames are different? etc.

Upvotes: 3

Greg
Greg

Reputation: 6759

The 'role' that your users assume in Postgres are local to the database. Your role in the database enforces database permissions. So, you can say that foobar can run this stored proc, or select this table, and Postgres will enforce that. If you create your procedure using SECURITY INVOKER that means that the procedure being run will be run using the current logged in Postgres user. SECURITY DEFINER means that the procedure will be run using the role that created the procedure. Either way, if your procedure does anything outside Postgres (like create a file) it will be done as the identity that started Postgres (as you have found out). In my case, I have a Unix user named 'postgres' and when I start up Postgres I do it as that user. So, any file that is created will be owned by the 'postgres' user.

You didn't tell us your operating system. Windows will be different than *nix. This answer is for *nix.

You did mention that your stored proc language was plpython. So, you could create the file, then change it's ownership after you create it in your procedure. Like:

import os
import pwd

f = open('/tmp/myfile','w')
f.write('hello')
f.close()
user_id = pwd.getpwnam("foobar").pw_uid    
os.chown('/tmp/myfile', user_id, -1)

Assuming that there is a foobar user on the host that runs Postgres this should work.

-g

Upvotes: 2

Related Questions