relation does not exist (golang/mac OS)
I've been extensively searching for a few hours now and I feel like I've tried everything but I'm still getting that the 'relation does not exist', from both my code in Go, and also from dumping the output of the PSQL database.
when logged in as the default postgres user:
from du, all of the users are there and it says that it owns the correct database
from dn, all of the schemas are there
though when I changer user, e.g.
c myapp_db myapp_admin
running du, dn, etc. I get nothing
show search_path
also displays as 'myapp', which is correct. everything seems to be correct but the relation is not there, what gives?
Here is my .sql file which I loaded like so:
psql
i thefile.sql
... runs and everything works granted, created, altered, etc.
c myapp_db myapp_admin
then I run
dt
and no bueno! there are no relations!
The SQL file in question:
CREATE USER myapp_admin WITH ENCRYPTED PASSWORD 'admin';
CREATE USER myapp_manager WITH ENCRYPTED PASSWORD 'manager';
CREATE USER myapp_user WITH ENCRYPTED PASSWORD 'user';
GRANT myapp_user TO myapp_manager;
GRANT myapp_manager TO myapp_admin;
CREATE DATABASE myapp_db;
REVOKE ALL ON DATABASE myapp_db FROM PUBLIC;
GRANT CONNECT ON DATABASE myapp_db to myapp_user;
CREATE SCHEMA myapp AUTHORIZATION myapp_admin;
SET search_path = myapp;
ALTER ROLE myapp_admin IN DATABASE myapp_db SET search_path = myapp;
ALTER ROLE myapp_manager IN DATABASE myapp_db SET search_path = myapp;
ALTER ROLE myapp_user IN DATABASE myapp_db SET search_path = myapp;
GRANT USAGE ON SCHEMA myapp to myapp_user;
GRANT CREATE ON SCHEMA myapp to myapp_admin;
CREATE TABLE myapp.account (
id bigserial primary key not null,
);
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT SELECT ON TABLES TO myapp_user; -- only read
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO myapp_manager; -- + write, truncate
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO myapp_manager;
Any ideas what's going on here? I'm starting to think it's some error with my PostgreSQL installation.
database postgresql
go psql asked Dec 27 at 13:42
flooblebit
816
|
show 1 more comment
I've been extensively searching for a few hours now and I feel like I've tried everything but I'm still getting that the 'relation does not exist', from both my code in Go, and also from dumping the output of the PSQL database.
when logged in as the default postgres user:
from du, all of the users are there and it says that it owns the correct database
from dn, all of the schemas are there
though when I changer user, e.g.
c myapp_db myapp_admin
running du, dn, etc. I get nothing
show search_path
also displays as 'myapp', which is correct. everything seems to be correct but the relation is not there, what gives?
Here is my .sql file which I loaded like so:
psql
i thefile.sql
... runs and everything works granted, created, altered, etc.
c myapp_db myapp_admin
then I run
dt
and no bueno! there are no relations!
The SQL file in question:
CREATE USER myapp_admin WITH ENCRYPTED PASSWORD 'admin';
CREATE USER myapp_manager WITH ENCRYPTED PASSWORD 'manager';
CREATE USER myapp_user WITH ENCRYPTED PASSWORD 'user';
GRANT myapp_user TO myapp_manager;
GRANT myapp_manager TO myapp_admin;
CREATE DATABASE myapp_db;
REVOKE ALL ON DATABASE myapp_db FROM PUBLIC;
GRANT CONNECT ON DATABASE myapp_db to myapp_user;
CREATE SCHEMA myapp AUTHORIZATION myapp_admin;
SET search_path = myapp;
ALTER ROLE myapp_admin IN DATABASE myapp_db SET search_path = myapp;
ALTER ROLE myapp_manager IN DATABASE myapp_db SET search_path = myapp;
ALTER ROLE myapp_user IN DATABASE myapp_db SET search_path = myapp;
GRANT USAGE ON SCHEMA myapp to myapp_user;
GRANT CREATE ON SCHEMA myapp to myapp_admin;
CREATE TABLE myapp.account (
id bigserial primary key not null,
);
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT SELECT ON TABLES TO myapp_user; -- only read
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO myapp_manager; -- + write, truncate
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO myapp_manager;
Any ideas what's going on here? I'm starting to think it's some error with my PostgreSQL installation.
database postgresql
go psql asked Dec 27 at 13:42
flooblebit
816
1
You create the database but all the tables etc are created in the database you’re connected to, not in the new one. It will remain empty
– Sami Kuhmonen
Dec 27 at 13:47
@SamiKuhmonen Oh... that is annoyingly simple. How can I specify to create them in the new database?
– flooblebit
Dec 27 at 13:56
You’ll have to create the database, connect to it, then run the commands. The connection switching can’t be a part of the SQL script. So basically remove the database creation and do it outside the script.
– Sami Kuhmonen
Dec 27 at 14:01
Ah I see. I'll give that a go and report back.
– flooblebit
Dec 27 at 14:10
@SamiKuhmonen This works! Thank you. Though, I also get a permission denied for the table when trying to insert rows. Could you spot anything in my setup that would cause this?
– flooblebit
Dec 27 at 14:56
|
show 1 more comment
I've been extensively searching for a few hours now and I feel like I've tried everything but I'm still getting that the 'relation does not exist', from both my code in Go, and also from dumping the output of the PSQL database.
when logged in as the default postgres user:
from du, all of the users are there and it says that it owns the correct database
from dn, all of the schemas are there
though when I changer user, e.g.
c myapp_db myapp_admin
running du, dn, etc. I get nothing
show search_path
also displays as 'myapp', which is correct. everything seems to be correct but the relation is not there, what gives?
Here is my .sql file which I loaded like so:
psql
i thefile.sql
... runs and everything works granted, created, altered, etc.
c myapp_db myapp_admin
then I run
dt
and no bueno! there are no relations!
The SQL file in question:
CREATE USER myapp_admin WITH ENCRYPTED PASSWORD 'admin';
CREATE USER myapp_manager WITH ENCRYPTED PASSWORD 'manager';
CREATE USER myapp_user WITH ENCRYPTED PASSWORD 'user';
GRANT myapp_user TO myapp_manager;
GRANT myapp_manager TO myapp_admin;
CREATE DATABASE myapp_db;
REVOKE ALL ON DATABASE myapp_db FROM PUBLIC;
GRANT CONNECT ON DATABASE myapp_db to myapp_user;
CREATE SCHEMA myapp AUTHORIZATION myapp_admin;
SET search_path = myapp;
ALTER ROLE myapp_admin IN DATABASE myapp_db SET search_path = myapp;
ALTER ROLE myapp_manager IN DATABASE myapp_db SET search_path = myapp;
ALTER ROLE myapp_user IN DATABASE myapp_db SET search_path = myapp;
GRANT USAGE ON SCHEMA myapp to myapp_user;
GRANT CREATE ON SCHEMA myapp to myapp_admin;
CREATE TABLE myapp.account (
id bigserial primary key not null,
);
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT SELECT ON TABLES TO myapp_user; -- only read
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO myapp_manager; -- + write, truncate
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO myapp_manager;
Any ideas what's going on here? I'm starting to think it's some error with my PostgreSQL installation.
database postgresql
go psql asked Dec 27 at 13:42
flooblebit
816
I've been extensively searching for a few hours now and I feel like I've tried everything but I'm still getting that the 'relation does not exist', from both my code in Go, and also from dumping the output of the PSQL database.
when logged in as the default postgres user:
from du, all of the users are there and it says that it owns the correct database
from dn, all of the schemas are there
though when I changer user, e.g.
c myapp_db myapp_admin
running du, dn, etc. I get nothing
show search_path
also displays as 'myapp', which is correct. everything seems to be correct but the relation is not there, what gives?
Here is my .sql file which I loaded like so:
psql
i thefile.sql
... runs and everything works granted, created, altered, etc.
c myapp_db myapp_admin
then I run
dt
and no bueno! there are no relations!
The SQL file in question:
CREATE USER myapp_admin WITH ENCRYPTED PASSWORD 'admin';
CREATE USER myapp_manager WITH ENCRYPTED PASSWORD 'manager';
CREATE USER myapp_user WITH ENCRYPTED PASSWORD 'user';
GRANT myapp_user TO myapp_manager;
GRANT myapp_manager TO myapp_admin;
CREATE DATABASE myapp_db;
REVOKE ALL ON DATABASE myapp_db FROM PUBLIC;
GRANT CONNECT ON DATABASE myapp_db to myapp_user;
CREATE SCHEMA myapp AUTHORIZATION myapp_admin;
SET search_path = myapp;
ALTER ROLE myapp_admin IN DATABASE myapp_db SET search_path = myapp;
ALTER ROLE myapp_manager IN DATABASE myapp_db SET search_path = myapp;
ALTER ROLE myapp_user IN DATABASE myapp_db SET search_path = myapp;
GRANT USAGE ON SCHEMA myapp to myapp_user;
GRANT CREATE ON SCHEMA myapp to myapp_admin;
CREATE TABLE myapp.account (
id bigserial primary key not null,
);
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT SELECT ON TABLES TO myapp_user; -- only read
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO myapp_manager; -- + write, truncate
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO myapp_manager;
Any ideas what's going on here? I'm starting to think it's some error with my PostgreSQL installation.
database postgresql
go psql database postgresql
go psql asked Dec 27 at 13:42
flooblebit
816
asked Dec 27 at 13:42
flooblebit
816
asked Dec 27 at 13:42
flooblebit
816
asked Dec 27 at 13:42
flooblebit
816
asked Dec 27 at 13:42
flooblebit
816
816
1
You create the database but all the tables etc are created in the database you’re connected to, not in the new one. It will remain empty
– Sami Kuhmonen
Dec 27 at 13:47
@SamiKuhmonen Oh... that is annoyingly simple. How can I specify to create them in the new database?
– flooblebit
Dec 27 at 13:56
You’ll have to create the database, connect to it, then run the commands. The connection switching can’t be a part of the SQL script. So basically remove the database creation and do it outside the script.
– Sami Kuhmonen
Dec 27 at 14:01
Ah I see. I'll give that a go and report back.
– flooblebit
Dec 27 at 14:10
@SamiKuhmonen This works! Thank you. Though, I also get a permission denied for the table when trying to insert rows. Could you spot anything in my setup that would cause this?
– flooblebit
Dec 27 at 14:56
|
show 1 more comment
1
You create the database but all the tables etc are created in the database you’re connected to, not in the new one. It will remain empty
– Sami Kuhmonen
Dec 27 at 13:47
@SamiKuhmonen Oh... that is annoyingly simple. How can I specify to create them in the new database?
– flooblebit
Dec 27 at 13:56
You’ll have to create the database, connect to it, then run the commands. The connection switching can’t be a part of the SQL script. So basically remove the database creation and do it outside the script.
– Sami Kuhmonen
Dec 27 at 14:01
Ah I see. I'll give that a go and report back.
– flooblebit
Dec 27 at 14:10
@SamiKuhmonen This works! Thank you. Though, I also get a permission denied for the table when trying to insert rows. Could you spot anything in my setup that would cause this?
– flooblebit
Dec 27 at 14:56
1
1
You create the database but all the tables etc are created in the database you’re connected to, not in the new one. It will remain empty
– Sami Kuhmonen
Dec 27 at 13:47
You create the database but all the tables etc are created in the database you’re connected to, not in the new one. It will remain empty
– Sami Kuhmonen
Dec 27 at 13:47
@SamiKuhmonen Oh... that is annoyingly simple. How can I specify to create them in the new database?
– flooblebit
Dec 27 at 13:56
@SamiKuhmonen Oh... that is annoyingly simple. How can I specify to create them in the new database?
– flooblebit
Dec 27 at 13:56
You’ll have to create the database, connect to it, then run the commands. The connection switching can’t be a part of the SQL script. So basically remove the database creation and do it outside the script.
– Sami Kuhmonen
Dec 27 at 14:01
You’ll have to create the database, connect to it, then run the commands. The connection switching can’t be a part of the SQL script. So basically remove the database creation and do it outside the script.
– Sami Kuhmonen
Dec 27 at 14:01
Ah I see. I'll give that a go and report back.
– flooblebit
Dec 27 at 14:10
Ah I see. I'll give that a go and report back.
– flooblebit
Dec 27 at 14:10
@SamiKuhmonen This works! Thank you. Though, I also get a permission denied for the table when trying to insert rows. Could you spot anything in my setup that would cause this?
– flooblebit
Dec 27 at 14:56
@SamiKuhmonen This works! Thank you. Though, I also get a permission denied for the table when trying to insert rows. Could you spot anything in my setup that would cause this?
– flooblebit
Dec 27 at 14:56
|
show 1 more comment
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53946067%2frelation-does-not-exist-golang-mac-os%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53946067%2frelation-does-not-exist-golang-mac-os%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
You create the database but all the tables etc are created in the database you’re connected to, not in the new one. It will remain empty
– Sami Kuhmonen
Dec 27 at 13:47
@SamiKuhmonen Oh... that is annoyingly simple. How can I specify to create them in the new database?
– flooblebit
Dec 27 at 13:56
You’ll have to create the database, connect to it, then run the commands. The connection switching can’t be a part of the SQL script. So basically remove the database creation and do it outside the script.
– Sami Kuhmonen
Dec 27 at 14:01
Ah I see. I'll give that a go and report back.
– flooblebit
Dec 27 at 14:10
@SamiKuhmonen This works! Thank you. Though, I also get a permission denied for the table when trying to insert rows. Could you spot anything in my setup that would cause this?
– flooblebit
Dec 27 at 14:56