Re: Why can't the database owner create schemas and how can I enable that?

On Sat, Sep 23, 2017 at 2:33 AM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 9/22/2017 10:29 PM, Tim Uckun wrote:
>
>> I am setting up a database for an app. So I create a user without
>> superuser but with login priveleges
>>
>> I then create a database and set it's owner to that user like this...
>>
>> dropdb --if-exists api_development
>> dropuser --if-exists api_user
>>
>> createuser api_user -P -d
>> createdb api_development -O api_user
>>
>> The app can now connect to the database but it can't create any tables,
>> schemas or anything else unless I give it superuser privileges.
>>
>> Is there any way I can make this user a superuser for this database
>> without making it a superuser on other databases?
>>
>
>
> that should have worked just fine.
>
>
> [root(at)new ~]# useradd fred
> [root(at)new ~]# su - postgres
> $ createuser fred
> $ createdb fred -O fred
> $ logout
> [root(at)new ~]# su - fred
> [fred(at)new ~]$ psql
> psql (9.3.19)
> Type "help" for help.
>
> fred=> create schema xyzzy;
> CREATE SCHEMA
> fred=> create table xyzzy.abc (id serial, dat text);
> CREATE TABLE
> fred=> \q
>
> .....
>
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Make sure you are the DB owner when you connect.

Either
psql -U api_user -d api_development

OR
psql -d api_development

SET ROLE api_user;
SELECT current_user;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.