Postgresql error permission denied to create database

The first test to see whether you can access the database server is to try to create a database. A running PostgreSQL server can manage many databases. Typically, a separate database is used for each project or for each user.

Possibly, your site administrator has already created a database for your use. In that case you can omit this step and skip ahead to the next section.

To create a new database, in this example named mydb, you use the following command:

$ createdb mydb

If this produces no response then this step was successful and you can skip over the remainder of this section.

If you see a message similar to:

createdb: command not found

then PostgreSQL was not installed properly. Either it was not installed at all or your shell’s search path was not set to include it. Try calling the command with an absolute path instead:

$ /usr/local/pgsql/bin/createdb mydb

The path at your site might be different. Contact your site administrator or check the installation instructions to correct the situation.

Another response could be this:

createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

This means that the server was not started, or it is not listening where createdb expects to contact it. Again, check the installation instructions or consult the administrator.

Another response could be this:

createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "joe" does not exist

where your own login name is mentioned. This will happen if the administrator has not created a PostgreSQL user account for you. (PostgreSQL user accounts are distinct from operating system user accounts.) If you are the administrator, see Chapter 22 for help creating accounts. You will need to become the operating system user under which PostgreSQL was installed (usually postgres) to create the first user account. It could also be that you were assigned a PostgreSQL user name that is different from your operating system user name; in that case you need to use the -U switch or set the PGUSER environment variable to specify your PostgreSQL user name.

If you have a user account but it does not have the privileges required to create a database, you will see the following:

createdb: error: database creation failed: ERROR:  permission denied to create database

Not every user has authorization to create new databases. If PostgreSQL refuses to create databases for you then the site administrator needs to grant you permission to create databases. Consult your site administrator if this occurs. If you installed PostgreSQL yourself then you should log in for the purposes of this tutorial under the user account that you started the server as. ^[1]

You can also create databases with other names. PostgreSQL allows you to create any number of databases at a given site. Database names must have an alphabetic first character and are limited to 63 bytes in length. A convenient choice is to create a database with the same name as your current user name. Many tools assume that database name as the default, so it can save you some typing. To create that database, simply type:

$ createdb

If you do not want to use your database anymore you can remove it. For example, if you are the owner (creator) of the database mydb, you can destroy it using the following command:

$ dropdb mydb

(For this command, the database name does not default to the user account name. You always need to specify it.) This action physically removes all files associated with the database and cannot be undone, so this should only be done with a great deal of forethought.

More about createdb and dropdb can be found in createdb and dropdb respectively.

Источник

Introduction

This article’s focus is mainly on how to solve an error message. The error message appear when executing the command for creating an new database. So, the following is the appearance of the output of creating a database :

First of all, the most important part is accessing the PostgreSQL database server. In this context, accessing the PostgreSQL database is possible by using a command in the command line. The aim is to login to the PostgreSQL command console :
```
[root@hostname ~]# psql -Uadmin 
Password for user admin:
psql (11.10)
Type "help" for help.


postgres=>
```
Create a new database in the PostgreSQL command console by executing the following query :
```
postgres=> create database mydb;
ERROR:  permission denied to create database
postgres=> q
```
Sadly, but it is true, the command for creating a new database does not work as usual. So, where does it go wrong ?. So, there must be a solution to solve the problem above. As the error message is indicating a permission issue, there might be a connection with the role of the user executing the command.

Solution

The solution is quite simple. Since there is a slight connection with the permission issue, below are steps to solve the problem :

First of all, just check the permission of the user from the PostgreSQL command console by accessing the PostgreSQL command console as follows :
```
[root@hostname ~]# psql -Uadmin postgres
Password for user admin:
psql (11.10)
Type "help" for help.

postgres=#
```
After that, type the follwoing command to change the permission of the user. Just execute the command to add a role for creating database. The role name is ‘createdb’. The query pattern for altering the user’s permission exist as follows :
```
postgres=# du
                                    List of roles
  Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
 admin       |                                                            | {}
 centos      | Cannot login                                               | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 dbadmin     | Superuser                                                  | {}


postgres=#
```
So, as in the above output, it is very clear that the user ‘admin’ does not have any role attributes at all. So, add a new role attribute to the user. Login first as postgres or as a superuser account by typing the following command :
```
[root@hostname ~]#psql -Upostgres
Password for user postgres:
psql (11.10)
Type "help" for help.

postgres=#
```
Soon after, execute the command to add the createdb role as follows :
```
postgres=# alter user admin createdb;
ALTER ROLE
postgres=# q
```
Next, connect to the PostgreSQL command console once more using the first user. In this context, it is the user with the name of ‘admin’ as follows :
```
[root@hostname ~]# psql -Uadmin postgres
psql (11.10)
Type "help" for help.

postgres=>
```

Finally, create the database by executing the following query :

postgres=> create database mydb;
CREATE DATABASE
postgres=> q

Last but not least, list the database to check whether the new created database exist or not by typing the following query :

postgres=> l
                                                   List of databases
    Name     |  Owner   | Encoding |          Collate           |           Ctype            |    Access privileges
-------------+----------+----------+----------------------------+----------------------------+--------------------------
 mydb        | admin    | UTF8     | English_United States.1252 | English_United States.1252 | 
 postgres    | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
 simpeg      | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
 template0   | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres             +
             |          |          |                            |                            | postgres=CTc/postgres
 template1   | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres             +
             |          |          |                            |                            | postgres=CTc/postgres
(9 rows)


postgres=>

At last, the new created database exist with the name of ‘mydb’.

Источник

Содержание

Postgresql error permission denied to create database
Submit correction
Postgresql error permission denied to create database
How to Solve Error Message ERROR: permission denied to create database in PostgreSQL Database
Introduction
Solution
Easy way to fix permission denied for database Postgres error
How to fix Permission denied for database Postgres?
1. Missing CONNECT privilege
2. Grant privileges to a new user
3. Missing Postgres user
Conclusion
PREVENT YOUR SERVER FROM CRASHING!

Possibly, your site administrator has already created a database for your use. In that case you can omit this step and skip ahead to the next section.

To create a new database, in this example named mydb , you use the following command:

If this produces no response then this step was successful and you can skip over the remainder of this section.

If you see a message similar to:

then PostgreSQL was not installed properly. Either it was not installed at all or your shell’s search path was not set to include it. Try calling the command with an absolute path instead:

The path at your site might be different. Contact your site administrator or check the installation instructions to correct the situation.

Another response could be this:

This means that the server was not started, or it is not listening where createdb expects to contact it. Again, check the installation instructions or consult the administrator.

Another response could be this:

where your own login name is mentioned. This will happen if the administrator has not created a PostgreSQL user account for you. ( PostgreSQL user accounts are distinct from operating system user accounts.) If you are the administrator, see Chapter 22 for help creating accounts. You will need to become the operating system user under which PostgreSQL was installed (usually postgres ) to create the first user account. It could also be that you were assigned a PostgreSQL user name that is different from your operating system user name; in that case you need to use the -U switch or set the PGUSER environment variable to specify your PostgreSQL user name.

If you have a user account but it does not have the privileges required to create a database, you will see the following:

If you do not want to use your database anymore you can remove it. For example, if you are the owner (creator) of the database mydb , you can destroy it using the following command:

More about createdb and dropdb can be found in createdb and dropdb respectively.

[1] As an explanation for why this works: PostgreSQL user names are separate from operating system user accounts. When you connect to a database, you can choose what PostgreSQL user name to connect as; if you don’t, it will default to the same name as your current operating system account. As it happens, there will always be a PostgreSQL user account that has the same name as the operating system user that started the server, and it also happens that that user always has permission to create databases. Instead of logging in as that user you can also specify the -U option everywhere to select a PostgreSQL user name to connect as.

Prev	Up	Next
1.2. Architectural Fundamentals	Home	1.4. Accessing a Database

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Источник

Postgresql error permission denied to create database

Possibly, your site administrator has already created a database for your use. In that case you can omit this step and skip ahead to the next section.

To create a new database, in this example named mydb , you use the following command:

If this produces no response then this step was successful and you can skip over the remainder of this section.

If you see a message similar to:

then PostgreSQL was not installed properly. Either it was not installed at all or your shell’s search path was not set to include it. Try calling the command with an absolute path instead:

The path at your site might be different. Contact your site administrator or check the installation instructions to correct the situation.

Another response could be this:

This means that the server was not started, or it is not listening where createdb expects to contact it. Again, check the installation instructions or consult the administrator.

Another response could be this:

where your own login name is mentioned. This will happen if the administrator has not created a PostgreSQL user account for you. ( PostgreSQL user accounts are distinct from operating system user accounts.) If you are the administrator, see ChapterВ 22 for help creating accounts. You will need to become the operating system user under which PostgreSQL was installed (usually postgres ) to create the first user account. It could also be that you were assigned a PostgreSQL user name that is different from your operating system user name; in that case you need to use the -U switch or set the PGUSER environment variable to specify your PostgreSQL user name.

If you have a user account but it does not have the privileges required to create a database, you will see the following:

If you do not want to use your database anymore you can remove it. For example, if you are the owner (creator) of the database mydb , you can destroy it using the following command:

More about createdb and dropdb can be found in createdb and dropdb respectively.

Источник

How to Solve Error Message ERROR: permission denied to create database in PostgreSQL Database

Introduction

First of all, the most important part is accessing the PostgreSQL database server. In this context, accessing the PostgreSQL database is possible by using a command in the command line. The aim is to login to the PostgreSQL command console :

Create a new database in the PostgreSQL command console by executing the following query :

Sadly, but it is true, the command for creating a new database does not work as usual. So, where does it go wrong ?. So, there must be a solution to solve the problem above. As the error message is indicating a permission issue, there might be a connection with the role of the user executing the command.

Solution

The solution is quite simple. Since there is a slight connection with the permission issue, below are steps to solve the problem :

First of all, just check the permission of the user from the PostgreSQL command console by accessing the PostgreSQL command console as follows :

After that, type the follwoing command to change the permission of the user. Just execute the command to add a role for creating database. The role name is ‘createdb’. The query pattern for altering the user’s permission exist as follows :

So, as in the above output, it is very clear that the user ‘admin’ does not have any role attributes at all. So, add a new role attribute to the user. Login first as postgres or as a superuser account by typing the following command :

Soon after, execute the command to add the createdb role as follows :

Next, connect to the PostgreSQL command console once more using the first user. In this context, it is the user with the name of ‘admin’ as follows :

Finally, create the database by executing the following query :

Last but not least, list the database to check whether the new created database exist or not by typing the following query :

At last, the new created database exist with the name of ‘mydb’.

Источник

Easy way to fix permission denied for database Postgres error

Postgres databases can handle complex website functions easily.

But, what if you get a permission denied error for database Postgres, frustrating right?

This website error occurs due to the lack of database privileges like CONNECT, CREATE, etc.

At Bobcares, we often receive requests to fix the permission denied error as part of our Server Management Services.

Today, let’s discuss this error in detail and see how our Support Engineers fix it for our customers.

How to fix Permission denied for database Postgres?

Postgres is a powerful database that comes up with vast features to help developers.

But, it often shows up permission denied error. Finding the exact reason for this error can be quite tricky.

The core reason for the permission denied error in Postgres is the lack of several privileges.

Now, let’s see the main causes of this error and its respective fixes.

1. Missing CONNECT privilege

Recently, one of our customers approached us with a permission denied error in the Postgres. He tried to log in to his database using psql command,

Here, userdb and user are the database name and username respectively.

But, after entering the password, he got the following error,

Our Support Engineers checked and found an error with CONNECT privilege.

Usually, the CONNECT privilege allows the user to connect to a database. And we check this privilege at the connection startup.

So, to grant CONNECT privilege, we followed the command,

This resolved the error effectively.

2. Grant privileges to a new user

In some cases, users try to grant all privileges of a database to a new Postgres user other than the owner. For that, we use the command,

But, when we log in as the new user and try to read data from the table, it ends up showing the error,

This is because GRANT ALL PRIVILEGES ON DATABASE grants CREATE, CONNECT and TEMPORARY privileges on a database to a user.

But, none of these privileges permit the user to read data from the table.

Therefore, it requires the SELECT privilege. We resolve this permission denied error using the command.

The new_user was then able to read data from the table.

Similarly, we can also resolve the permission denied error by setting DEFAULT privileges to the user.

Here, we use the ALTER DEFAULT PRIVILEGES command to define the default access privileges.

This will be specific to the schema specified in the command. Also, to apply it to the entire database, we use the command,

3. Missing Postgres user

In the control panel based servers, permission denied error can happen due to missing users as well. For instance, in control panels like Plesk, if the password for PostgreSQL user postgres does not correspond password in the Plesk database, it shows up errors.

Therefore, to fix it, we update the PostgreSQL user with the proper password and sync it with the Plesk DB. And confirm it from Plesk panel at:

[Stuck with permission denied error in Postgres?- We’ll help you.]

Conclusion

In short, the permission denied for database Postgres occurs due to the lack of certain privileges like CONNECT, CREATE, DEFAULT and so on. In today’s writeup, we discussed how our Support Engineers fix Postgres privileges for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

Источник

Today I tried to setup Postgres as my db for my Rails project, and met with this error:

Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"my_cute_db", "pool"=>5, "username"=>"rongjun", "password"=>nil}
PG::Error: ERROR:  permission denied to create database

I want to write down how I solved it.

What I intended to do is the create a db called my_cute_db with rake command, rake db:setup. However, it’s giving error like shown above.

Here’s my database.yml

development:  
adapter: postgresql  
encoding: unicode  
database: my_cute_db  
pool: 5  
username: rongjun  
password:

My username is rongjun. And the error is telling me that “permission denied to create database“, so I have to go into psql command-line interface to find out the permission of my role.

my_terminal# psql postgres
postgres=# du
                              List of roles
 Role name  |                   Attributes                   | Member of
------------+------------------------------------------------+-----------
 rongjun    |                                                | {}
 daodaowang | Create DB                                      | {}
 kai        | Password valid until infinity                  | {}
 tanjunrong | Superuser, Create role, Create DB, Replication | {}
 validator  |                                                | {}

du command is listing down the role names and their privileges. We can see that rongjun is not having any privileges. On the other hand daodaowang is able to Create DB. Now we would like to enable rongjun to be able to Create DB just like daodaowang.

postgres=# ALTER ROLE rongjun WITH CREATEDB;

Then we run du again to check:

postgres=# du
                              List of roles
 Role name  |                   Attributes                   | Member of
------------+------------------------------------------------+-----------
 rongjun    | Create DB                                      | {}
 daodaowang | Create DB                                      | {}
 kai        | Password valid until infinity                  | {}
 tanjunrong | Superuser, Create role, Create DB, Replication | {}
 validator  |                                                | {}

rongjun is now having the correct privilege to Create DB. Let’s run rake db:create again, it will do the job without error now. You can check by running psql -l command to list down all databases you have.

After creating the db, you can grant more privileges to your user:

postgres=# GRANT ALL PRIVILEGES ON DATABASE my_cute_db to rongjun;

Reference:
For how to create database refer to: http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/

For creating the user with ‘Create DB’ privilege directly without having to ALTER it later:

create user any_username with password 'any_password' createdb;

Источник