Granting PostgreSQL Database and Table Ownership / Privileges to a New User
Scott Rippee @ 8:10 pm January 14th, 2007
|
I found the process of modifying database and table permissions a little tricky in PostgreSQL, mostly due to trying to use GRANT when I should have been using ALTER. The following are some tips. |
Login to postgresql as a user who has superuser privileges:
-
psql -U username
Check out the users who already exist
-
\du
or a select from pg_users
-
SELECT * FROM pg_user;
Lets add a new user with the createuser shell command:
-
createuser -U currentuser -A -D -P newuser
This give us a new user with the name 'newuser' and password 'somepassword'. We specified a current user with sufficient privileges (superuser) using -U."
We can now create a new database with the owner being the newuser we had created:
-
createdb -U currentuser -O newuser mynewdb
Or using SQL:
-
CREATE DATABASE mynewdb OWNER newuser;
Here we used our super user account to add a new database that is owned by our 'newuser'. We can log back into postgresql with our new user and access our new database using "psql -U newuser mynewdb". Now we can SQL it up to our hearts desire, create new tables, alter tables, drop tables.
Now for granting this new user privileges and ownership of database that already has an existing owner. You may try using GRANT to give the new user the desired privileges to the existing tables using:
-
GRANT ALL ON tablename TO newuser;
However, you most likely want the ability to create new tables in the database and with the above can still be restricted access certain ALTER functionality for the table. For this access privileges of the database needs to be changed AND the tables have to be told they have a new OWNER:
Logged in as a super user and:
-
ALTER DATABASE mynewdb OWNER TO newuser;
Additionally you could just add privileges for a user to a database with GRANT. Here I grant all, but one could grant only specific access privileges:
-
GRANT ALL ON DATABASE mynewdb TO newuser;
And you can be logged in as the new user to the correct database to change the OWNER of the existing tables:
-
ALTER TABLE some_table OWNER TO newuser;
Lastly you can control host and user access to databases by editing postgresql/data/pg_hba.conf. The following will give access to our new user to our new database when trying to connect from the localhost:
-
# TYPE DATABASE USER CIDR-ADDRESS METHOD
-
host mynewdb newuser 127.0.0.1/32 md5
Now we're all setup to add new users, modify the owner of existing databases and tables, and control database access. Happy Hacking
June 14th, 2007 at 1:45 pm
Great tutorial by the way. When you create the a new user from the command shell I think it should be a capital -P not -p.
-p port
--port port
-P password
--pwprompt
Chris
June 17th, 2007 at 5:13 pm
Thanks, its fixed now.
February 26th, 2009 at 1:03 pm
This is old, but I can't seem to find any other forum that discusses it (w/o sending a mail message).
Don't have access to super user.
Database/table owner was USERA.
Superuser changed database owner to USERB.
USERB cannot perform table ownership change:
ALTER TABLE OWNER TO USERB;
While this seems like it's on purpose, was curious if there was a way around it?
February 26th, 2009 at 1:09 pm
Some of the text in the SQL Statement got cut off because it used HTML. It should have read:
ALTER TABLE (usera_table) OWNER TO USERB;
Also, I know that only the Superuser/Owner can make ownership changes. Just curious why database owner cannot. Possibly because the role of the database owner is to perform backups and not modify the tables? Curious if there is a setting where this role can be modified/changed.
Thank you,
Mike