Postgresql Error Permission Denied To Create Role
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the permission denied to create database postgresql workings and policies of this site About Us Learn more about Stack
Rails Postgres Permission Denied To Create Database
Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions pg::insufficientprivilege: error: permission denied to create extension "hstore" Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join postgres grant user permission to create database them; it only takes a minute: Sign up Postgres permission denied to create database on rake db:create:all up vote 11 down vote favorite 1 I am trying to create postgres databases for development and tests. I'm using... OSX Yosemite Rails version: 4.2.0 git version: 2.2.2 psql version: 9.4.0 ruby version: 2.1.0p0 HomeBrew version: 0.9.5 Gemfile... gem 'pg' database.yml default:
Error: Must Be Superuser To Alter Superusers
&default adapter: postgresql encoding: unicode pool: 5 development: <<: *default database: myapp_development username: username password: test: <<: *default database: myapp_test rake db:create:all returns PG::InsufficientPrivilege: ERROR: permission denied to create database : CREATE DATABASE "myapp_development" ENCODING = 'unicode' .... (lots of tracing) Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "pool"=>5, "database"=>"myapp_development", "username"=>"username", "password"=>nil} myapp_test already exists What is wrong? EDIT I just tried changing the username in the database.yml to my username that I'm using on my Mac. It worked. It also told me that not only maybe_test' already exists, but it also just told me thatmyapp_development` already exists too. Why wouldn't it be able to use the other username that I had created and assigned a role to CREATEDB? Why did it say that the development couldn't be created then tell me that it already existed? This all seems way too confusing and reminds me of php setup with apache back in the very old days. I don't want to have to deal with problems every time I create a new app and try to f
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings error must be superuser to alter superusers rds and policies of this site About Us Learn more about Stack Overflow pg::insufficientprivilege: error: permission denied for relation schema_migrations the company Business Learn more about hiring developers or posting ads with us Super User Questions Tags Users
Postgres Give User Permission To Create Database
Badges Unanswered Ask Question _ Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute: Sign up Here's how http://stackoverflow.com/questions/28116927/postgres-permission-denied-to-create-database-on-rake-dbcreateall it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top User permissions for creating PostgreSQL DB up vote 13 down vote favorite 6 I'm working on Ubuntu. Following is one of my commands. $ psql -U kuser -d postgres Then this connects to the database. But from postgres terminal when http://superuser.com/questions/507721/user-permissions-for-creating-postgresql-db i try postgres=> CREATE DATABASE kdb; ERROR: permission denied to create database When I try a similar command in Ubuntu, it gives the following $ sudo -u kuser createdb kdb sudo: unknown user: kuser sudo: unable to initialize policy plugin How do I create this DB?. I have sudo rights and kuser is not me. postgresql share|improve this question edited Nov 19 '12 at 15:19 qqx 1,873711 asked Nov 19 '12 at 15:03 dinesh707 175114 add a comment| 1 Answer 1 active oldest votes up vote 28 down vote accepted It appears that you have a database user named kuser, but there is no system user with that name. This is why you're able to get a postgres prompt as that user, but sudo fails. That user isn't able to create a database, because that account doesn't have the necessary permission. You can either grant that permission to the user, using the postgres account which is the default management account on Ubuntu: sudo -u postgres psql -c 'alter user kuser with createdb' postgres Or you can just use that management
9.0 PostgreSQL 9.6.0 Documentation Prev Up Next CREATE ROLE NameCREATE ROLE--define a new database role Synopsis CREATE ROLE name [ [ WITH ] option https://www.postgresql.org/docs/9.6/static/sql-createrole.html [ ... ] ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS https://www.depesz.com/2009/09/06/create-role-privilege-cannot-be-inherited/ | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE to create role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid Description CREATE ROLE adds a new role to a PostgreSQL database cluster. A role is an entity that can own database objects and have database privileges; a role can be considered a "user", a "group", or both depending on how it is used. Refer to Chapter 21 and Chapter permission denied to 20 for information about managing users and authentication. You must have CREATEROLE privilege or be a database superuser to use this command. Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster. Parameters name The name of the new role. SUPERUSER NOSUPERUSER These clauses determine whether the new role is a "superuser", who can override all access restrictions within the database. Superuser status is dangerous and should be used only when really needed. You must yourself be a superuser to create a new superuser. If not specified, NOSUPERUSER is the default. CREATEDB NOCREATEDB These clauses define a role's ability to create databases. If CREATEDB is specified, the role being defined will be allowed to create new databases. Specifying NOCREATEDB will deny a role the ability to create databases. If not specified, NOCREATEDB is the default. CREATEROLE NOCREATEROLE These clauses determine whether a role will be permitted to create new roles (that is, execute CREATE ROLE). A role with CREATEROLE privilege can also alter and drop other roles. If not specified, NOCREATEROLE is the default. INHERIT NOINHERIT These clauses determine whether a role "inherits" the
me? One of my clients hit a strange limitation - apparently you cannot inherit CREATE ROLE privilege. First, let's test if it's really true: First, let's create role which will have CREATE ROLE privilege: create role test1 with login createrole; Now, let's create new role, make it inherit privileges, and grant it test1 role: # create role test2 with login inherit;
CREATE ROLE
# grant test1 to test2 with admin option;
GRANT ROLE And now, let's connect to test2 role, and check if we can create new roles: > \c - test2
You are now connected to database "depesz".
> create role test3;
ERROR: permission denied to create role Ok, So, let's just check if everything is ok: > \du test*
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
test1 | Create role | {}
test2 | | {test1}
> \c - test1
You are now connected to database "depesz" as user "test1".
> create role test3;
CREATE ROLE OK. Clearly test1 role can create new roles, test2 inherits from it, and cannot. So, what can we do about it? Answer is pretty simple - let's write a wrapper around CREATE ROLE: \c - test1
You are now connected to database "depesz".
> CREATE OR REPLACE FUNCTION create_role( in_role_name TEXT, in_options TEXT ) RETURNS void as $_$
DECLARE
use_sql TEXT;
BEGIN
use_sql := 'CREATE ROLE ' || quote_ident( in_role_name );
IF in_options IS NOT NULL THEN
IF in_options ~ '(;|--)' THEN
RAISE EXCEPTION $$Don't try to be too smart ...$$;
END IF;
use_sql := use_sql || ' WITH ' || in_options;
END IF;
EXECUTE use_sql;
END;
$_$ LANGUAGE plpgsql SECURITY DEFINER; And now we just have to revoke rights to execute this function from public (otherwise any user could call it!): REVOKE ALL ON FUNCTION create_role( in_role_name TEXT, in_options TEXT ) FROM public; Now, we can: > \c - test2
You are now connected to database "depesz" as user "test2".
> select create_role('test4', 'login inherit');
create_role
-------------
(1 row)
> \du test*
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
test1 | Create role | {}
test2 | | {test1}
test3 | | {}
test4 | | {} Of course, calling this function requires code change ( assuming previous code called CREATE ROLE directly ), but at least it works around missing privilege inheritance. Notice that we didn't have to GRANT any privileges to EXECUTE the function -