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

[Tom Lane]

John R Pierce  writes:
> On 9/22/2017 10:29 PM, Tim Uckun wrote:
>> 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.

> that should have worked just fine.

Yeah.  Note that creating new schemas is not directly connected to
ownership anyway --- it's a question of whether you have the CREATE
privilege on the database.  The owner should have that privilege
by default, but it could be revoked, or granted to others.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

On Sat, Sep 23, 2017 at 2:33 AM, John R Pierce  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@new ~]# useradd fred
> [root@new ~]# su - postgres
> $ createuser fred
> $ createdb fred -O fred
> $ logout
> [root@new ~]# su - fred
> [fred@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@postgresql.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.

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

[John R Pierce]

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@new ~]# useradd fred
[root@new ~]# su - postgres
$ createuser fred
$ createdb fred -O fred
$ logout
[root@new ~]# su - fred
[fred@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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Why can't the database owner create schemas and how can I enable that?

[Tim Uckun]

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?