Re: [GENERAL] Why can't the database owner create schemas and how canI enable that? - Mailing list pgsql-general

From Melvin Davidson
Subject Re: [GENERAL] Why can't the database owner create schemas and how canI enable that?
Date
Msg-id CANu8FiwMtF33dwgv--EMcdff9Mb+KsSOxGWOhpnB3ac8VbJ8ew@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] Why can't the database owner create schemas and how canI enable that?  (John R Pierce <pierce@hogranch.com>)
List pgsql-general


On Sat, Sep 23, 2017 at 2:33 AM, John R Pierce <pierce@hogranch.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@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.

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: [GENERAL] Adding Cyrillic support
Next
From: Tomas Vondra
Date:
Subject: Re: [GENERAL] shared_buffers smaller than max_wal_size