1

I am new to PostgreSQL and am having a bit of trouble finding any information on how to grant a user permissions on the database that they own. I have read a few sources that says the owner has all permissions, but it doesn't seem to work for me. I still had to grant permissions in order to run a select statement.

I have also read quite a few things that say that PostgreSQL simply doesn't support granting to multiple tables in a database at once, so I'm hoping that they are just out-of-date.

Here is what I have done so far:

# Create the user
createuser -SDRPE demo

# Create the database
createdb demo -O demo

# Import schema
psql -f myfile.sql demo

# Grant Permissions
???

Can somebody tell me the best way to go about this? Currently, I am writing all of this inside of a Rake task, so I could technically retrieve all of the tables in the database and iterate through them, but it feels like an awful clunky solution. It seems like I am missing something. Any thoughts/comments/links would be greatly appreciated.

Edit 1: As a quick note, all of these commands are being run as root which is a superuser in the database with the IDENT SAMEUSER authentication specified in pg_hba.conf in case that makes any difference.

3 Answers 3

1

Can you try

psql dbname username < filename.sql

So you would use

psql demo demo < myfile.sql

I am not sure where your sql file is going because you are not specifying the db in your script.

5
  • Updated code to show that it is going into the demo database. Does it matter what user I use to import it? Nov 11, 2009 at 14:44
  • I think the user is important. Try psql -f myfile.sql -d demo -u demo. This should run the commands as the demo user which should mean the objects created will be owned by the demo user.
    – JosephL
    Nov 11, 2009 at 19:47
  • @JoesephL - Thanks, I'll give it a try and comment. Nov 11, 2009 at 21:22
  • @JoesephL - Thanks, it worked! Now the only problem is that it requires me to type the password for every myfile.sql that I import (which is only 3 at the moment, but it's still a lot of user interaction). Any idea how to change that? Nov 11, 2009 at 21:30
  • 1
    You could use a .pgpass file in your users home dirctory. Not sure how this would work as you are running the script as root but connecting as demo using ident. I would try putting the file in the demo users home directory. See postgresql.org/docs/8.1/interactive/libpq-pgpass.html
    – JosephL
    Nov 12, 2009 at 1:12
1

Some examples might be:

pretty much everything: grant all privileges on database demo to demo;

grant some simple selects: grant select on table foo to demo;

more than select: grant select, insert, update, delete on table foo to demo;

More info here: http://www.postgresql.org/docs/8.3/static/sql-grant.html

4
  • Thanks, I think I tried this, but I'll try again and come back to comment/select an answer. Nov 11, 2009 at 14:42
  • 1
    I tried grant all privileges on database demo to demo; but I am still getting ERROR: permission denied for relation whatever. I am running 8.3. Nov 11, 2009 at 15:06
  • try as postgres (instead of your superuser) in case your superuser isn't what you think it is :)
    – user6373
    Nov 11, 2009 at 16:25
  • @ironchef - Yeah, that thought crossed my mind; attempted it, still the same problem. Nov 11, 2009 at 19:26
0

If all you want to do is grant the user 'demo' the ability to read/write/modify the database 'demo', you are already done, because the owner of the database has all necessary privileges by default.

If you need more specific privileges for more than a single user, you can CREATE USER a new user and GRANT privileges to one or more objects.

For non-local access to the database I strongly recommend adjusting your pg_hba.conf

1
  • Unfortunately, as my question attempted to state, what I have done is NOT granting permissions on the demo table for the demo user. I can't do a simple select statement on the table until I have executed grant all on table_name to demo. Nov 11, 2009 at 14:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .