about img
blogger img

scotts posts image

buchos posts img

Corys posts image

UnderPaidLoveMonkis posts img


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:

CODE:
  1. psql -U username

Check out the users who already exist

CODE:
  1. \du

or a select from pg_users

SQL:
  1. SELECT * FROM pg_user;

Lets add a new user with the createuser shell command:

CODE:
  1. 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:

CODE:
  1. createdb -U currentuser -O newuser mynewdb

Or using SQL:

SQL:
  1. 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:

SQL:
  1. 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:

SQL:
  1. 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:

SQL:
  1. 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:

SQL:
  1. 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:

CODE:
  1. # TYPE  DATABASE   USER         CIDR-ADDRESS     METHOD
  2. 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

7 Responses to “Granting PostgreSQL Database and Table Ownership / Privileges to a New User”

  1. Chris Mcmichael Says:

    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

  2. Scott Rippee Says:

    Thanks, its fixed now.

  3. Michael Blume Says:

    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?

  4. Michael Blume Says:

    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

  5. yogesh Says:

    Thanks, i wanted to change the owner of table ... i got that....

  6. Prabakaran Says:

    Hi,

    I want to change the owner of bulk table in postgres, kindly help me.
    give some example command.

    Thanks in advance

  7. supriya Says:

    hi can u tell me how to check owner of the table in postgresql?

Leave a Reply

Subscribe without commenting