Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
    
Updated code to show that it is going into the demo database. Does it matter what user I use to import it? –  Topher Fangio Nov 11 '09 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 '09 at 19:47
    
@JoesephL - Thanks, I'll give it a try and comment. –  Topher Fangio Nov 11 '09 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? –  Topher Fangio Nov 11 '09 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 '09 at 1:12

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

share|improve this answer
    
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. –  Topher Fangio Nov 11 '09 at 14:42

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

share|improve this answer
    
Thanks, I think I tried this, but I'll try again and come back to comment/select an answer. –  Topher Fangio Nov 11 '09 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. –  Topher Fangio Nov 11 '09 at 15:06
    
try as postgres (instead of your superuser) in case your superuser isn't what you think it is :) –  user6373 Nov 11 '09 at 16:25
    
@ironchef - Yeah, that thought crossed my mind; attempted it, still the same problem. –  Topher Fangio Nov 11 '09 at 19:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.