Use Roles and Permissions in PostgreSQL on a VPS

great article. helped me a lot!

Excellent, easy-to-follow intro to roles and permissions in PostgreSQL. Thanks Justin.

A small note. On Ubuntu 14.04, after logging in as ‘test_user’, then reverting to the admin session and typing sudo su - postgres from the command line, I found I was asked for the postgres password, which I didn’t set / didn’t know. Problem solved by opening a new terminal and entering the same command - then I was asked for my standard Ubuntu password.

Hi, I’m trying to create a user with permissions to create a database. I’m logged in as the deploy user and I’m able to create the user ‘deploy’

However, when I try to alter the role and give it CREATEDB permissions with the command:

ALTER ROLE deploy WITH CREATEDB

and then check on the changed permissions with \du, nothing has happened. The ‘deploy’ user is there, but it has no CREATEDB permissions.

I also tried this as a root user, with the same result.

I also tried to create the user from the commandline with:

createuser deploy

according to this tutorial I should get these questions:

Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

but I didn’t, the user gets created and I’m back at the prompt.

at the prompt I’m also seeing:

psql (9.4beta3)

Does that mean my version of Postgres is 9.4? That’s odd, because I installed version 9.3

thanks for your help,

Anthony

This article helped me a lot, Thanks

Thanks! Didn’t know a default user is created and its name is postgres